Thread: Tracking table modifications / table stats

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).

Thanks,

Derrick

On Thu, Mar 3, 2011 at 11:00 AM, Derrick Rice <derrick.rice@gmail.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

On 3/3/2011 11:00 AM, Derrick Rice 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).
>
> Thanks,
>
> Derrick

There are stat tables you can look at:

http://www.postgresql.org/docs/9.0/static/monitoring-stats.html

-Andy

Aha! Thank you.