On Thu, Mar 3, 2011 at 11:00 AM, Derrick Rice <derrick.rice@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
