Platform  3.1
PHP API documentation
 All Data Structures Namespaces Files Functions Variables Pages
pgsize.php
Go to the documentation of this file.
1 <html>
2 <head>
3 <?php
4 print sprintf("<title>%s</title>", isset($_GET['q'])?htmlspecialchars($_GET['q']):'pgsize');
5 ?>
6 <style type="text/css">
7 body {
8  font-family: monospace;
9 }
10 div.menu {
11  background-color: darkgray;
12 }
13 table > thead {
14  background-color: lightgray;
15 }
16 </style>
17 </head>
18 <body>
19 <?php
20 
21 define('PG_PAGE_SIZE', 8 * 1024);
22 
23 include("../WHAT/Lib.Common.php");
24 
25 $pgservice_core = getServiceCore();
26 
27 $conn = pg_connect("service='$pgservice_core'");
28 if( $conn === false ) {
29  print sprintf("Error: could not connect to pg service '%s'", $pgservice_core);
30  exit(1);
31 }
32 
33 $query = array();
34 
35 $query['pg_settings'] = sprintf("SELECT name, setting, context, short_desc FROM pg_settings");
36 
37 $query['pg_stat_activity'] = sprintf("SELECT datname, procpid, client_addr, client_port, waiting, query_start, now() - query_start AS time, current_query FROM pg_stat_activity WHERE procpid != pg_backend_pid()");
38 
39 $query['tables'] = sprintf("SELECT s.schemaname, s.relname, c.oid, c.relfilenode, s.seq_scan, s.idx_scan, c.reltuples, c.relpages as pages FROM pg_stat_all_tables as s, pg_class as c WHERE s.relname = c.relname AND s.schemaname IN (SELECT nspname FROM pg_namespace WHERE oid = c.relnamespace) AND ( s.schemaname = 'public' OR s.schemaname = 'pg_toast' )");
40 
41 $query['tables I/O'] = sprintf("SELECT schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_all_tables WHERE schemaname IN ('public', 'pg_toast')");
42 
43 $query['indexes'] = sprintf("SELECT s.schemaname, s.relname, s.indexrelname, c.oid, c.relfilenode, s.idx_scan, s.idx_tup_read, s.idx_tup_fetch, c.reltuples, c.relpages as pages FROM pg_stat_all_indexes as s, pg_class as c WHERE s.indexrelname = c.relname AND s.schemaname IN (SELECT nspname FROM pg_namespace WHERE oid = c.relnamespace) AND ( s.schemaname = 'public' OR s.schemaname = 'pg_toast' )");
44 
45 $query['indexes I/O'] = sprintf("SELECT schemaname, relname, indexrelname, idx_blks_read, idx_blks_hit FROM pg_statio_all_indexes WHERE schemaname IN ('public', 'pg_toast')");
46 
47 $query['vacuums'] = sprintf("SELECT s.schemaname, s.relname, c.reltuples, s.n_tup_ins, s.n_tup_upd, s.n_tup_del, s.n_tup_hot_upd, s.n_live_tup, s.n_dead_tup, s.last_vacuum, s.last_autovacuum FROM pg_stat_all_tables as s, pg_class as c WHERE s.relname = c.relname AND s.schemaname IN (SELECT nspname FROM pg_namespace WHERE oid = c.relnamespace) AND ( s.schemaname = 'public' OR s.schemaname = 'pg_toast' )");
48 
49 $query['pg_buffercache'] = sprintf("SELECT c.relname, count(*) AS pages FROM pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = c.relfilenode AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) GROUP BY c.relname");
50 
51 $parms = array();
52 $parms['q'] = $_GET['q'];
53 $parms['orderby'] = $_GET['orderby'];
54 $parms['desc'] = $_GET['desc'];
55 
56 if( ! isset($query[$parms['q']]) ) {
57  $parms['q'] = key($query);
58 }
59 if( $parms['desc'] != 'desc' && $parms['desc'] != 'asc' ) {
60  $parms['desc'] = 'desc';
61 }
62 
63 $menu = array();
64 foreach( $query as $k => $v ) {
65  $menu [] = '<a href="?q='.htmlspecialchars($k).'">'.htmlspecialchars($k).'</a>';
66 }
67 print '<div class="menu">&nbsp;'.join('&nbsp;|&nbsp;', $menu).'&nbsp;</div>';
68 
69 show_query($conn, $query[$parms['q']], $parms);
70 
71 function show_query(&$conn, &$query, &$parms) {
72  $q = $parms['q'];
73  $orderby = $parms['orderby'];
74  $desc = $parms['desc'];
75 
76  $add = '';
77  if( $orderby != '' ) {
78  $add = sprintf('ORDER BY "%s"', pg_escape_string($orderby));
79  if( $desc != '' ) {
80  $add = sprintf('%s %s', $add, pg_escape_string($desc));
81  }
82  }
83 
84  $res = pg_prepare($conn, 'select', sprintf("%s %s", $query, pg_escape_string($add)));
85  if( $res !== false ) {
86  $res = pg_execute($conn, 'select', array());
87  }
88  if( $res === false ) {
89  print '<div class="error">';
90  print htmlspecialchars(pg_last_error($conn));
91  print '</div>';
92  return false;
93  }
94 
95  $col_num = pg_num_fields($res);
96  $line_num = pg_num_rows($res);
97 
98  $fields = array();
99  for( $i = 0; $i < $col_num; $i++ ) {
100  $fields[$i] = pg_field_name($res, $i);
101  }
102  $desc = ($desc == 'desc') ? 'asc' : 'desc';
103 
104  print '<div class="table">';
105  print '<table>';
106  print '<thead>';
107  foreach( $fields as $i => $name ) {
108  print '<td><a href="?q='.htmlspecialchars($q).'&orderby='.htmlspecialchars($name).'&desc='.htmlspecialchars($desc).'">'.htmlspecialchars($name).'</a></td>';
109  }
110  print '</thead>';
111  print '<tbody>';
112  while( $row = pg_fetch_row($res) ) {
113  print '<tr>';
114  foreach( $fields as $i => $name ) {
115  $v = $row[$i];
116  if( $name == 'pages' ) {
117  $v = prettySize($v * PG_PAGE_SIZE);
118  }
119  print '<td>'.htmlspecialchars($v).'</td>';
120  }
121  print '</tr>';
122  }
123  print '</tbody>';
124  print '</table>';
125  print '</div>';
126 }
127 
128 function prettySize($bytes, $precision = 2) {
129  $units = array('B', 'KB', 'MB', 'GB', 'TB');
130 
131  $bytes = max($bytes, 0);
132  $pow = floor(($bytes ? log($bytes) : 0) / log(1024));
133  $pow = min($pow, count($units) - 1);
134 
135  $bytes = $bytes / pow(1024, $pow);
136 
137  return round($bytes, $precision) . ' ' . $units[$pow];
138 }
139 
140 ?>
141 </body>
142 </html>
← centre documentaire © anakeen - published under CC License - Dynacase