« C'est pour ça que je préfère l'exemple des lemmings : il est plus parlant. »

Sample reports

The following sample reports are built from a log file generated by a pgbench script so they are not really representative of the output generated with a real life log.

Default report

The following command generates a default report:

$ pgfouine.php -file logs_pgbench_20051211063633.log > sample_default.html

Exclude write queries from our report

If we suppose UPDATE queries are not relevant to optimize our application, we can filter them:

$ pgfouine.php -file logs_pgbench_20051211063633.log -onlyselect > sample_only_select.html

Choose the generated reports

We can choose the generated reports with the -reports option:

$ pgfouine.php -file logs_pgbench_20051211063633.log -reports overall,bytype > sample_reports.html

Normalized query examples option

If we want more examples for normalized queries, we can define it with the -examples option:

$ pgfouine.php -file logs_pgbench_20051211063633.log -examples 10 > sample_examples.html

Error report

If we want to generate an error report, we can use the "n-mostfrequenterrors" report type:

$ pgfouine.php -file logs_pgbench_20051211063633.log -reports n-mostfrequenterrors > sample_errors.html

Hourly activity with graphs

It is also possible generate hourly statistics graphs which can help you to understand the activity of your database:

$ pgfouine.php -file logs_pgbench_20051211063633.log -report sample_hourly.html=overall,bytype,hourly \
-format html-with-graphs

History of the executed queries

$ pgfouine.php -file logs_history_sample.log -report history > sample_history.html

Multiple reports in one analysis

This is the command line I usually use to generate our reports:

$ pgfouine.php -file pgsql.log -top 40 \
-report queries.html=overall,bytype,slowest,n-mosttime,n-mostfrequent,n-slowestaverage \
-report hourly.html=overall,hourly \
-report errors.html=overall,n-mostfrequenterrors \
-format html-with-graphs

Reading the log from stdin

pgFouine can read from stdin using - instead of -file so we can use the following command line:

$ gunzip -c your_log_file.gz | pgfouine.php - -report overall,bytype > your_report.html

And what about Apache logs?

pgFouine can also analyze Apache logs by using the following CustomLog directive:

CustomLog /var/log/httpd/pgfouine.log "%{%b %e %H:%M:%S}t apache postgres[%P]: [%{%s}t%B-1] \
LOG: duration: %D us statement: %U 'vhost: %V' 'qs: %q'"

pgFouine - a PostgreSQL log analyzer