4 @version v5.20.1 06-Dec-2015
5 @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
6 @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community
7 Released under both BSD license and Lesser GPL library license.
8 Whenever there is any discrepancy between the two licenses,
9 the BSD license will take precedence. See License.txt.
10 Set tabs to 4 for best viewing.
12 Latest version is available at http://adodb.sourceforge.net
14 Library for basic performance monitoring and tuning
18 // security - hide paths
19 if (!defined('ADODB_DIR')) die();
22 Notice that PostgreSQL has no sql query cache
24 class perf_postgres
extends adodb_perf
{
27 "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
28 on b.relname = 'pg_toast_'||trim(a.relfilenode)
29 left join pg_class c on c.relname = 'pg_toast_'||trim(a.relfilenode)||'_index'
30 where a.relname in (select tablename from pg_tables where tablename not like 'pg_%')";
32 var $createTableSQL = "CREATE TABLE adodb_logsql (
33 created timestamp NOT NULL,
34 sql0 varchar(250) NOT NULL,
38 timer decimal(16,6) NOT NULL
41 var $settings = array(
43 'statistics collector' => array('RATIO',
44 "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' ",
45 '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)'),
46 'data cache hit ratio' => array('RATIO',
47 "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'",
50 'data reads' => array('IO',
51 'select sum(heap_blks_read+toast_blks_read) from pg_statio_user_tables',
53 'data writes' => array('IO',
54 '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',
55 'Count of inserts/updates/deletes * coef'),
58 'data cache buffers' => array('DATAC',
59 "select setting from pg_settings where name='shared_buffers'",
60 'Number of cache buffers. <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic>Tuning</a>'),
61 'cache blocksize' => array('DATAC',
64 'data cache size' => array( 'DATAC',
65 "select setting::integer*8192 from pg_settings where name='shared_buffers'",
67 'operating system cache size' => array( 'DATA',
68 "select setting::integer*8192 from pg_settings where name='effective_cache_size'",
69 '(effective cache size)' ),
71 # Postgres 7.5 changelog: Rename server parameters SortMem and VacuumMem to work_mem and maintenance_work_mem;
72 'sort/work buffer size' => array('CACHE',
73 "select setting::integer*1024 from pg_settings where name='sort_mem' or name = 'work_mem' order by name",
74 'Size of sort buffer (per query)' ),
76 'current connections' => array('SESS',
77 'select count(*) from pg_stat_activity',
79 'max connections' => array('SESS',
80 "select setting from pg_settings where name='max_connections'",
83 'rollback buffers' => array('COST',
84 "select setting from pg_settings where name='wal_buffers'",
86 'random page cost' => array('COST',
87 "select setting from pg_settings where name='random_page_cost'",
88 'Cost of doing a seek (default=4). See <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#less>random_page_cost</a>'),
92 function __construct(&$conn)
97 var $optimizeTableLow = 'VACUUM %s';
98 var $optimizeTableHigh = 'VACUUM ANALYZE %s';
101 * @see adodb_perf#optimizeTable
104 function optimizeTable($table, $mode = ADODB_OPT_LOW
)
106 if(! is_string($table)) return false;
109 if (! $conn) return false;
113 case ADODB_OPT_LOW
: $sql = $this->optimizeTableLow
; break;
114 case ADODB_OPT_HIGH
: $sql = $this->optimizeTableHigh
; break;
117 ADOConnection
::outp(sprintf("<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__
, 'optimizeTable', $mode));
121 $sql = sprintf($sql, $table);
123 return $conn->Execute($sql) !== false;
126 function Explain($sql,$partial=false)
128 $save = $this->conn
->LogSQL(false);
131 $sqlq = $this->conn
->qstr($sql.'%');
132 $arr = $this->conn
->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq");
134 foreach($arr as $row) {
136 if (crc32($sql) == $partial) break;
140 $sql = str_replace('?',"''",$sql);
141 $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
142 $rs = $this->conn
->Execute('EXPLAIN '.$sql);
143 $this->conn
->LogSQL($save);
147 $s .= reset($rs->fields
)."\n";
151 $s .= $this->Tracer($sql,$partial);