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.9BE4.52131E4D@oss.ntt.co.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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828