Plugins - pgsql_stats - Agile Web Development

Plugins - pgsql_stats

PgsqlStats

====

PgsqlStats is an interface to the PostgreSQL statistics collector, extracted from the ZingLists administration pages. This plug-in includes models to pull data out of the collector and helper methods to simplify displaying the statistics in a page. It also includes the beginnings of an analysis module to provide hints for improving performance.

Use the Google Group at http://groups.google.com/group/pgsql_stats for questions, feature suggestions or patches.

Server Configuration

==============

Before PgsqlStats can do anything useful, ensure that your PostgreSQL server is configured to return the level of statistics you wish to display.

If all you care about is the current query running in each server process, verify the following two parameters in your postgresql.conf:

stats_start_collector = on stats_command_string = on

(Strictly speaking, you don’t even need the first one if you’re running 8.2 or later.)

If you want to gather row- or block-level statistics on your tables, indexes and sequences, then you need to enable one or both of the following:

stats_row_level = on stats_block_level = on

Row-level stats include the number of times tables and indexes are scanned and the number of rows returned, and how many rows have been inserted, updated and deleted.

Block-level stats include the number of disk blocks read and the number of times a block read was avoided by a buffer hit, and allow PgsqlStats to compute cache hit rates.

If either parameter is turned on, that also gives PgsqlStats information on the number of transactions committed and rolled back, database-wide.

Finally, while it isn’t used by PgsqlStats, it’s worth noting that you can choose to allow the statistics to persist (or not) across a server restart with "stats_reset_on_server_start".

See Chapter 25, "Monitoring Database Activity," in the PostgreSQL manual for more information.

Usage

Using the plug-in is pretty easy. Everything starts by asking for PgsqlStats::Collector for some information:

        tables = PgsqlStats::Collector.user_tables

This returns an Array of Table instances representing all of the user tables in the current database. The current database is defined as the one at the other end of ActiveRecord::Base.connection. You can change this if you want, by assigning a new connection:

        PgsqlStats::Collector.connection = MyModel.connection

There are various methods in each class that let you move around. For example, to get the indexes on a single table:

        indexes = tables.first.indexes

Or to figure out how many server processes are servicing requests on the current database:

        processes = PgsqlStats::Collector.server_activity(
          ActiveRecord::Base.configurations[RAILS_ENV]["database"])

You can leave off the database specification and get all processes in the server, but unless you’re connected as a superuser, some of the information will be unavailable.

Displaying In a View

==============

A helper is included to aid display of the statistics in a view.

The most interesting helper method is percentage_bar, which uses CSS to render a horizontal bar representing a percentage. The first argument is a snippet of text to display, the second is the percentage to fill up the bar, and the third provides arguments for choosing styles for the text and bar.

The third argument can either be a simple Hash or a Hash of Range objects:

        percentage_bar("half", 50,
          :bar_class => "bar", :text_class => "bartext")
        percentage_bar("high", 90,
          (0..20) => { :bar_class => "barlow",
                       :text_class => "barlowtext" },
          (21..79) => { :bar_class => "barnormal",
                        :text_class => "barnormaltext" },
          (80..100) => { :bar_class => "barhigh",
                         :text_class => "barhightext" })

You can also use :bar_style and :text_style to embed styles right in the emitted HTML instead of using class names.

Table and Index Analysis

==================

There is the beginnings of an analysis module. Based on the collected statistics, you can ask for an analysis of one or more tables and indexes and be given hints about how to potentially improve database performance.

This code is very new. It doesn’t provide much analysis yet and what it does say may be worthless. Having said that, run it by calling the methods in PgsqlStats::Analysis, then use the helper method analysis_results to put the hints in your view.

Owner: Steve Madsen

Home: http://groups.google.com/group/pgsql_stats

Repository: http://svn.lightyearsoftware.com/svn/plugins/pgsql_stats

License: Rails' (MIT)

Rating: