Re: Tracking table modifications / table stats

On Thu, Mar 3, 2011 at 11:00 AM, Derrick Rice <derrick(dot)rice(at)gmail(dot)com> wrote:
> Hey folks,
>
> I was looking through the contrib modules with 8.4 and hoping to find
> something that satisfies my itch.
> http://www.postgresql.org/docs/8.4/static/pgstatstatements.html comes the
> closest.
>
> I'm inheriting a database which has mostly unknown usage patterns, and would
> like to figure them out so that I can allocate tablespaces and set
> autovacuum settings appropriately.  To do this, it seems I need to know (at
> least) the number of rows read, rows updated, rows deleted, and rows
> inserted for each table (over time, or until reset).
>
> I suppose things like disk usage and CPU usage would be interesting as well,
> but I'm somewhat less concerned with those.  For one, CPU usage can't be
> tied to a table as easily and is more about query optimization than
> PostgreSQL configuration (excluding cost coefficients and memory size
> settings).  For the other, disk usage can be mostly inferred from the row
> size and and number of operations per table (this does exclude seq. scans
> and heavy heavy index use, though).  I realize those statements are fuzzy
> and short-sighted, but I'm trying to get "good enough" information, not
> optimize a space shuttle.
>
> There's no way I'm the first person to feel the need for this.  Is there a
> doc or wiki which gives some recommendations?  I'd like to avoid parsing
> logs or installing triggers.  I'd also like to avoid heavy statement-level
> tracking like the above mentioned contrib does (sounds expensive, and I'm
> not sure the users have parameterized SQL).

The old tried and true method of slow query logging
(min_statement_duration) works wonders. Usually in a typical system
10% of the queries are doing 90% of the work.

If I'm coming into a new database created by someone else, priority #1
is to get logging under control: make sure it's being captured,
rotated properly, etc. If there are lots of garbage errors being
dropped in there, try fixing them so that the logs become useful.

merlin