All dCache sites should have a look at this. It's easy to install and can 
generate a wealth of useful information about PostgreSQL transactions.

Not to be outdone, there's also this for MySQL:

http://hackmysql.com/mysqlsla

Happy New Year everyone!

Greig

---------- Forwarded message ----------
Date: Sun, 30 Dec 2007 14:05:01 +0100
From: Tigran Mkrtchyan <[log in to unmask]>
To: DCache user forum <[log in to unmask]>
Subject: Re: postgres tuning for dcache


Hi Stijn,


Please have a look at pgFouine:

http://pgfouine.projects.postgresql.org/

Regards,
 	Tigran.

Stijn De Weirdt wrote:
> hi all,
>
> i'm setting up a testbed to check the impact of postgres tuning tips on
> dcache. for this i'm using dcache 180 on Centos5.1 x86_64, postgres
> 8.2.5 (the hardware is a rather standard box atm, but will be improved
> in a few weeks).
>
> so i have a few questions/issues and i'd hope some of you can help out:
>
> a. if someone already did this, can i see your results ? ;)
> (i know of some general tuning parameters, but i don't know if this is
> very dcache specific or just general tips)
>
> b. is there anyway to determine the load on postgres by dcache?
>  - typical/maximum number of connections open that can be expected
>   - what parts of dcache actually need to connect?
>  - what type of queries
>   - frequency
>   - lenghth
>   - read or insert
>  - what dcache part dominates this
>   - pnfs
>   - srm
>   - ?
>  - is there a way in postgres to collect all queries our current
> production setup for eg one day, so i can replay it on the testbed?
>
> c. i would like to do some pure pnfs tests based on data of our testbed
> (i need a directory tree with a few 10k files and the companion info).
> (our current setup uses postgres 8.1.X)
> i tried dumping the companion DB and one DB of pnfs, and inserted them
> in the testbed (i created an empty structure first). but pnfs doesn't
> list the files.
>  - what is needed for such a minimal setup? (or do i need a full
> replication of existing strucure?)
>   - do i also need eg the admin DB?
>   - the pnfsdb-numbers (or whatever they are called) of the databases do
> not correspond: i mean /opt/pnfsdb/pnfs/info/D-0002 on testbed is
> /opt/pnfsdb/pnfs/info/D-0007 on production setup, D-0000 and D-0001 are
> the same (admin and data1, but the admin db is not a copy of the
> production setup ))
>
> d. i also noticed that the pnfs databases are owned by postgres
> user/role, not pnfsserver (i'm not using YAIM for install, so it could
> be my fault;)
>  - in /opt/pnfs/etc/pnfs_config.template PNFS_USER is not defined. is
> this the cause (ie if it's missing it default to the postgres user)?
>  - is this bad? i can dccp new files, but i guess from securtiy point of
> view its not the smartest thing to do.
>
>
> thanks a lot,
>
> stijn
>
>


-- 
________________________________________________________________________
Tigran Mkrtchyan                               DESY, IT,
[log in to unmask]                       Notkestrasse 85,
Tel: + 49 40 89983946                          Hamburg 22607,
Fax: + 49 40 89984429                          Germany.