Re: contrib/pg_stat_statements

On Oct 11, 2008, at 4:05 AM, ITAGAKI Takahiro wrote:
> I'd like to submit pg_stat_statements contrib module, that counts up
> incoming statements in shared memory and summarizes the result as a
> view.
> It is just a statements-version of pg_stat_user_functions.

Awesome!

> I attach WIP version of the module. auto_explain.patch is also
> required
> because the module uses a new version DefineCustomVariable.
> http://archives.postgresql.org/message-id/
> 20081009165157(dot)9BE4(dot)52131E4D(at)oss(dot)ntt(dot)co(dot)jp
> The module allocates fixed share memory at the server start and store
> statements statistics in it. On out of memory, least recently used
> statements are discarded.

How hard would it be to dump this information to a table, or some
other more-permanent form of storage? Of course there would need to
be some means of cleaning that up over time, but if it's a simple
table you can DELETE from, we could put the burden on the users to do
that on occasion (I believe Oracle does something similar). It would
also be good to periodically save everything to the table so that
data wasn't completely lost on a crash.

I'm concerned because ISTM that in a high velocity environment you'd
over-run shared memory pretty quickly if you had a lot of different
queries you were running.

Of course, someone could always just setup a cron job to grab the
stats once a minute, so if this greatly complicates the patch I
wouldn't worry about it.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828