[Summary view]

Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403] [Versions 400 and 403]

   1  <?php
   2  /**
   3   * Library for basic performance monitoring and tuning
   4   *
   5   * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
   6   *
   7   * @package ADOdb
   8   * @link https://adodb.org Project's web site and documentation
   9   * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
  10   *
  11   * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
  12   * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
  13   * any later version. This means you can use it in proprietary products.
  14   * See the LICENSE.md file distributed with this source code for details.
  15   * @license BSD-3-Clause
  16   * @license LGPL-2.1-or-later
  17   *
  18   * @copyright 2000-2013 John Lim
  19   * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
  20   */
  21  
  22  // security - hide paths
  23  if (!defined('ADODB_DIR')) die();
  24  
  25  /*
  26  	 Notice that PostgreSQL has no sql query cache
  27  */
  28  class perf_postgres extends adodb_perf{
  29  
  30  	 var $tablesSQL =
  31  	 "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
  32  	 	 on b.relname = 'pg_toast_'||trim(a.relfilenode)
  33  	 	 left join pg_class c on c.relname = 'pg_toast_'||trim(a.relfilenode)||'_index'
  34  	 	 where a.relname in (select tablename from pg_tables where tablename not like 'pg_%')";
  35  
  36  	 var $createTableSQL = "CREATE TABLE adodb_logsql (
  37  	 	   created timestamp NOT NULL,
  38  	 	   sql0 varchar(250) NOT NULL,
  39  	 	   sql1 text NOT NULL,
  40  	 	   params text NOT NULL,
  41  	 	   tracer text NOT NULL,
  42  	 	   timer decimal(16,6) NOT NULL
  43  	 	 )";
  44  
  45  	 var $settings = array(
  46  	 'Ratios',
  47  	 	 'statistics collector' => array('RATIO',
  48  	 	 	 "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' ",
  49  	 	 	 '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)'),
  50  	 	 'data cache hit ratio' => array('RATIO',
  51  	 	 	 "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'",
  52  	 	 	 '=WarnCacheRatio'),
  53  	 'IO',
  54  	 	 'data reads' => array('IO',
  55  	 	 'select sum(heap_blks_read+toast_blks_read) from pg_statio_user_tables',
  56  	 	 ),
  57  	 	 'data writes' => array('IO',
  58  	 	 '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',
  59  	 	 'Count of inserts/updates/deletes * coef'),
  60  
  61  	 'Data Cache',
  62  	 	 'data cache buffers' => array('DATAC',
  63  	 	 	 "select setting from pg_settings where name='shared_buffers'",
  64  	 	 	 'Number of cache buffers. <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic>Tuning</a>'),
  65  	 	 'cache blocksize' => array('DATAC',
  66  	 	 	 'select 8192',
  67  	 	 	 '(estimate)' ),
  68  	 	 'data cache size' => array( 'DATAC',
  69  	 	 "select setting::integer*8192 from pg_settings where name='shared_buffers'",
  70  	 	 	 '' ),
  71  	 	 'operating system cache size' => array( 'DATA',
  72  	 	 "select setting::integer*8192 from pg_settings where name='effective_cache_size'",
  73  	 	 	 '(effective cache size)' ),
  74  	 'Memory Usage',
  75  	 # Postgres 7.5 changelog: Rename server parameters SortMem and VacuumMem to work_mem and maintenance_work_mem;
  76  	 	 'sort/work buffer size' => array('CACHE',
  77  	 	 	 "select setting::integer*1024 from pg_settings where name='sort_mem' or name = 'work_mem' order by name",
  78  	 	 	 'Size of sort buffer (per query)' ),
  79  	 'Connections',
  80  	 	 'current connections' => array('SESS',
  81  	 	 	 'select count(*) from pg_stat_activity',
  82  	 	 	 ''),
  83  	 	 'max connections' => array('SESS',
  84  	 	 	 "select setting from pg_settings where name='max_connections'",
  85  	 	 	 ''),
  86  	 'Parameters',
  87  	 	 'rollback buffers' => array('COST',
  88  	 	 	 "select setting from pg_settings where name='wal_buffers'",
  89  	 	 	 'WAL buffers'),
  90  	 	 'random page cost' => array('COST',
  91  	 	 	 "select setting from pg_settings where name='random_page_cost'",
  92  	 	 	 'Cost of doing a seek (default=4). See <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#less>random_page_cost</a>'),
  93  	 	 false
  94  	 );
  95  
  96  	function __construct(&$conn)
  97  	 {
  98  	 	 $this->conn = $conn;
  99  	 }
 100  
 101  	 var $optimizeTableLow  = 'VACUUM %s';
 102  	 var $optimizeTableHigh = 'VACUUM ANALYZE %s';
 103  
 104  /**
 105   * @see adodb_perf::optimizeTable()
 106   */
 107  
 108  	function optimizeTable($table, $mode = ADODB_OPT_LOW)
 109  	 {
 110  	     if(! is_string($table)) return false;
 111  
 112  	     $conn = $this->conn;
 113  	     if (! $conn) return false;
 114  
 115  	     $sql = '';
 116  	     switch($mode) {
 117  	         case ADODB_OPT_LOW : $sql = $this->optimizeTableLow;  break;
 118  	         case ADODB_OPT_HIGH: $sql = $this->optimizeTableHigh; break;
 119  	         default            :
 120  	             ADOConnection::outp(sprintf("<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__, 'optimizeTable', $mode));
 121  	             return false;
 122  	     }
 123  	     $sql = sprintf($sql, $table);
 124  
 125  	     return $conn->Execute($sql) !== false;
 126  	 }
 127  
 128  	function Explain($sql,$partial=false)
 129  	 {
 130  	 	 $save = $this->conn->LogSQL(false);
 131  
 132  	 	 if ($partial) {
 133  	 	 	 $sqlq = $this->conn->qstr($sql.'%');
 134  	 	 	 $arr = $this->conn->getArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
 135  	 	 	 if ($arr) {
 136  	 	 	 	 foreach($arr as $row) {
 137  	 	 	 	 	 $sql = reset($row);
 138  	 	 	 	 	 if (crc32($sql) == $partial) break;
 139  	 	 	 	 }
 140  	 	 	 }
 141  	 	 }
 142  	 	 $sql = str_replace('?',"''",$sql);
 143  	 	 $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
 144  	 	 $rs = $this->conn->Execute('EXPLAIN '.$sql);
 145  	 	 $this->conn->LogSQL($save);
 146  	 	 $s .= '<pre>';
 147  	 	 if ($rs)
 148  	 	 	 while (!$rs->EOF) {
 149  	 	 	 	 $s .= reset($rs->fields)."\n";
 150  	 	 	 	 $rs->MoveNext();
 151  	 	 	 }
 152  	 	 $s .= '</pre>';
 153  	 	 $s .= $this->Tracer($sql,$partial);
 154  	 	 return $s;
 155  	 }
 156  }