See Release Notes
Long Term Support Release
<?php /** * Library for basic performance monitoring and tuning * * This file is part of ADOdb, a Database Abstraction Layer library for PHP. * * @package ADOdb * @link https://adodb.org Project's web site and documentation * @link https://github.com/ADOdb/ADOdb Source code and issue tracker * * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option, * any later version. This means you can use it in proprietary products. * See the LICENSE.md file distributed with this source code for details. * @license BSD-3-Clause * @license LGPL-2.1-or-later * * @copyright 2000-2013 John Lim * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community */ // security - hide paths if (!defined('ADODB_DIR')) die(); /* Notice that PostgreSQL has no sql query cache */ class perf_postgres extends adodb_perf{ var $tablesSQL = "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 on b.relname = 'pg_toast_'||trim(a.relfilenode) left join pg_class c on c.relname = 'pg_toast_'||trim(a.relfilenode)||'_index' where a.relname in (select tablename from pg_tables where tablename not like 'pg_%')"; var $createTableSQL = "CREATE TABLE adodb_logsql ( created timestamp NOT NULL, sql0 varchar(250) NOT NULL, sql1 text NOT NULL, params text NOT NULL, tracer text NOT NULL, timer decimal(16,6) NOT NULL )"; var $settings = array( 'Ratios', 'statistics collector' => array('RATIO', "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' ", '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)'), 'data cache hit ratio' => array('RATIO', "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'", '=WarnCacheRatio'), 'IO', 'data reads' => array('IO', 'select sum(heap_blks_read+toast_blks_read) from pg_statio_user_tables', ), 'data writes' => array('IO', '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', 'Count of inserts/updates/deletes * coef'), 'Data Cache', 'data cache buffers' => array('DATAC', "select setting from pg_settings where name='shared_buffers'", 'Number of cache buffers. <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic>Tuning</a>'), 'cache blocksize' => array('DATAC', 'select 8192', '(estimate)' ), 'data cache size' => array( 'DATAC', "select setting::integer*8192 from pg_settings where name='shared_buffers'", '' ), 'operating system cache size' => array( 'DATA', "select setting::integer*8192 from pg_settings where name='effective_cache_size'", '(effective cache size)' ), 'Memory Usage', # Postgres 7.5 changelog: Rename server parameters SortMem and VacuumMem to work_mem and maintenance_work_mem; 'sort/work buffer size' => array('CACHE', "select setting::integer*1024 from pg_settings where name='sort_mem' or name = 'work_mem' order by name", 'Size of sort buffer (per query)' ), 'Connections', 'current connections' => array('SESS', 'select count(*) from pg_stat_activity', ''), 'max connections' => array('SESS', "select setting from pg_settings where name='max_connections'", ''), 'Parameters', 'rollback buffers' => array('COST', "select setting from pg_settings where name='wal_buffers'", 'WAL buffers'), 'random page cost' => array('COST', "select setting from pg_settings where name='random_page_cost'", 'Cost of doing a seek (default=4). See <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#less>random_page_cost</a>'), false ); function __construct(&$conn) { $this->conn = $conn; } var $optimizeTableLow = 'VACUUM %s'; var $optimizeTableHigh = 'VACUUM ANALYZE %s'; /**< * @see adodb_perf#optimizeTable> * @see adodb_perf::optimizeTable()*/ function optimizeTable($table, $mode = ADODB_OPT_LOW) { if(! is_string($table)) return false; $conn = $this->conn; if (! $conn) return false; $sql = ''; switch($mode) { case ADODB_OPT_LOW : $sql = $this->optimizeTableLow; break; case ADODB_OPT_HIGH: $sql = $this->optimizeTableHigh; break; default : ADOConnection::outp(sprintf("<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__, 'optimizeTable', $mode)); return false; } $sql = sprintf($sql, $table); return $conn->Execute($sql) !== false; } function Explain($sql,$partial=false) { $save = $this->conn->LogSQL(false); if ($partial) { $sqlq = $this->conn->qstr($sql.'%');< $arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq");> $arr = $this->conn->getArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");if ($arr) { foreach($arr as $row) { $sql = reset($row); if (crc32($sql) == $partial) break; } } } $sql = str_replace('?',"''",$sql); $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>'; $rs = $this->conn->Execute('EXPLAIN '.$sql); $this->conn->LogSQL($save); $s .= '<pre>'; if ($rs) while (!$rs->EOF) { $s .= reset($rs->fields)."\n"; $rs->MoveNext(); } $s .= '</pre>'; $s .= $this->Tracer($sql,$partial); return $s; } }