TYPO3 CMS  TYPO3_6-2
•All Classes Namespaces Files Functions Variables Pages
perf-postgres.inc.php
Go to the documentation of this file.
1 <?php
2 
3 /*
4 V5.19 23-Apr-2014 (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved.
5  Released under both BSD license and Lesser GPL library license.
6  Whenever there is any discrepancy between the two licenses,
7  the BSD license will take precedence. See License.txt.
8  Set tabs to 4 for best viewing.
9 
10  Latest version is available at http://adodb.sourceforge.net
11 
12  Library for basic performance monitoring and tuning
13 
14 */
15 
16 // security - hide paths
17 if (!defined('ADODB_DIR')) die();
18 
19 /*
20  Notice that PostgreSQL has no sql query cache
21 */
22 class perf_postgres extends adodb_perf{
23 
24  var $tablesSQL =
25  "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
26  on b.relname = 'pg_toast_'||trim(a.relfilenode)
27  left join pg_class c on c.relname = 'pg_toast_'||trim(a.relfilenode)||'_index'
28  where a.relname in (select tablename from pg_tables where tablename not like 'pg_%')";
29 
30  var $createTableSQL = "CREATE TABLE adodb_logsql (
31  created timestamp NOT NULL,
32  sql0 varchar(250) NOT NULL,
33  sql1 text NOT NULL,
34  params text NOT NULL,
35  tracer text NOT NULL,
36  timer decimal(16,6) NOT NULL
37  )";
38 
39  var $settings = array(
40  'Ratios',
41  'statistics collector' => array('RATIO',
42  "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' ",
43  '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)'),
44  'data cache hit ratio' => array('RATIO',
45  "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'",
46  '=WarnCacheRatio'),
47  'IO',
48  'data reads' => array('IO',
49  'select sum(heap_blks_read+toast_blks_read) from pg_statio_user_tables',
50  ),
51  'data writes' => array('IO',
52  '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',
53  'Count of inserts/updates/deletes * coef'),
54 
55  'Data Cache',
56  'data cache buffers' => array('DATAC',
57  "select setting from pg_settings where name='shared_buffers'",
58  'Number of cache buffers. <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic>Tuning</a>'),
59  'cache blocksize' => array('DATAC',
60  'select 8192',
61  '(estimate)' ),
62  'data cache size' => array( 'DATAC',
63  "select setting::integer*8192 from pg_settings where name='shared_buffers'",
64  '' ),
65  'operating system cache size' => array( 'DATA',
66  "select setting::integer*8192 from pg_settings where name='effective_cache_size'",
67  '(effective cache size)' ),
68  'Memory Usage',
69  # Postgres 7.5 changelog: Rename server parameters SortMem and VacuumMem to work_mem and maintenance_work_mem;
70  'sort/work buffer size' => array('CACHE',
71  "select setting::integer*1024 from pg_settings where name='sort_mem' or name = 'work_mem' order by name",
72  'Size of sort buffer (per query)' ),
73  'Connections',
74  'current connections' => array('SESS',
75  'select count(*) from pg_stat_activity',
76  ''),
77  'max connections' => array('SESS',
78  "select setting from pg_settings where name='max_connections'",
79  ''),
80  'Parameters',
81  'rollback buffers' => array('COST',
82  "select setting from pg_settings where name='wal_buffers'",
83  'WAL buffers'),
84  'random page cost' => array('COST',
85  "select setting from pg_settings where name='random_page_cost'",
86  'Cost of doing a seek (default=4). See <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#less>random_page_cost</a>'),
87  false
88  );
89 
90  function perf_postgres(&$conn)
91  {
92  $this->conn = $conn;
93  }
94 
95  var $optimizeTableLow = 'VACUUM %s';
96  var $optimizeTableHigh = 'VACUUM ANALYZE %s';
97 
102  function optimizeTable($table, $mode = ADODB_OPT_LOW)
103  {
104  if(! is_string($table)) return false;
105 
106  $conn = $this->conn;
107  if (! $conn) return false;
108 
109  $sql = '';
110  switch($mode) {
111  case ADODB_OPT_LOW : $sql = $this->optimizeTableLow; break;
112  case ADODB_OPT_HIGH: $sql = $this->optimizeTableHigh; break;
113  default :
114  {
115  ADOConnection::outp(sprintf("<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__, 'optimizeTable', $mode));
116  return false;
117  }
118  }
119  $sql = sprintf($sql, $table);
120 
121  return $conn->Execute($sql) !== false;
122  }
123 
124  function Explain($sql,$partial=false)
125  {
126  $save = $this->conn->LogSQL(false);
127 
128  if ($partial) {
129  $sqlq = $this->conn->qstr($sql.'%');
130  $arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq");
131  if ($arr) {
132  foreach($arr as $row) {
133  $sql = reset($row);
134  if (crc32($sql) == $partial) break;
135  }
136  }
137  }
138  $sql = str_replace('?',"''",$sql);
139  $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
140  $rs = $this->conn->Execute('EXPLAIN '.$sql);
141  $this->conn->LogSQL($save);
142  $s .= '<pre>';
143  if ($rs)
144  while (!$rs->EOF) {
145  $s .= reset($rs->fields)."\n";
146  $rs->MoveNext();
147  }
148  $s .= '</pre>';
149  $s .= $this->Tracer($sql,$partial);
150  return $s;
151  }
152 }
adodb_perf\Tracer
Tracer($sql)
Definition: adodb-perf.inc.php:382
$sql
$sql
Definition: server.php:82
ADODB_OPT_LOW
const ADODB_OPT_LOW
Definition: adodb-perf.inc.php:23
adodb_perf\$conn
$conn
Definition: adodb-perf.inc.php:218
$rs
if(isset($_REQUEST['nrows'])) else $rs
Definition: server.php:92
perf_postgres\$tablesSQL
$tablesSQL
Definition: perf-postgres.inc.php:24
perf_postgres\perf_postgres
perf_postgres(&$conn)
Definition: perf-postgres.inc.php:90
ADODB_OPT_HIGH
const ADODB_OPT_HIGH
Definition: adodb-perf.inc.php:22
die
die
Definition: index.php:6
perf_postgres\optimizeTable
optimizeTable($table, $mode=ADODB_OPT_LOW)
Definition: perf-postgres.inc.php:102
perf_postgres\$optimizeTableLow
$optimizeTableLow
Definition: perf-postgres.inc.php:95
perf_postgres
Definition: perf-postgres.inc.php:22
adodb_perf\$table
$table
Definition: adodb-perf.inc.php:220
perf_postgres\$createTableSQL
$createTableSQL
Definition: perf-postgres.inc.php:30
perf_postgres\$settings
$settings
Definition: perf-postgres.inc.php:39
perf_postgres\$optimizeTableHigh
$optimizeTableHigh
Definition: perf-postgres.inc.php:96
adodb_perf
Definition: adodb-perf.inc.php:217
perf_postgres\Explain
Explain($sql, $partial=false)
Definition: perf-postgres.inc.php:124