"TYPO3 4.0.1: typo3_src-4.0.1/typo3/sysext/adodb/adodb/perf/perf-postgres.inc.php Source File", "datetime" => "Sat Dec 2 19:22:26 2006", "date" => "2 Dec 2006", "doxygenversion" => "1.4.6", "projectname" => "TYPO3 4.0.1", "projectnumber" => "4.0.1" ); get_header($doxygen_vars); ?>

perf-postgres.inc.php

00001 <?php
00002 
00003 /* 
00004 V4.90 8 June 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
00005   Released under both BSD license and Lesser GPL library license. 
00006   Whenever there is any discrepancy between the two licenses, 
00007   the BSD license will take precedence. See License.txt. 
00008   Set tabs to 4 for best viewing.
00009   
00010   Latest version is available at http://adodb.sourceforge.net
00011   
00012   Library for basic performance monitoring and tuning 
00013   
00014 */
00015 
00016 // security - hide paths
00017 if (!defined('ADODB_DIR')) die();
00018 
00019 /*
00020         Notice that PostgreSQL has no sql query cache
00021 */
00022 class perf_postgres extends adodb_perf{
00023         
00024         var $tablesSQL = 
00025         "select a.relname as tablename,(a.relpages+CASE WHEN b.relpages is null THEN 0 ELSE b.relpages END+CASE WHEN c.relpages is null THEN 0 ELSE c.relpages END)*8 as size_in_K,a.relfilenode as \"OID\"  from pg_class a left join pg_class b
00026                 on b.relname = 'pg_toast_'||trim(a.relfilenode) 
00027                 left join pg_class c on c.relname = 'pg_toast_'||trim(a.relfilenode)||'_index'
00028                 where a.relname in (select tablename from pg_tables where tablename not like 'pg_%')";
00029         
00030         var $createTableSQL = "CREATE TABLE adodb_logsql (
00031                   created timestamp NOT NULL,
00032                   sql0 varchar(250) NOT NULL,
00033                   sql1 text NOT NULL,
00034                   params text NOT NULL,
00035                   tracer text NOT NULL,
00036                   timer decimal(16,6) NOT NULL
00037                 )";     
00038         
00039         var $settings = array(
00040         'Ratios',
00041                 'statistics collector' => array('RATIO',
00042                         "select case when count(*)=3 then 'TRUE' else 'FALSE' end from pg_settings where (name='stats_block_level' or name='stats_row_level' or name='stats_start_collector') and setting='on' ",
00043                         'Value must be TRUE to enable hit ratio statistics (<i>stats_start_collector</i>,<i>stats_row_level</i> and <i>stats_block_level</i> must be set to true in postgresql.conf)'),
00044                 'data cache hit ratio' => array('RATIO',
00045                         "select case when blks_hit=0 then 0 else round( ((1-blks_read::float/blks_hit)*100)::numeric, 2) end from pg_stat_database where datname='\$DATABASE'",
00046                         '=WarnCacheRatio'),
00047         'IO',
00048                 'data reads' => array('IO',
00049                 'select sum(heap_blks_read+toast_blks_read) from pg_statio_user_tables',
00050                 ),
00051                 'data writes' => array('IO',
00052                 'select round((sum(n_tup_ins/4.0+n_tup_upd/8.0+n_tup_del/4.0)/16)::numeric,2) from pg_stat_user_tables',
00053                 'Count of inserts/updates/deletes * coef'),
00054 
00055         'Data Cache',
00056                 'data cache buffers' => array('DATAC',
00057                         "select setting from pg_settings where name='shared_buffers'",
00058                         'Number of cache buffers. <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic>Tuning</a>'),
00059                 'cache blocksize' => array('DATAC',
00060                         'select 8192',
00061                         '(estimate)' ),
00062                 'data cache size' => array( 'DATAC',
00063                 "select setting::integer*8192 from pg_settings where name='shared_buffers'",
00064                         '' ),
00065                 'operating system cache size' => array( 'DATA',
00066                 "select setting::integer*8192 from pg_settings where name='effective_cache_size'",
00067                         '(effective cache size)' ),
00068         'Memory Usage',
00069         # Postgres 7.5 changelog: Rename server parameters SortMem and VacuumMem to work_mem and maintenance_work_mem;
00070                 'sort/work buffer size' => array('CACHE',
00071                         "select setting::integer*1024 from pg_settings where name='sort_mem' or name = 'work_mem' order by name",
00072                         'Size of sort buffer (per query)' ),
00073         'Connections',
00074                 'current connections' => array('SESS',
00075                         'select count(*) from pg_stat_activity',
00076                         ''),
00077                 'max connections' => array('SESS',
00078                         "select setting from pg_settings where name='max_connections'",
00079                         ''),
00080         'Parameters',
00081                 'rollback buffers' => array('COST',
00082                         "select setting from pg_settings where name='wal_buffers'",
00083                         'WAL buffers'),
00084                 'random page cost' => array('COST',
00085                         "select setting from pg_settings where name='random_page_cost'",
00086                         'Cost of doing a seek (default=4). See <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#less>random_page_cost</a>'),
00087                 false
00088         );
00089         
00090         function perf_postgres(&$conn)
00091         {
00092                 $this->conn =& $conn;
00093         }
00094         
00095         function Explain($sql,$partial=false)
00096         {
00097                 $save = $this->conn->LogSQL(false);
00098                 
00099                 if ($partial) {
00100                         $sqlq = $this->conn->qstr($sql.'%');
00101                         $arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq");
00102                         if ($arr) {
00103                                 foreach($arr as $row) {
00104                                         $sql = reset($row);
00105                                         if (crc32($sql) == $partial) break;
00106                                 }
00107                         }
00108                 }
00109                 $sql = str_replace('?',"''",$sql);
00110                 $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
00111                 $rs = $this->conn->Execute('EXPLAIN '.$sql);
00112                 $this->conn->LogSQL($save);
00113                 $s .= '<pre>';
00114                 if ($rs)
00115                         while (!$rs->EOF) {
00116                                 $s .= reset($rs->fields)."\n";
00117                                 $rs->MoveNext();
00118                         }
00119                 $s .= '</pre>';
00120                 $s .= $this->Tracer($sql,$partial);
00121                 return $s;
00122         }
00123 }
00124 ?>