[2] | 1 | <?php |
---|
| 2 | |
---|
| 3 | /* |
---|
[34] | 4 | V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. |
---|
[2] | 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', |
---|
[34] | 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'", |
---|
[2] | 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', |
---|
[34] | 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', |
---|
[2] | 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', |
---|
[34] | 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", |
---|
[2] | 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 | function Explain($sql,$partial=false) |
---|
| 96 | { |
---|
| 97 | $save = $this->conn->LogSQL(false); |
---|
| 98 | |
---|
| 99 | if ($partial) { |
---|
| 100 | $sqlq = $this->conn->qstr($sql.'%'); |
---|
| 101 | $arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq"); |
---|
| 102 | if ($arr) { |
---|
| 103 | foreach($arr as $row) { |
---|
| 104 | $sql = reset($row); |
---|
| 105 | if (crc32($sql) == $partial) break; |
---|
| 106 | } |
---|
| 107 | } |
---|
| 108 | } |
---|
| 109 | $sql = str_replace('?',"''",$sql); |
---|
| 110 | $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>'; |
---|
| 111 | $rs = $this->conn->Execute('EXPLAIN '.$sql); |
---|
| 112 | $this->conn->LogSQL($save); |
---|
| 113 | $s .= '<pre>'; |
---|
| 114 | if ($rs) |
---|
| 115 | while (!$rs->EOF) { |
---|
| 116 | $s .= reset($rs->fields)."\n"; |
---|
| 117 | $rs->MoveNext(); |
---|
| 118 | } |
---|
| 119 | $s .= '</pre>'; |
---|
| 120 | $s .= $this->Tracer($sql,$partial); |
---|
| 121 | return $s; |
---|
| 122 | } |
---|
| 123 | } |
---|
| 124 | ?> |
---|