From dc0a5aa571d6f4d1d77a2da956a54fc37cc87f2d Mon Sep 17 00:00:00 2001 From: chriskl Date: Fri, 2 Jan 2004 12:53:36 +0000 Subject: [PATCH] add stats collector integration. improve formatting of some text files --- HISTORY | 2 + INSTALL | 15 ++- TRANSLATORS | 72 ++++++++------ classes/database/Postgres.php | 75 +++++++++++++- info.php | 180 +++++++++++++++++++++++++++++++++- lang/english.php | 21 +++- lang/recoded/english.php | 20 +++- 7 files changed, 344 insertions(+), 41 deletions(-) diff --git a/HISTORY b/HISTORY index 61dcd555..b2ac9287 100644 --- a/HISTORY +++ b/HISTORY @@ -10,6 +10,8 @@ Features connections and using external style sheet. * SQL pop-up window now defaults to the current database * Display aggregates and operator classes +* Integration with the PostgreSQL statistics collector. See + table and index performance and usage information. Bugs * Object browser fixed for databases with no schemas diff --git a/INSTALL b/INSTALL index 21ba8485..cb43d96d 100644 --- a/INSTALL +++ b/INSTALL @@ -25,10 +25,20 @@ phpPgAdmin Installation Guide Also, you will need to ensure that your 'pg_dump' and 'pg_dumpall' utilities are executable by the PHP process. -5. Browse to the phpPgAdmin installation using a web browser. You might +5. Enable the statistics collector in PostgreSQL. phpPgAdmin will display + table and index performance and usage statistics if you have enabled the + PostgreSQL statistics collector. To enable the collector, uncomment the + following lines in your postgresql.conf and enable them: + + stats_start_collector = true + stats_command_string = true + stats_block_level = true + stats_row_level = true + +6. Browse to the phpPgAdmin installation using a web browser. You might need cookies enabled for phpPgAdmin to work. -6. IMPORTANT - SECURITY +7. IMPORTANT - SECURITY PostgreSQL by default does not require you to use a password to log in. We STRONGLY recomment that you enable md5 passwords for local connections @@ -44,3 +54,4 @@ phpPgAdmin Installation Guide Once you are certain you have properly secured your database server, you can then disable 'extra_login_security' so that you can log in as your database administrator using the administrator password. + diff --git a/TRANSLATORS b/TRANSLATORS index c813e1e5..86be6b35 100644 --- a/TRANSLATORS +++ b/TRANSLATORS @@ -3,57 +3,69 @@ Translator Info If you like phpPgAdmin, then why not translate it into your native language? -Translation is slightly complicated in phpPgAdmin compared to other PHP software, -since we support viewing database encodings that are different to your language -encoding. +Translation is slightly complicated in phpPgAdmin compared to other PHP +software, since we support viewing database encodings that are different to +your language encoding. + +Also, there are quite a large number of strings to be translated. Partial +translations are better than no translations at all, and a rough guide is that +the strings are in the order from most important to least important in the +language file. You can ask the developers list if you don't know what a +certain string means. To translate messages, you will need to install GNU Recode on your computer. GNU Recode: http://www.gnu.org/software/recode/recode.html -Your favourite OS should have a GNU Recode package available. (See Bottom For Details) +Your favourite OS should have a GNU Recode package available. (See bottom for +details.) -Once you have Recode installed, these are the steps to creating a new translation: +Once you have Recode installed, these are the steps to creating a new +translation: 1. Go to the lang/ subdirectory 2. Copy english.php to yourlanguage.php -3. Update the comment at the top of the file. Put yourself as the language maintainer. - Edit the 'applang' variable and put your language's name in it, in your language. - Edit the 'appcharset' variable and put in the name of the encoding for your language. +3. Update the comment at the top of the file. Put yourself as the language + maintainer. Edit the 'applang' variable and put your language's name in it, + in your language. + + Edit the 'appcharset' variable and put in the name of the encoding for your + language. -4. Go through as much of the rest of the file as you wish, replacing the English strings - with strings in your language. +4. Go through as much of the rest of the file as you wish, replacing the + English strings with strings in your native language. -5. Edit the Makefile in the lang/ directory and add an entry for your new language. Note - that the parameter for Recode is "yourcharset..HTML". +5. Edit the Makefile in the lang/ directory and add an entry for your new + language. Note that the parameter for Recode is "yourcharset..HTML". -6. Run the Makefile by typing 'make yourlanguage'. A recoded language file will appear - in the lang/recoded/ directory. If the recoding fails, then fix the error in your - language file and try again. +6. Run the Makefile by typing 'make yourlanguage'. A recoded language file + will appear in the lang/recoded/ directory. If the recoding fails, then + fix the error in your language file and try again. 7. The HTML encoded language file is what phpPgAdmin actually uses to display - localised strings. Have a look at the recoded file to see how the HTML encoding - works. By encoding your language like this, we can display your language's - characters as well as the characters of the language in your database. + localised strings. Have a look at the recoded file to see how the HTML + encoding works. By encoding your language like this, we can display your + language's characters as well as the characters of the language in your + database. -8. To add your language to phpPgAdmin's login screen, edit libraries/lib.inc.php and add - your language to the $appLangFiles array. You must include the HTML encoded version - of your language's name. You can get this from the recoded version of your translated - strings file. +8. To add your language to phpPgAdmin's login screen, edit the + libraries/lib.inc.php file and add your language to the $appLangFiles array. + You must include the HTML encoded version of your language's name. You can + get this from the recoded version of your translated strings file. 9. Send your contribution to us. We need the lib.inc.php entry as well as the - yourlanguage.php file in lang/. We don't need the recoded file as we can recode it - ourselves before committing it. - -10. Thank you for your contribution! You have just made phpPgAdmin accessible to - thousands more users! + yourlanguage.php file in lang/. We don't need the recoded file as we can + recode it ourselves before committing it. -11. There exists a tool named 'langcheck' in the lang/ directory. To run it, just type - 'php langcheck '. It will give you a report about which strings are missing - from your language file and which need to be deleted. +10. Thank you for your contribution! You have just made phpPgAdmin accessible + to thousands more users! +11. There exists a tool named 'langcheck' in the lang/ directory. To run it, + just type 'php langcheck '. It will give you a report about + which strings are missing from your language file and which need to be + deleted. Appendix A: Ways to Get Recode ------------------------------ diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php index 52adc5ef..68bb11c6 100755 --- a/classes/database/Postgres.php +++ b/classes/database/Postgres.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres.php,v 1.170 2003/12/30 03:09:29 chriskl Exp $ + * $Id: Postgres.php,v 1.171 2004/01/02 12:53:36 chriskl Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -3399,7 +3399,78 @@ class Postgres extends BaseDB { return $this->selectSet($sql); } - + + // Statistics collector functions + + /** + * Fetches statistics for a database + * @param $database The database to fetch stats for + * @return A recordset + */ + function &getStatsDatabase($database) { + $this->clean($database); + + $sql = "SELECT * FROM pg_stat_database WHERE datname='{$database}'"; + + return $this->selectSet($sql); + } + + /** + * Fetches tuple statistics for a table + * @param $table The table to fetch stats for + * @return A recordset + */ + function &getStatsTableTuples($table) { + $this->clean($table); + + $sql = "SELECT * FROM pg_stat_all_tables WHERE schemaname='{$this->_schema}' + AND relname='{$table}'"; + + return $this->selectSet($sql); + } + + /** + * Fetches I/0 statistics for a table + * @param $table The table to fetch stats for + * @return A recordset + */ + function &getStatsTableIO($table) { + $this->clean($table); + + $sql = "SELECT * FROM pg_statio_all_tables WHERE schemaname='{$this->_schema}' + AND relname='{$table}'"; + + return $this->selectSet($sql); + } + + /** + * Fetches tuple statistics for all indexes on a table + * @param $table The table to fetch index stats for + * @return A recordset + */ + function &getStatsIndexTuples($table) { + $this->clean($table); + + $sql = "SELECT * FROM pg_stat_all_indexes WHERE schemaname='{$this->_schema}' + AND relname='{$table}' ORDER BY indexrelname"; + + return $this->selectSet($sql); + } + + /** + * Fetches I/0 statistics for all indexes on a table + * @param $table The table to fetch index stats for + * @return A recordset + */ + function &getStatsIndexIO($table) { + $this->clean($table); + + $sql = "SELECT * FROM pg_statio_all_indexes WHERE schemaname='{$this->_schema}' + AND relname='{$table}' ORDER BY indexrelname"; + + return $this->selectSet($sql); + } + // Type conversion routines /** diff --git a/info.php b/info.php index 727758ab..2bcfc7e4 100644 --- a/info.php +++ b/info.php @@ -3,7 +3,7 @@ /** * List extra information on a table * - * $Id: info.php,v 1.4 2003/12/17 09:11:32 chriskl Exp $ + * $Id: info.php,v 1.5 2004/01/02 12:53:36 chriskl Exp $ */ // Include application functions @@ -27,10 +27,16 @@ $referrers = &$data->getReferrers($_REQUEST['table']); $parents = &$data->getTableParents($_REQUEST['table']); $children = &$data->getTableChildren($_REQUEST['table']); + $tablestatstups = &$data->getStatsTableTuples($_REQUEST['table']); + $tablestatsio = &$data->getStatsTableIO($_REQUEST['table']); + $indexstatstups = &$data->getStatsIndexTuples($_REQUEST['table']); + $indexstatsio = &$data->getStatsIndexIO($_REQUEST['table']); // Check that there is some info if (($referrers === -99 || ($referrers !== -99 && $referrers->recordCount() == 0)) - && $parents->recordCount() == 0 && $children->recordCount() == 0) { + && $parents->recordCount() == 0 && $children->recordCount() == 0 + && $tablestatstups->recordCount() == 0 && $tablestatsio->recordCount() == 0 + && $indexstatstups->recordCount() == 0 && $indexstatsio->recordCount() == 0) { $misc->printMsg($lang['strnoinfo']); } else { @@ -59,7 +65,7 @@ echo "", $misc->printVal($referrers->f['consrc']), ""; echo "{$lang['strproperties']}\n"; + "&table=", urlencode($referrers->f['relname']), "\">{$lang['strproperties']}\n"; echo "\t\n"; $referrers->movenext(); $i++; @@ -90,7 +96,7 @@ echo "", $misc->printVal($parents->f['relname']), ""; echo "{$lang['strproperties']}\n"; + "&table=", urlencode($parents->f['relname']), "\">{$lang['strproperties']}\n"; echo "\t\n"; $parents->movenext(); $i++; @@ -121,7 +127,7 @@ echo "", $misc->printVal($children->f['relname']), ""; echo "{$lang['strproperties']}\n"; + "&table=", urlencode($children->f['relname']), "\">{$lang['strproperties']}\n"; echo "\t\n"; $children->movenext(); $i++; @@ -129,6 +135,170 @@ echo "\n"; } + + // Row performance + if ($tablestatstups->recordCount() > 0) { + echo "

{$lang['strrowperf']}

\n"; + + echo "\n"; + echo "\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + echo "\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + $i = 0; + + while (!$tablestatstups->EOF) { + $id = ( ($i % 2 ) == 0 ? '1' : '2' ); + echo "\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + $tablestatstups->movenext(); + $i++; + } + + echo "
{$lang['strsequential']}{$lang['strindex']}{$lang['strrows2']}
{$lang['strscan']}{$lang['strread']}{$lang['strscan']}{$lang['strfetch']}{$lang['strinsert']}{$lang['strupdate']}{$lang['strdelete']}
", $misc->printVal($tablestatstups->f['seq_scan'], true, 'int4'), "", $misc->printVal($tablestatstups->f['seq_tup_read'], true, 'int4'), "", $misc->printVal($tablestatstups->f['idx_scan'], true, 'int4'), "", $misc->printVal($tablestatstups->f['idx_tup_fetch'], true, 'int4'), "", $misc->printVal($tablestatstups->f['n_tup_ins'], true, 'int4'), "", $misc->printVal($tablestatstups->f['n_tup_upd'], true, 'int4'), "", $misc->printVal($tablestatstups->f['n_tup_del'], true, 'int4'), "
\n"; + } + + // I/O performance + if ($tablestatsio->recordCount() > 0) { + echo "

{$lang['strioperf']}

\n"; + + echo "\n"; + echo "\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + echo "\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + $i = 0; + + while (!$tablestatsio->EOF) { + $id = ( ($i % 2 ) == 0 ? '1' : '2' ); + echo "\t\n"; + + $total = $tablestatsio->f['heap_blks_hit'] + $tablestatsio->f['heap_blks_read']; + if ($total > 0) $percentage = round(($tablestatsio->f['heap_blks_hit'] / $total) * 100); + else $percentage = 0; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + + $total = $tablestatsio->f['idx_blks_hit'] + $tablestatsio->f['idx_blks_read']; + if ($total > 0) $percentage = round(($tablestatsio->f['idx_blks_hit'] / $total) * 100); + else $percentage = 0; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + + $total = $tablestatsio->f['toast_blks_hit'] + $tablestatsio->f['toast_blks_read']; + if ($total > 0) $percentage = round(($tablestatsio->f['toast_blks_hit'] / $total) * 100); + else $percentage = 0; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + + $total = $tablestatsio->f['tidx_blks_hit'] + $tablestatsio->f['tidx_blks_read']; + if ($total > 0) $percentage = round(($tablestatsio->f['tidx_blks_hit'] / $total) * 100); + else $percentage = 0; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + $tablestatsio->movenext(); + $i++; + } + + echo "
{$lang['strheap']}{$lang['strindex']}{$lang['strtoast']}{$lang['strtoastindex']}
{$lang['strdisk']}{$lang['strcache']}{$lang['strpercent']}{$lang['strdisk']}{$lang['strcache']}{$lang['strpercent']}{$lang['strdisk']}{$lang['strcache']}{$lang['strpercent']}{$lang['strdisk']}{$lang['strcache']}{$lang['strpercent']}
", $misc->printVal($tablestatsio->f['heap_blks_read'], true, 'int4'), "", $misc->printVal($tablestatsio->f['heap_blks_hit'], true, 'int4'), "({$percentage}{$lang['strpercent']})", $misc->printVal($tablestatsio->f['idx_blks_read'], true, 'int4'), "", $misc->printVal($tablestatsio->f['idx_blks_hit'], true, 'int4'), "({$percentage}{$lang['strpercent']})", $misc->printVal($tablestatsio->f['toast_blks_read'], true, 'int4'), "", $misc->printVal($tablestatsio->f['toast_blks_hit'], true, 'int4'), "({$percentage}{$lang['strpercent']})", $misc->printVal($tablestatsio->f['tidx_blks_read'], true, 'int4'), "", $misc->printVal($tablestatsio->f['tidx_blks_hit'], true, 'int4'), "({$percentage}{$lang['strpercent']})
\n"; + } + + // Index row performance + if ($indexstatstups->recordCount() > 0) { + echo "

{$lang['stridxrowperf']}

\n"; + + echo "\n"; + echo "\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + $i = 0; + + while (!$indexstatstups->EOF) { + $id = ( ($i % 2 ) == 0 ? '1' : '2' ); + echo "\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + $indexstatstups->movenext(); + $i++; + } + + echo "
{$lang['strindex']}{$lang['strscan']}{$lang['strread']}{$lang['strfetch']}
", $misc->printVal($indexstatstups->f['indexrelname']), "", $misc->printVal($indexstatstups->f['idx_scan'], true, 'int4'), "", $misc->printVal($indexstatstups->f['idx_tup_read'], true, 'int4'), "", $misc->printVal($indexstatstups->f['idx_tup_fetch'], true, 'int4'), "
\n"; + } + + // Index I/0 performance + if ($indexstatsio->recordCount() > 0) { + echo "

{$lang['stridxioperf']}

\n"; + + echo "\n"; + echo "\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + $i = 0; + + while (!$indexstatsio->EOF) { + $id = ( ($i % 2 ) == 0 ? '1' : '2' ); + echo "\t\n"; + $total = $indexstatsio->f['idx_blks_hit'] + $indexstatsio->f['idx_blks_read']; + if ($total > 0) $percentage = round(($indexstatsio->f['idx_blks_hit'] / $total) * 100); + else $percentage = 0; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\t\n"; + echo "\t\n"; + $indexstatsio->movenext(); + $i++; + } + + echo "
{$lang['strindex']}{$lang['strdisk']}{$lang['strcache']}{$lang['strpercent']}
", $misc->printVal($indexstatsio->f['indexrelname']), "", $misc->printVal($indexstatsio->f['idx_blks_read'], true, 'int4'), "", $misc->printVal($indexstatsio->f['idx_blks_hit'], true, 'int4'), "({$percentage}{$lang['strpercent']})
\n"; + } } } diff --git a/lang/english.php b/lang/english.php index e0acf12e..17b951f6 100755 --- a/lang/english.php +++ b/lang/english.php @@ -4,7 +4,7 @@ * English language file for phpPgAdmin. Use this as a basis * for new translations. * - * $Id: english.php,v 1.126 2003/12/31 15:44:27 soranzo Exp $ + * $Id: english.php,v 1.127 2004/01/02 12:53:36 chriskl Exp $ */ // Language and character set @@ -314,6 +314,7 @@ $lang['strsequenceresetbad'] = 'Sequence reset failed.'; // Indexes + $lang['strindex'] = 'Index'; $lang['strindexes'] = 'Indexes'; $lang['strindexname'] = 'Index Name'; $lang['strshowallindexes'] = 'Show all indexes'; @@ -553,9 +554,27 @@ $lang['strnoopclasses'] = 'No operator classes found.'; $lang['straccessmethod'] = 'Access method'; + // Stats & Performance + $lang['strrowperf'] = 'Row Performance'; + $lang['strioperf'] = 'I/O Performance'; + $lang['stridxrowperf'] = 'Index Row Performance'; + $lang['stridxioperf'] = 'Index I/O Performance'; + $lang['strpercent'] = '%'; + $lang['strsequential'] = 'Sequential'; + $lang['strscan'] = 'Scan'; + $lang['strread'] = 'Read'; + $lang['strfetch'] = 'Fetch'; + $lang['strheap'] = 'Heap'; + $lang['strtoast'] = 'TOAST'; + $lang['strtoastindex'] = 'TOAST Index'; + $lang['strcache'] = 'Cache'; + $lang['strdisk'] = 'Disk'; + $lang['strrows2'] = 'Rows'; + // Miscellaneous $lang['strtopbar'] = '%s running on %s:%s -- You are logged in as user "%s", %s'; $lang['strtimefmt'] = 'jS M, Y g:iA'; $lang['strhelp'] = 'Help'; + ?> diff --git a/lang/recoded/english.php b/lang/recoded/english.php index fd28af25..47898493 100644 --- a/lang/recoded/english.php +++ b/lang/recoded/english.php @@ -4,7 +4,7 @@ * English language file for phpPgAdmin. Use this as a basis * for new translations. * - * $Id: english.php,v 1.78 2003/12/31 15:44:27 soranzo Exp $ + * $Id: english.php,v 1.79 2004/01/02 12:53:36 chriskl Exp $ */ // Language and character set @@ -313,6 +313,7 @@ $lang['strsequenceresetbad'] = 'Sequence reset failed.'; // Indexes + $lang['strindex'] = 'Index'; $lang['strindexes'] = 'Indexes'; $lang['strindexname'] = 'Index Name'; $lang['strshowallindexes'] = 'Show all indexes'; @@ -552,6 +553,23 @@ $lang['strnoopclasses'] = 'No operator classes found.'; $lang['straccessmethod'] = 'Access method'; + // Stats & Performance + $lang['strrowperf'] = 'Row Performance'; + $lang['strioperf'] = 'I/O Performance'; + $lang['stridxrowperf'] = 'Index Row Performance'; + $lang['stridxioperf'] = 'Index I/O Performance'; + $lang['strpercent'] = '%'; + $lang['strsequential'] = 'Sequential'; + $lang['strscan'] = 'Scan'; + $lang['strread'] = 'Read'; + $lang['strfetch'] = 'Fetch'; + $lang['strheap'] = 'Heap'; + $lang['strtoast'] = 'TOAST'; + $lang['strtoastindex'] = 'TOAST Index'; + $lang['strcache'] = 'Cache'; + $lang['strdisk'] = 'Disk'; + $lang['strrows2'] = 'Rows'; + // Miscellaneous $lang['strtopbar'] = '%s running on %s:%s -- You are logged in as user "%s", %s'; $lang['strtimefmt'] = 'jS M, Y g:iA'; -- 2.39.5