[6779] | 1 | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> |
---|
| 2 | <html> |
---|
| 3 | <head> |
---|
| 4 | <title>ADOdb Performance Monitoring Library</title> |
---|
| 5 | <style type="text/css"> |
---|
| 6 | body, td { |
---|
| 7 | /*font-family: Arial, Helvetica, sans-serif;*/ |
---|
| 8 | font-size: 11pt; |
---|
| 9 | } |
---|
| 10 | pre { |
---|
| 11 | font-size: 9pt; |
---|
| 12 | background-color: #EEEEEE; padding: .5em; margin: 0px; |
---|
| 13 | } |
---|
| 14 | .toplink { |
---|
| 15 | font-size: 8pt; |
---|
| 16 | } |
---|
| 17 | </style> |
---|
| 18 | </head> |
---|
| 19 | <body> |
---|
| 20 | <h3>The ADOdb Performance Monitoring Library</h3> |
---|
| 21 | <p>V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my)</p> |
---|
| 22 | <p><font size="1">This software is dual licensed using BSD-Style and |
---|
| 23 | LGPL. This means you can use it in compiled proprietary and commercial |
---|
| 24 | products.</font></p> |
---|
| 25 | <p>Useful ADOdb links: <a href="http://adodb.sourceforge.net/#download">Download</a> |
---|
| 26 | <a href="http://adodb.sourceforge.net/#docs">Other Docs</a> |
---|
| 27 | </p> |
---|
| 28 | <h3>Introduction</h3> |
---|
| 29 | <p>This module, part of the ADOdb package, provides both CLI and HTML |
---|
| 30 | interfaces for viewing key performance indicators of your database. |
---|
| 31 | This is very useful because web apps such as the popular phpMyAdmin |
---|
| 32 | currently do not provide effective database health monitoring tools. |
---|
| 33 | The module provides the following: </p> |
---|
| 34 | <ul> |
---|
| 35 | <li>A quick health check of your database server using <code>$perf->HealthCheck()</code> |
---|
| 36 | or <code>$perf->HealthCheckCLI()</code>. </li> |
---|
| 37 | <li>User interface for performance monitoring, <code>$perf->UI()</code>. |
---|
| 38 | This UI displays: |
---|
| 39 | <ul> |
---|
| 40 | <li>the health check, </li> |
---|
| 41 | <li>all SQL logged and their query plans, </li> |
---|
| 42 | <li>a list of all tables in the current database</li> |
---|
| 43 | <li>an interface to continiously poll the server for key |
---|
| 44 | performance indicators such as CPU, Hit Ratio, Disk I/O</li> |
---|
| 45 | <li>a form where you can enter and run SQL interactively.</li> |
---|
| 46 | </ul> |
---|
| 47 | </li> |
---|
| 48 | <li>Gives you an API to build database monitoring tools for a server |
---|
| 49 | farm, for example calling <code>$perf->DBParameter('data cache hit |
---|
| 50 | ratio')</code> returns this very important statistic in a database |
---|
| 51 | independant manner. </li> |
---|
| 52 | </ul> |
---|
| 53 | <p>ADOdb also has the ability to log all SQL executed, using <a |
---|
| 54 | href="docs-adodb.htm#logsql">LogSQL</a>. All SQL logged can be |
---|
| 55 | analyzed through the performance monitor <a href="#ui">UI</a>. In the <i>View |
---|
| 56 | SQL</i> mode, we categorize the SQL into 3 types: |
---|
| 57 | </p> |
---|
| 58 | <ul> |
---|
| 59 | <li><b>Suspicious SQL</b>: queries with high average execution times, |
---|
| 60 | and are potential candidates for rewriting</li> |
---|
| 61 | <li><b>Expensive SQL</b>: queries with high total execution times |
---|
| 62 | (#executions * avg execution time). Optimizing these queries will |
---|
| 63 | reduce your database server load.</li> |
---|
| 64 | <li><b>Invalid SQL</b>: queries that generate errors.</li> |
---|
| 65 | </ul> |
---|
| 66 | <p>Each query is hyperlinked to a description of the query plan, and |
---|
| 67 | every PHP script that executed that query is also shown.</p> |
---|
| 68 | <p>Please note that the information presented is a very basic database |
---|
| 69 | health check, and does not provide a complete overview of database |
---|
| 70 | performance. Although some attempt has been made to make it work across |
---|
| 71 | multiple databases in the same way, it is impossible to do so. For the |
---|
| 72 | health check, we do try to display the following key database |
---|
| 73 | parameters for all drivers:</p> |
---|
| 74 | <ul> |
---|
| 75 | <li><b>data cache size</b> - The amount of memory allocated to the |
---|
| 76 | cache.</li> |
---|
| 77 | <li><b>data cache hit ratio</b> - A measure of how effective the |
---|
| 78 | cache is, as a percentage. The higher, the better.</li> |
---|
| 79 | <li><b>current connections</b> - The number of sessions currently |
---|
| 80 | connected to the database. </li> |
---|
| 81 | </ul> |
---|
| 82 | <p>You will need to connect to the database as an administrator to view |
---|
| 83 | most of the parameters. </p> |
---|
| 84 | <p>Code improvements as very welcome, particularly adding new database |
---|
| 85 | parameters and automated tuning hints.</p> |
---|
| 86 | <a name="usage"></a> |
---|
| 87 | <h3>Usage</h3> |
---|
| 88 | <p>Currently, the following drivers: <em>mysql</em>, <em>postgres</em>, |
---|
| 89 | <em>oci8</em>, <em>mssql</em>, <i>informix</i> and <em>db2</em> are |
---|
| 90 | supported. To create a new performance monitor, call NewPerfMonitor( ) |
---|
| 91 | as demonstrated below: </p> |
---|
| 92 | <pre><?php<br>include_once('adodb.inc.php');<br>session_start(); <font |
---|
| 93 | color="#006600"># session variables required for monitoring</font><br>$conn = ADONewConnection($driver);<br>$conn->Connect($server,$user,$pwd,$db);<br>$perf =& NewPerfMonitor($conn);<br>$perf->UI($pollsecs=5);<br>?><br></pre> |
---|
| 94 | <p>It is also possible to retrieve a single database parameter:</p> |
---|
| 95 | <pre>$size = $perf->DBParameter('data cache size');<br></pre> |
---|
| 96 | <p> |
---|
| 97 | Thx to Fernando Ortiz for the informix module. </p> |
---|
| 98 | <h3>Methods</h3> |
---|
| 99 | <a name="ui"></a> |
---|
| 100 | <p><font face="Courier New, Courier, mono">function <b>UI($pollsecs=5)</b></font></p> |
---|
| 101 | <p>Creates a web-based user interface for performance monitoring. When |
---|
| 102 | you click on Poll, server statistics will be displayed every $pollsecs |
---|
| 103 | seconds. See <a href="#usage">Usage</a> above. </p> |
---|
| 104 | <p>Since 4.11, we allow users to enter and run SQL interactively via |
---|
| 105 | the "Run SQL" link. To disable this for security reasons, set this |
---|
| 106 | constant before calling $perf->UI(). </p> |
---|
| 107 | <p> </p> |
---|
| 108 | <pre>define('ADODB_PERF_NO_RUN_SQL',1);</pre> |
---|
| 109 | <p>Sample output follows below:</p> |
---|
| 110 | <table bgcolor="lightyellow" border="1" width="100%"> |
---|
| 111 | <tbody> |
---|
| 112 | <tr> |
---|
| 113 | <td> <b><a href="http://php.weblogs.com/adodb?perf=1">ADOdb</a> |
---|
| 114 | Performance Monitor</b> for localhost, db=test<br> |
---|
| 115 | <font size="-1">PostgreSQL 7.3.2 on i686-pc-cygwin, compiled by |
---|
| 116 | GCC gcc (GCC) 3.2 20020927 (prerelease)</font></td> |
---|
| 117 | </tr> |
---|
| 118 | <tr> |
---|
| 119 | <td> <a href="#">Performance Stats</a> <a href="#">View |
---|
| 120 | SQL</a> <a href="#">View Tables</a> <a href="#">Poll |
---|
| 121 | Stats</a></td> |
---|
| 122 | </tr> |
---|
| 123 | </tbody> |
---|
| 124 | </table> |
---|
| 125 | <table bgcolor="white" border="1"> |
---|
| 126 | <tbody> |
---|
| 127 | <tr> |
---|
| 128 | <td colspan="3"> |
---|
| 129 | <h3>postgres7</h3> |
---|
| 130 | </td> |
---|
| 131 | </tr> |
---|
| 132 | <tr> |
---|
| 133 | <td><b>Parameter</b></td> |
---|
| 134 | <td><b>Value</b></td> |
---|
| 135 | <td><b>Description</b></td> |
---|
| 136 | </tr> |
---|
| 137 | <tr bgcolor="#f0f0f0"> |
---|
| 138 | <td colspan="3"><i>Ratios</i> </td> |
---|
| 139 | </tr> |
---|
| 140 | <tr> |
---|
| 141 | <td>statistics collector</td> |
---|
| 142 | <td>TRUE</td> |
---|
| 143 | <td>Value must be TRUE to enable hit ratio statistics (<i>stats_start_collector</i>,<i>stats_row_level</i> |
---|
| 144 | and <i>stats_block_level</i> must be set to true in postgresql.conf)</td> |
---|
| 145 | </tr> |
---|
| 146 | <tr> |
---|
| 147 | <td>data cache hit ratio</td> |
---|
| 148 | <td>99.7967555299239</td> |
---|
| 149 | <td> </td> |
---|
| 150 | </tr> |
---|
| 151 | <tr bgcolor="#f0f0f0"> |
---|
| 152 | <td colspan="3"><i>IO</i> </td> |
---|
| 153 | </tr> |
---|
| 154 | <tr> |
---|
| 155 | <td>data reads</td> |
---|
| 156 | <td>125</td> |
---|
| 157 | <td> </td> |
---|
| 158 | </tr> |
---|
| 159 | <tr> |
---|
| 160 | <td>data writes</td> |
---|
| 161 | <td>21.78125000000000000</td> |
---|
| 162 | <td>Count of inserts/updates/deletes * coef</td> |
---|
| 163 | </tr> |
---|
| 164 | <tr bgcolor="#f0f0f0"> |
---|
| 165 | <td colspan="3"><i>Data Cache</i> </td> |
---|
| 166 | </tr> |
---|
| 167 | <tr> |
---|
| 168 | <td>data cache buffers</td> |
---|
| 169 | <td>640</td> |
---|
| 170 | <td>Number of cache buffers. <a |
---|
| 171 | href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic">Tuning</a></td> |
---|
| 172 | </tr> |
---|
| 173 | <tr> |
---|
| 174 | <td>cache blocksize</td> |
---|
| 175 | <td>8192</td> |
---|
| 176 | <td>(estimate)</td> |
---|
| 177 | </tr> |
---|
| 178 | <tr> |
---|
| 179 | <td>data cache size</td> |
---|
| 180 | <td>5M</td> |
---|
| 181 | <td> </td> |
---|
| 182 | </tr> |
---|
| 183 | <tr> |
---|
| 184 | <td>operating system cache size</td> |
---|
| 185 | <td>80M</td> |
---|
| 186 | <td>(effective cache size)</td> |
---|
| 187 | </tr> |
---|
| 188 | <tr bgcolor="#f0f0f0"> |
---|
| 189 | <td colspan="3"><i>Memory Usage</i> </td> |
---|
| 190 | </tr> |
---|
| 191 | <tr> |
---|
| 192 | <td>sort buffer size</td> |
---|
| 193 | <td>1M</td> |
---|
| 194 | <td>Size of sort buffer (per query)</td> |
---|
| 195 | </tr> |
---|
| 196 | <tr bgcolor="#f0f0f0"> |
---|
| 197 | <td colspan="3"><i>Connections</i> </td> |
---|
| 198 | </tr> |
---|
| 199 | <tr> |
---|
| 200 | <td>current connections</td> |
---|
| 201 | <td>0</td> |
---|
| 202 | <td> </td> |
---|
| 203 | </tr> |
---|
| 204 | <tr> |
---|
| 205 | <td>max connections</td> |
---|
| 206 | <td>32</td> |
---|
| 207 | <td> </td> |
---|
| 208 | </tr> |
---|
| 209 | <tr bgcolor="#f0f0f0"> |
---|
| 210 | <td colspan="3"><i>Parameters</i> </td> |
---|
| 211 | </tr> |
---|
| 212 | <tr> |
---|
| 213 | <td>rollback buffers</td> |
---|
| 214 | <td>8</td> |
---|
| 215 | <td>WAL buffers</td> |
---|
| 216 | </tr> |
---|
| 217 | <tr> |
---|
| 218 | <td>random page cost</td> |
---|
| 219 | <td>4</td> |
---|
| 220 | <td>Cost of doing a seek (default=4). See <a |
---|
| 221 | href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#less">random_page_cost</a></td> |
---|
| 222 | </tr> |
---|
| 223 | </tbody> |
---|
| 224 | </table> |
---|
| 225 | <p><font face="Courier New, Courier, mono">function <b>HealthCheck</b>()</font></p> |
---|
| 226 | <p>Returns database health check parameters as a HTML table. You will |
---|
| 227 | need to echo or print the output of this function,</p> |
---|
| 228 | <p><font face="Courier New, Courier, mono">function <b>HealthCheckCLI</b>()</font></p> |
---|
| 229 | <p>Returns database health check parameters formatted for a command |
---|
| 230 | line interface. You will need to echo or print the output of this |
---|
| 231 | function. Sample output for mysql:</p> |
---|
| 232 | <pre>-- Ratios -- <br> MyISAM cache hit ratio => 56.5635738832 <br> InnoDB cache hit ratio => 0 <br> sql cache hit ratio => 0 <br> -- IO -- <br> data reads => 2622 <br> data writes => 2415.5 <br> -- Data Cache -- <br> MyISAM data cache size => 512K <br> BDB data cache size => 8388600<br> InnoDB data cache size => 8M<br> -- Memory Pools -- <br> read buffer size => 131072 <br> sort buffer size => 65528 <br> table cache => 4 <br> -- Connections -- <br> current connections => 3<br> max connections => 100</pre> |
---|
| 233 | <p><font face="Courier New, Courier, mono">function <b>Poll</b>($pollSecs=5) |
---|
| 234 | </font> </p> |
---|
| 235 | <p> Run in infinite loop, displaying the following information every |
---|
| 236 | $pollSecs. This will not work properly if output buffering is enabled. |
---|
| 237 | In the example below, $pollSecs=3: |
---|
| 238 | </p> |
---|
| 239 | <pre>Accumulating statistics...<br> Time WS-CPU% Hit% Sess Reads/s Writes/s<br>11:08:30 0.7 56.56 1 0.0000 0.0000<br>11:08:33 1.8 56.56 2 0.0000 0.0000<br>11:08:36 11.1 56.55 3 2.5000 0.0000<br>11:08:39 9.8 56.55 2 3.1121 0.0000<br>11:08:42 2.8 56.55 1 0.0000 0.0000<br>11:08:45 7.4 56.55 2 0.0000 1.5000<br></pre> |
---|
| 240 | <p><b>WS-CPU%</b> is the Web Server CPU load of the server that PHP is |
---|
| 241 | running from (eg. the database client), and not the database. The <b>Hit%</b> |
---|
| 242 | is the data cache hit ratio. <b>Sess</b> is the current number of |
---|
| 243 | sessions connected to the database. If you are using persistent |
---|
| 244 | connections, this should not change much. The <b>Reads/s</b> and <b>Writes/s</b> |
---|
| 245 | are synthetic values to give the viewer a rough guide to I/O, and are |
---|
| 246 | not to be taken literally. </p> |
---|
| 247 | <p><font face="Courier New, Courier, mono">function <b>SuspiciousSQL</b>($numsql=10)</font></p> |
---|
| 248 | <p>Returns SQL which have high average execution times as a HTML table. |
---|
| 249 | Each sql statement |
---|
| 250 | is hyperlinked to a new window which details the execution plan and the |
---|
| 251 | scripts that execute this SQL. |
---|
| 252 | </p> |
---|
| 253 | <p> The number of statements returned is determined by $numsql. Data is |
---|
| 254 | taken from the adodb_logsql table, where the sql statements are logged |
---|
| 255 | when |
---|
| 256 | $connection->LogSQL(true) is enabled. The adodb_logsql table is |
---|
| 257 | populated using <a href="docs-adodb.htm#logsql">$conn->LogSQL</a>. |
---|
| 258 | </p> |
---|
| 259 | <p>For Oracle, Ixora Suspicious SQL returns a list of SQL statements |
---|
| 260 | that are most cache intensive as a HTML table. These are data intensive |
---|
| 261 | SQL statements that could benefit most from tuning. </p> |
---|
| 262 | <p><font face="Courier New, Courier, mono">function <b>ExpensiveSQL</b>($numsql=10)</font></p> |
---|
| 263 | <p>Returns SQL whose total execution time (avg time * #executions) is |
---|
| 264 | high as a HTML table. Each sql statement |
---|
| 265 | is hyperlinked to a new window which details the execution plan and the |
---|
| 266 | scripts that execute this SQL. |
---|
| 267 | </p> |
---|
| 268 | <p> The number of statements returned is determined by $numsql. Data is |
---|
| 269 | taken from the adodb_logsql table, where the sql statements are logged |
---|
| 270 | when |
---|
| 271 | $connection->LogSQL(true) is enabled. The adodb_logsql table is |
---|
| 272 | populated using <a href="docs-adodb.htm#logsql">$conn->LogSQL</a>. |
---|
| 273 | </p> |
---|
| 274 | <p>For Oracle, Ixora Expensive SQL returns a list of SQL statements |
---|
| 275 | that are taking the most CPU load when run. |
---|
| 276 | </p> |
---|
| 277 | <p><font face="Courier New, Courier, mono">function <b>InvalidSQL</b>($numsql=10)</font></p> |
---|
| 278 | <p>Returns a list of invalid SQL as an HTML table. |
---|
| 279 | </p> |
---|
| 280 | <p>Data is taken from the adodb_logsql table, where the sql statements |
---|
| 281 | are logged when |
---|
| 282 | $connection->LogSQL(true) is enabled. |
---|
| 283 | </p> |
---|
| 284 | <p><font face="Courier New, Courier, mono">function <b>Tables</b>($orderby=1)</font></p> |
---|
| 285 | <p>Returns information on all tables in a database, with the first two |
---|
| 286 | fields containing the table name and table size, the remaining fields |
---|
| 287 | depend on the database driver. If $orderby is set to 1, it will sort by |
---|
| 288 | name. If $orderby is set to 2, then it will sort by table size. Some |
---|
| 289 | database drivers (mssql and mysql) will ignore the $orderby clause. For |
---|
| 290 | postgresql, the information is up-to-date since the last <i>vacuum</i>. |
---|
| 291 | Not supported currently for db2.</p> |
---|
| 292 | <h3>Raw Functions</h3> |
---|
| 293 | <p>Raw functions return values without any formatting.</p> |
---|
| 294 | <p><font face="Courier New, Courier, mono">function <b>DBParameter</b>($paramname)</font></p> |
---|
| 295 | <p>Returns the value of a database parameter, such as |
---|
| 296 | $this->DBParameter("data cache size").</p> |
---|
| 297 | <p><font face="Courier New, Courier, mono">function <b>CPULoad</b>()</font></p> |
---|
| 298 | <p>Returns the CPU load of the database client (NOT THE SERVER) as a |
---|
| 299 | percentage. Only works for Linux and Windows. For Windows, WMI must be |
---|
| 300 | available.</p> |
---|
| 301 | <h3>Format of $settings Property</h3> |
---|
| 302 | <p> To create new database parameters, you need to understand |
---|
| 303 | $settings. The $settings data structure is an associative array. Each |
---|
| 304 | element of the array defines a database parameter. The key is the name |
---|
| 305 | of the database parameter. If no key is defined, then it is assumed to |
---|
| 306 | be a section break, and the value is the name of the section break. If |
---|
| 307 | this is too confusing, looking at the source code will help a lot!</p> |
---|
| 308 | <p> Each database parameter is itself an array consisting of the |
---|
| 309 | following elements:</p> |
---|
| 310 | <ol start="0"> |
---|
| 311 | <li> Category code, used to group related db parameters. If the |
---|
| 312 | category code is 'HIDE', then |
---|
| 313 | the database parameter is not shown when HTML() is called. <br> |
---|
| 314 | </li> |
---|
| 315 | <li> either |
---|
| 316 | <ol type="a"> |
---|
| 317 | <li>sql string to retrieve value, eg. "select value from |
---|
| 318 | v\$parameter where name='db_block_size'", </li> |
---|
| 319 | <li>array holding sql string and field to look for, e.g. |
---|
| 320 | array('show variables','table_cache'); optional 3rd parameter is the |
---|
| 321 | $rs->fields[$index] to use (otherwise $index=1), and optional 4th |
---|
| 322 | parameter is a constant to multiply the result with (typically 100 for |
---|
| 323 | percentage calculations),</li> |
---|
| 324 | <li>a string prefixed by =, then a PHP method of the class is |
---|
| 325 | invoked, e.g. to invoke $this->GetIndexValue(), set this array |
---|
| 326 | element to '=GetIndexValue', <br> |
---|
| 327 | </li> |
---|
| 328 | </ol> |
---|
| 329 | </li> |
---|
| 330 | <li> Description of database parameter. If description begins with an |
---|
| 331 | =, then it is interpreted as a method call, just as in (1c) above, |
---|
| 332 | taking one parameter, the current value. E.g. '=GetIndexDescription' |
---|
| 333 | will invoke $this->GetIndexDescription($val). This is useful for |
---|
| 334 | generating tuning suggestions. For an example, see WarnCacheRatio().</li> |
---|
| 335 | </ol> |
---|
| 336 | <p>Example from MySQL, table_cache database parameter:</p> |
---|
| 337 | <pre>'table cache' => array('CACHE', # category code<br> array("show variables", 'table_cache'), # array (type 1b)<br> 'Number of tables to keep open'), # description</pre> |
---|
| 338 | <h3>Example Health Check Output</h3> |
---|
| 339 | <p><a href="#db2">db2</a> <a href="#informix">informix</a> <a |
---|
| 340 | href="#mysql">mysql</a> <a href="#mssql">mssql</a> <a href="#oci8">oci8</a> |
---|
| 341 | <a href="#postgres">postgres</a></p> |
---|
| 342 | <p><a name="db2"></a></p> |
---|
| 343 | <table bgcolor="white" border="1"> |
---|
| 344 | <tbody> |
---|
| 345 | <tr> |
---|
| 346 | <td colspan="3"> |
---|
| 347 | <h3>db2</h3> |
---|
| 348 | </td> |
---|
| 349 | </tr> |
---|
| 350 | <tr> |
---|
| 351 | <td><b>Parameter</b></td> |
---|
| 352 | <td><b>Value</b></td> |
---|
| 353 | <td><b>Description</b></td> |
---|
| 354 | </tr> |
---|
| 355 | <tr bgcolor="#f0f0f0"> |
---|
| 356 | <td colspan="3"><i>Ratios</i> </td> |
---|
| 357 | </tr> |
---|
| 358 | <tr bgcolor="#ffffff"> |
---|
| 359 | <td>data cache hit ratio</td> |
---|
| 360 | <td>0 </td> |
---|
| 361 | <td> </td> |
---|
| 362 | </tr> |
---|
| 363 | <tr bgcolor="#f0f0f0"> |
---|
| 364 | <td colspan="3"><i>Data Cache</i></td> |
---|
| 365 | </tr> |
---|
| 366 | <tr bgcolor="#ffffff"> |
---|
| 367 | <td>data cache buffers</td> |
---|
| 368 | <td>250 </td> |
---|
| 369 | <td>See <a |
---|
| 370 | href="http://www7b.boulder.ibm.com/dmdd/library/techarticle/anshum/0107anshum.html#bufferpoolsize">tuning |
---|
| 371 | reference</a>.</td> |
---|
| 372 | </tr> |
---|
| 373 | <tr bgcolor="#ffffff"> |
---|
| 374 | <td>cache blocksize</td> |
---|
| 375 | <td>4096 </td> |
---|
| 376 | <td> </td> |
---|
| 377 | </tr> |
---|
| 378 | <tr bgcolor="#ffffff"> |
---|
| 379 | <td>data cache size</td> |
---|
| 380 | <td>1000K </td> |
---|
| 381 | <td> </td> |
---|
| 382 | </tr> |
---|
| 383 | <tr bgcolor="#f0f0f0"> |
---|
| 384 | <td colspan="3"><i>Connections</i></td> |
---|
| 385 | </tr> |
---|
| 386 | <tr bgcolor="#ffffff"> |
---|
| 387 | <td>current connections</td> |
---|
| 388 | <td>2 </td> |
---|
| 389 | <td> </td> |
---|
| 390 | </tr> |
---|
| 391 | </tbody> |
---|
| 392 | </table> |
---|
| 393 | <p> </p> |
---|
| 394 | <p><a name="informix"></a> |
---|
| 395 | <table bgcolor="white" border="1"> |
---|
| 396 | <tbody> |
---|
| 397 | <tr> |
---|
| 398 | <td colspan="3"> |
---|
| 399 | <h3>informix</h3> |
---|
| 400 | </td> |
---|
| 401 | </tr> |
---|
| 402 | <tr> |
---|
| 403 | <td><b>Parameter</b></td> |
---|
| 404 | <td><b>Val |
---|
| 405 | ue</b></td> |
---|
| 406 | <td><b>Description</b></td> |
---|
| 407 | </tr> |
---|
| 408 | <tr bgcolor="#f0f0f0"> |
---|
| 409 | <td colspan="3"><i>Ratios</i> </td> |
---|
| 410 | </tr> |
---|
| 411 | <tr> |
---|
| 412 | <td>data cache hit |
---|
| 413 | ratio</td> |
---|
| 414 | <td>95.89</td> |
---|
| 415 | <td> </td> |
---|
| 416 | </tr> |
---|
| 417 | <tr bgcolor="#f0f0f0"> |
---|
| 418 | <td colspan="3"><i>IO</i> </td> |
---|
| 419 | </tr> |
---|
| 420 | <tr> |
---|
| 421 | <td>data |
---|
| 422 | reads</td> |
---|
| 423 | <td>1883884</td> |
---|
| 424 | <td>Page reads</td> |
---|
| 425 | </tr> |
---|
| 426 | <tr> |
---|
| 427 | <td>data writes</td> |
---|
| 428 | <td>1716724</td> |
---|
| 429 | <td>Page writes</td> |
---|
| 430 | </tr> |
---|
| 431 | <tr bgcolor="#f0f0f0"> |
---|
| 432 | <td colspan="3"><i>Connections</i> |
---|
| 433 | </td> |
---|
| 434 | </tr> |
---|
| 435 | <tr> |
---|
| 436 | <td>current connections</td> |
---|
| 437 | <td>263.0</td> |
---|
| 438 | <td>Number of |
---|
| 439 | sessions</td> |
---|
| 440 | </tr> |
---|
| 441 | </tbody> |
---|
| 442 | </table> |
---|
| 443 | </p> |
---|
| 444 | <p> </p> |
---|
| 445 | <p><a name="mysql" id="mysql"></a></p> |
---|
| 446 | <table bgcolor="white" border="1"> |
---|
| 447 | <tbody> |
---|
| 448 | <tr> |
---|
| 449 | <td colspan="3"> |
---|
| 450 | <h3>mysql</h3> |
---|
| 451 | </td> |
---|
| 452 | </tr> |
---|
| 453 | <tr> |
---|
| 454 | <td><b>Parameter</b></td> |
---|
| 455 | <td><b>Value</b></td> |
---|
| 456 | <td><b>Description</b></td> |
---|
| 457 | </tr> |
---|
| 458 | <tr bgcolor="#f0f0f0"> |
---|
| 459 | <td colspan="3"><i>Ratios</i> </td> |
---|
| 460 | </tr> |
---|
| 461 | <tr> |
---|
| 462 | <td>MyISAM cache hit ratio</td> |
---|
| 463 | <td>56.5658301822</td> |
---|
| 464 | <td><font color="red"><b>Cache ratio should be at least 90%</b></font></td> |
---|
| 465 | </tr> |
---|
| 466 | <tr> |
---|
| 467 | <td>InnoDB cache hit ratio</td> |
---|
| 468 | <td>0</td> |
---|
| 469 | <td><font color="red"><b>Cache ratio should be at least 90%</b></font></td> |
---|
| 470 | </tr> |
---|
| 471 | <tr> |
---|
| 472 | <td>sql cache hit ratio</td> |
---|
| 473 | <td>0</td> |
---|
| 474 | <td> </td> |
---|
| 475 | </tr> |
---|
| 476 | <tr bgcolor="#f0f0f0"> |
---|
| 477 | <td colspan="3"><i>IO</i> </td> |
---|
| 478 | </tr> |
---|
| 479 | <tr> |
---|
| 480 | <td>data reads</td> |
---|
| 481 | <td>2622</td> |
---|
| 482 | <td>Number of selects (Key_reads is not accurate)</td> |
---|
| 483 | </tr> |
---|
| 484 | <tr> |
---|
| 485 | <td>data writes</td> |
---|
| 486 | <td>2415.5</td> |
---|
| 487 | <td>Number of inserts/updates/deletes * coef (Key_writes is not |
---|
| 488 | accurate)</td> |
---|
| 489 | </tr> |
---|
| 490 | <tr bgcolor="#f0f0f0"> |
---|
| 491 | <td colspan="3"><i>Data Cache</i> </td> |
---|
| 492 | </tr> |
---|
| 493 | <tr> |
---|
| 494 | <td>MyISAM data cache size</td> |
---|
| 495 | <td>512K</td> |
---|
| 496 | <td> </td> |
---|
| 497 | </tr> |
---|
| 498 | <tr> |
---|
| 499 | <td>BDB data cache size</td> |
---|
| 500 | <td>8388600</td> |
---|
| 501 | <td> </td> |
---|
| 502 | </tr> |
---|
| 503 | <tr> |
---|
| 504 | <td>InnoDB data cache size</td> |
---|
| 505 | <td>8M</td> |
---|
| 506 | <td> </td> |
---|
| 507 | </tr> |
---|
| 508 | <tr bgcolor="#f0f0f0"> |
---|
| 509 | <td colspan="3"><i>Memory Pools</i> </td> |
---|
| 510 | </tr> |
---|
| 511 | <tr> |
---|
| 512 | <td>read buffer size</td> |
---|
| 513 | <td>131072</td> |
---|
| 514 | <td>(per session)</td> |
---|
| 515 | </tr> |
---|
| 516 | <tr> |
---|
| 517 | <td>sort buffer size</td> |
---|
| 518 | <td>65528</td> |
---|
| 519 | <td>Size of sort buffer (per session)</td> |
---|
| 520 | </tr> |
---|
| 521 | <tr> |
---|
| 522 | <td>table cache</td> |
---|
| 523 | <td>4</td> |
---|
| 524 | <td>Number of tables to keep open</td> |
---|
| 525 | </tr> |
---|
| 526 | <tr bgcolor="#f0f0f0"> |
---|
| 527 | <td colspan="3"><i>Connections</i> </td> |
---|
| 528 | </tr> |
---|
| 529 | <tr> |
---|
| 530 | <td>current connections</td> |
---|
| 531 | <td>3</td> |
---|
| 532 | <td> </td> |
---|
| 533 | </tr> |
---|
| 534 | <tr> |
---|
| 535 | <td>max connections</td> |
---|
| 536 | <td>100</td> |
---|
| 537 | <td> </td> |
---|
| 538 | </tr> |
---|
| 539 | </tbody> |
---|
| 540 | </table> |
---|
| 541 | <p> </p> |
---|
| 542 | <p><a name="mssql" id="mssql"></a></p> |
---|
| 543 | <table bgcolor="white" border="1"> |
---|
| 544 | <tbody> |
---|
| 545 | <tr> |
---|
| 546 | <td colspan="3"> |
---|
| 547 | <h3>mssql</h3> |
---|
| 548 | </td> |
---|
| 549 | </tr> |
---|
| 550 | <tr> |
---|
| 551 | <td><b>Parameter</b></td> |
---|
| 552 | <td><b>Value</b></td> |
---|
| 553 | <td><b>Description</b></td> |
---|
| 554 | </tr> |
---|
| 555 | <tr bgcolor="#f0f0f0"> |
---|
| 556 | <td colspan="3"><i>Ratios</i> </td> |
---|
| 557 | </tr> |
---|
| 558 | <tr> |
---|
| 559 | <td>data cache hit ratio</td> |
---|
| 560 | <td>99.9999694824</td> |
---|
| 561 | <td> </td> |
---|
| 562 | </tr> |
---|
| 563 | <tr> |
---|
| 564 | <td>prepared sql hit ratio</td> |
---|
| 565 | <td>99.7738579828</td> |
---|
| 566 | <td> </td> |
---|
| 567 | </tr> |
---|
| 568 | <tr> |
---|
| 569 | <td>adhoc sql hit ratio</td> |
---|
| 570 | <td>98.4540169133</td> |
---|
| 571 | <td> </td> |
---|
| 572 | </tr> |
---|
| 573 | <tr bgcolor="#f0f0f0"> |
---|
| 574 | <td colspan="3"><i>IO</i> </td> |
---|
| 575 | </tr> |
---|
| 576 | <tr> |
---|
| 577 | <td>data reads</td> |
---|
| 578 | <td>2858</td> |
---|
| 579 | <td> </td> |
---|
| 580 | </tr> |
---|
| 581 | <tr> |
---|
| 582 | <td>data writes</td> |
---|
| 583 | <td>1438</td> |
---|
| 584 | <td> </td> |
---|
| 585 | </tr> |
---|
| 586 | <tr bgcolor="#f0f0f0"> |
---|
| 587 | <td colspan="3"><i>Data Cache</i> </td> |
---|
| 588 | </tr> |
---|
| 589 | <tr> |
---|
| 590 | <td>data cache size</td> |
---|
| 591 | <td>4362</td> |
---|
| 592 | <td>in K</td> |
---|
| 593 | </tr> |
---|
| 594 | <tr bgcolor="#f0f0f0"> |
---|
| 595 | <td colspan="3"><i>Connections</i> </td> |
---|
| 596 | </tr> |
---|
| 597 | <tr> |
---|
| 598 | <td>current connections</td> |
---|
| 599 | <td>14</td> |
---|
| 600 | <td> </td> |
---|
| 601 | </tr> |
---|
| 602 | <tr> |
---|
| 603 | <td>max connections</td> |
---|
| 604 | <td>32767</td> |
---|
| 605 | <td> </td> |
---|
| 606 | </tr> |
---|
| 607 | </tbody> |
---|
| 608 | </table> |
---|
| 609 | <p> </p> |
---|
| 610 | <p><a name="oci8" id="oci8"></a></p> |
---|
| 611 | <table bgcolor="white" border="1"> |
---|
| 612 | <tbody> |
---|
| 613 | <tr> |
---|
| 614 | <td colspan="3"> |
---|
| 615 | <h3>oci8</h3> |
---|
| 616 | </td> |
---|
| 617 | </tr> |
---|
| 618 | <tr> |
---|
| 619 | <td><b>Parameter</b></td> |
---|
| 620 | <td><b>Value</b></td> |
---|
| 621 | <td><b>Description</b></td> |
---|
| 622 | </tr> |
---|
| 623 | <tr bgcolor="#f0f0f0"> |
---|
| 624 | <td colspan="3"><i>Ratios</i> </td> |
---|
| 625 | </tr> |
---|
| 626 | <tr> |
---|
| 627 | <td>data cache hit ratio</td> |
---|
| 628 | <td>96.98</td> |
---|
| 629 | <td> </td> |
---|
| 630 | </tr> |
---|
| 631 | <tr> |
---|
| 632 | <td>sql cache hit ratio</td> |
---|
| 633 | <td>99.96</td> |
---|
| 634 | <td> </td> |
---|
| 635 | </tr> |
---|
| 636 | <tr bgcolor="#f0f0f0"> |
---|
| 637 | <td colspan="3"><i>IO</i> </td> |
---|
| 638 | </tr> |
---|
| 639 | <tr> |
---|
| 640 | <td>data reads</td> |
---|
| 641 | <td>842938</td> |
---|
| 642 | <td> </td> |
---|
| 643 | </tr> |
---|
| 644 | <tr> |
---|
| 645 | <td>data writes</td> |
---|
| 646 | <td>16852</td> |
---|
| 647 | <td> </td> |
---|
| 648 | </tr> |
---|
| 649 | <tr bgcolor="#f0f0f0"> |
---|
| 650 | <td colspan="3"><i>Data Cache</i> </td> |
---|
| 651 | </tr> |
---|
| 652 | <tr> |
---|
| 653 | <td>data cache buffers</td> |
---|
| 654 | <td>3072</td> |
---|
| 655 | <td>Number of cache buffers</td> |
---|
| 656 | </tr> |
---|
| 657 | <tr> |
---|
| 658 | <td>data cache blocksize</td> |
---|
| 659 | <td>8192</td> |
---|
| 660 | <td> </td> |
---|
| 661 | </tr> |
---|
| 662 | <tr> |
---|
| 663 | <td>data cache size</td> |
---|
| 664 | <td>48M</td> |
---|
| 665 | <td>shared_pool_size</td> |
---|
| 666 | </tr> |
---|
| 667 | <tr bgcolor="#f0f0f0"> |
---|
| 668 | <td colspan="3"><i>Memory Pools</i> </td> |
---|
| 669 | </tr> |
---|
| 670 | <tr> |
---|
| 671 | <td>java pool size</td> |
---|
| 672 | <td>0</td> |
---|
| 673 | <td>java_pool_size</td> |
---|
| 674 | </tr> |
---|
| 675 | <tr> |
---|
| 676 | <td>sort buffer size</td> |
---|
| 677 | <td>512K</td> |
---|
| 678 | <td>sort_area_size (per query)</td> |
---|
| 679 | </tr> |
---|
| 680 | <tr> |
---|
| 681 | <td>user session buffer size</td> |
---|
| 682 | <td>8M</td> |
---|
| 683 | <td>large_pool_size</td> |
---|
| 684 | </tr> |
---|
| 685 | <tr bgcolor="#f0f0f0"> |
---|
| 686 | <td colspan="3"><i>Connections</i> </td> |
---|
| 687 | </tr> |
---|
| 688 | <tr> |
---|
| 689 | <td>current connections</td> |
---|
| 690 | <td>1</td> |
---|
| 691 | <td> </td> |
---|
| 692 | </tr> |
---|
| 693 | <tr> |
---|
| 694 | <td>max connections</td> |
---|
| 695 | <td>170</td> |
---|
| 696 | <td> </td> |
---|
| 697 | </tr> |
---|
| 698 | <tr> |
---|
| 699 | <td>data cache utilization ratio</td> |
---|
| 700 | <td>88.46</td> |
---|
| 701 | <td>Percentage of data cache actually in use</td> |
---|
| 702 | </tr> |
---|
| 703 | <tr> |
---|
| 704 | <td>user cache utilization ratio</td> |
---|
| 705 | <td>91.76</td> |
---|
| 706 | <td>Percentage of user cache (large_pool) actually in use</td> |
---|
| 707 | </tr> |
---|
| 708 | <tr> |
---|
| 709 | <td>rollback segments</td> |
---|
| 710 | <td>11</td> |
---|
| 711 | <td> </td> |
---|
| 712 | </tr> |
---|
| 713 | <tr bgcolor="#f0f0f0"> |
---|
| 714 | <td colspan="3"><i>Transactions</i> </td> |
---|
| 715 | </tr> |
---|
| 716 | <tr> |
---|
| 717 | <td>peak transactions</td> |
---|
| 718 | <td>24</td> |
---|
| 719 | <td>Taken from high-water-mark</td> |
---|
| 720 | </tr> |
---|
| 721 | <tr> |
---|
| 722 | <td>max transactions</td> |
---|
| 723 | <td>187</td> |
---|
| 724 | <td>max transactions / rollback segments < 3.5 (or |
---|
| 725 | transactions_per_rollback_segment)</td> |
---|
| 726 | </tr> |
---|
| 727 | <tr bgcolor="#f0f0f0"> |
---|
| 728 | <td colspan="3"><i>Parameters</i> </td> |
---|
| 729 | </tr> |
---|
| 730 | <tr> |
---|
| 731 | <td>cursor sharing</td> |
---|
| 732 | <td>EXACT</td> |
---|
| 733 | <td>Cursor reuse strategy. Recommended is FORCE (8i+) or SIMILAR |
---|
| 734 | (9i+). See <a |
---|
| 735 | href="http://www.praetoriate.com/oracle_tips_cursor_sharing.htm">cursor_sharing</a>.</td> |
---|
| 736 | </tr> |
---|
| 737 | <tr> |
---|
| 738 | <td>index cache cost</td> |
---|
| 739 | <td>0</td> |
---|
| 740 | <td>% of indexed data blocks expected in the cache. Recommended |
---|
| 741 | is 20-80. Default is 0. See <a |
---|
| 742 | href="http://www.dba-oracle.com/oracle_tips_cbo_part1.htm">optimizer_index_caching</a>.</td> |
---|
| 743 | </tr> |
---|
| 744 | <tr> |
---|
| 745 | <td>random page cost</td> |
---|
| 746 | <td>100</td> |
---|
| 747 | <td>Recommended is 10-50 for TP, and 50 for data warehouses. |
---|
| 748 | Default is 100. See <a |
---|
| 749 | href="http://www.dba-oracle.com/oracle_tips_cost_adj.htm">optimizer_index_cost_adj</a>. |
---|
| 750 | </td> |
---|
| 751 | </tr> |
---|
| 752 | </tbody> |
---|
| 753 | </table> |
---|
| 754 | <h3>Suspicious SQL</h3> |
---|
| 755 | <table bgcolor="white" border="1"> |
---|
| 756 | <tbody> |
---|
| 757 | <tr> |
---|
| 758 | <td><b>LOAD</b></td> |
---|
| 759 | <td><b>EXECUTES</b></td> |
---|
| 760 | <td><b>SQL_TEXT</b></td> |
---|
| 761 | </tr> |
---|
| 762 | <tr> |
---|
| 763 | <td align="right"> .73%</td> |
---|
| 764 | <td align="right">89</td> |
---|
| 765 | <td>select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o, |
---|
| 766 | sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576) |
---|
| 767 | and o.obj#=t.obj# and o.owner# = u.user# select i.obj#, i.flags, |
---|
| 768 | u.name, o.name from sys.obj$ o, sys.user$ u, sys.ind$ i where |
---|
| 769 | (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512) and |
---|
| 770 | (not((i.type# = 9) and bitand(i.flags,8) = 8)) and o.obj#=i.obj# and |
---|
| 771 | o.owner# = u.user# </td> |
---|
| 772 | </tr> |
---|
| 773 | <tr> |
---|
| 774 | <td align="right"> .84%</td> |
---|
| 775 | <td align="right">3</td> |
---|
| 776 | <td>select /*+ RULE */ distinct tabs.table_name, tabs.owner , |
---|
| 777 | partitioned, iot_type , TEMPORARY, table_type, table_type_owner from |
---|
| 778 | DBA_ALL_TABLES tabs where tabs.owner = :own </td> |
---|
| 779 | </tr> |
---|
| 780 | <tr> |
---|
| 781 | <td align="right"> 3.95%</td> |
---|
| 782 | <td align="right">6</td> |
---|
| 783 | <td>SELECT round(count(1)*avg(buf.block_size)/1048576) FROM |
---|
| 784 | DBA_OBJECTS obj, V$BH bh, dba_segments seg, v$buffer_pool buf WHERE |
---|
| 785 | obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner = |
---|
| 786 | seg.owner and obj.object_name = seg.segment_name and obj.object_type = |
---|
| 787 | seg.segment_type and seg.buffer_pool = buf.name and buf.name = |
---|
| 788 | 'DEFAULT' </td> |
---|
| 789 | </tr> |
---|
| 790 | <tr> |
---|
| 791 | <td align="right"> 4.50%</td> |
---|
| 792 | <td align="right">6</td> |
---|
| 793 | <td>SELECT round(count(1)*avg(tsp.block_size)/1048576) FROM |
---|
| 794 | DBA_OBJECTS obj, V$BH bh, dba_segments seg, dba_tablespaces tsp WHERE |
---|
| 795 | obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner = |
---|
| 796 | seg.owner and obj.object_name = seg.segment_name and obj.object_type = |
---|
| 797 | seg.segment_type and seg.tablespace_name = tsp.tablespace_name </td> |
---|
| 798 | </tr> |
---|
| 799 | <tr> |
---|
| 800 | <td align="right">57.34%</td> |
---|
| 801 | <td align="right">9267</td> |
---|
| 802 | <td>select t.schema, t.name, t.flags, q.name from |
---|
| 803 | system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft, |
---|
| 804 | system.aq$_queues q where aft.table_objno = t.objno and |
---|
| 805 | aft.owner_instance = :1 and q.table_objno = t.objno and q.usage = 0 and |
---|
| 806 | bitand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, |
---|
| 807 | aft.table_objno skip locked </td> |
---|
| 808 | </tr> |
---|
| 809 | </tbody> |
---|
| 810 | </table> |
---|
| 811 | <h3>Expensive SQL</h3> |
---|
| 812 | <table bgcolor="white" border="1"> |
---|
| 813 | <tbody> |
---|
| 814 | <tr> |
---|
| 815 | <td><b>LOAD</b></td> |
---|
| 816 | <td><b>EXECUTES</b></td> |
---|
| 817 | <td><b>SQL_TEXT</b></td> |
---|
| 818 | </tr> |
---|
| 819 | <tr> |
---|
| 820 | <td align="right"> 5.24%</td> |
---|
| 821 | <td align="right">1</td> |
---|
| 822 | <td>select round(sum(bytes)/1048576) from dba_segments </td> |
---|
| 823 | </tr> |
---|
| 824 | <tr> |
---|
| 825 | <td align="right"> 6.89%</td> |
---|
| 826 | <td align="right">6</td> |
---|
| 827 | <td>SELECT round(count(1)*avg(buf.block_size)/1048576) FROM |
---|
| 828 | DBA_OBJECTS obj, V$BH bh, dba_segments seg, v$buffer_pool buf WHERE |
---|
| 829 | obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner = |
---|
| 830 | seg.owner and obj.object_name = seg.segment_name and obj.object_type = |
---|
| 831 | seg.segment_type and seg.buffer_pool = buf.name and buf.name = |
---|
| 832 | 'DEFAULT' </td> |
---|
| 833 | </tr> |
---|
| 834 | <tr> |
---|
| 835 | <td align="right"> 7.85%</td> |
---|
| 836 | <td align="right">6</td> |
---|
| 837 | <td>SELECT round(count(1)*avg(tsp.block_size)/1048576) FROM |
---|
| 838 | DBA_OBJECTS obj, V$BH bh, dba_segments seg, dba_tablespaces tsp WHERE |
---|
| 839 | obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner = |
---|
| 840 | seg.owner and obj.object_name = seg.segment_name and obj.object_type = |
---|
| 841 | seg.segment_type and seg.tablespace_name = tsp.tablespace_name </td> |
---|
| 842 | </tr> |
---|
| 843 | <tr> |
---|
| 844 | <td align="right">33.69%</td> |
---|
| 845 | <td align="right">89</td> |
---|
| 846 | <td>select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o, |
---|
| 847 | sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576) |
---|
| 848 | and o.obj#=t.obj# and o.owner# = u.user# </td> |
---|
| 849 | </tr> |
---|
| 850 | <tr> |
---|
| 851 | <td align="right">36.44%</td> |
---|
| 852 | <td align="right">89</td> |
---|
| 853 | <td>select i.obj#, i.flags, u.name, o.name from sys.obj$ o, |
---|
| 854 | sys.user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or |
---|
| 855 | bitand(i.flags, 512) = 512) and (not((i.type# = 9) and |
---|
| 856 | bitand(i.flags,8) = 8)) and o.obj#=i.obj# and o.owner# = u.user# </td> |
---|
| 857 | </tr> |
---|
| 858 | </tbody> |
---|
| 859 | </table> |
---|
| 860 | <p><a name="postgres" id="postgres"></a></p> |
---|
| 861 | <table bgcolor="white" border="1"> |
---|
| 862 | <tbody> |
---|
| 863 | <tr> |
---|
| 864 | <td colspan="3"> |
---|
| 865 | <h3>postgres7</h3> |
---|
| 866 | </td> |
---|
| 867 | </tr> |
---|
| 868 | <tr> |
---|
| 869 | <td><b>Parameter</b></td> |
---|
| 870 | <td><b>Value</b></td> |
---|
| 871 | <td><b>Description</b></td> |
---|
| 872 | </tr> |
---|
| 873 | <tr bgcolor="#f0f0f0"> |
---|
| 874 | <td colspan="3"><i>Ratios</i> </td> |
---|
| 875 | </tr> |
---|
| 876 | <tr> |
---|
| 877 | <td>statistics collector</td> |
---|
| 878 | <td>FALSE</td> |
---|
| 879 | <td>Must be set to TRUE to enable hit ratio statistics (<i>stats_start_collector</i>,<i>stats_row_level</i> |
---|
| 880 | and <i>stats_block_level</i> must be set to true in postgresql.conf)</td> |
---|
| 881 | </tr> |
---|
| 882 | <tr> |
---|
| 883 | <td>data cache hit ratio</td> |
---|
| 884 | <td>99.9666031916603</td> |
---|
| 885 | <td> </td> |
---|
| 886 | </tr> |
---|
| 887 | <tr bgcolor="#f0f0f0"> |
---|
| 888 | <td colspan="3"><i>IO</i> </td> |
---|
| 889 | </tr> |
---|
| 890 | <tr> |
---|
| 891 | <td>data reads</td> |
---|
| 892 | <td>15</td> |
---|
| 893 | <td> </td> |
---|
| 894 | </tr> |
---|
| 895 | <tr> |
---|
| 896 | <td>data writes</td> |
---|
| 897 | <td>0.000000000000000000</td> |
---|
| 898 | <td>Count of inserts/updates/deletes * coef</td> |
---|
| 899 | </tr> |
---|
| 900 | <tr bgcolor="#f0f0f0"> |
---|
| 901 | <td colspan="3"><i>Data Cache</i> </td> |
---|
| 902 | </tr> |
---|
| 903 | <tr> |
---|
| 904 | <td>data cache buffers</td> |
---|
| 905 | <td>1280</td> |
---|
| 906 | <td>Number of cache buffers. <a |
---|
| 907 | href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic">Tuning</a></td> |
---|
| 908 | </tr> |
---|
| 909 | <tr> |
---|
| 910 | <td>cache blocksize</td> |
---|
| 911 | <td>8192</td> |
---|
| 912 | <td>(estimate)</td> |
---|
| 913 | </tr> |
---|
| 914 | <tr> |
---|
| 915 | <td>data cache size</td> |
---|
| 916 | <td>10M</td> |
---|
| 917 | <td> </td> |
---|
| 918 | </tr> |
---|
| 919 | <tr> |
---|
| 920 | <td>operating system cache size</td> |
---|
| 921 | <td>80000K</td> |
---|
| 922 | <td>(effective cache size)</td> |
---|
| 923 | </tr> |
---|
| 924 | <tr bgcolor="#f0f0f0"> |
---|
| 925 | <td colspan="3"><i>Memory Pools</i> </td> |
---|
| 926 | </tr> |
---|
| 927 | <tr> |
---|
| 928 | <td>sort buffer size</td> |
---|
| 929 | <td>1M</td> |
---|
| 930 | <td>Size of sort buffer (per query)</td> |
---|
| 931 | </tr> |
---|
| 932 | <tr bgcolor="#f0f0f0"> |
---|
| 933 | <td colspan="3"><i>Connections</i> </td> |
---|
| 934 | </tr> |
---|
| 935 | <tr> |
---|
| 936 | <td>current connections</td> |
---|
| 937 | <td>13</td> |
---|
| 938 | <td> </td> |
---|
| 939 | </tr> |
---|
| 940 | <tr> |
---|
| 941 | <td>max connections</td> |
---|
| 942 | <td>32</td> |
---|
| 943 | <td> </td> |
---|
| 944 | </tr> |
---|
| 945 | <tr bgcolor="#f0f0f0"> |
---|
| 946 | <td colspan="3"><i>Parameters</i> </td> |
---|
| 947 | </tr> |
---|
| 948 | <tr> |
---|
| 949 | <td>rollback buffers</td> |
---|
| 950 | <td>8</td> |
---|
| 951 | <td>WAL buffers</td> |
---|
| 952 | </tr> |
---|
| 953 | <tr> |
---|
| 954 | <td>random page cost</td> |
---|
| 955 | <td>4</td> |
---|
| 956 | <td>Cost of doing a seek (default=4). See <a |
---|
| 957 | href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#less">random_page_cost</a></td> |
---|
| 958 | </tr> |
---|
| 959 | </tbody> |
---|
| 960 | </table> |
---|
| 961 | </body> |
---|
| 962 | </html> |
---|