Hi,
I have a DB with a large number schemas (around 10K) and a large
number of tables (400K). The app became slow lately, and logging the
slow queries, I see more than a few like this:
SELECT: LOG: duration: 169547.424 ms statement: SELECT
attr.attname, name.nspname, seq.relname
FROM pg_class seq,
pg_attribute attr,
pg_depend dep,
pg_namespace name,
pg_constraint cons
WHERE seq.oid = dep.objid
AND seq.relnamespace = name.oid
AND seq.relkind = 'S'
AND attr.attrelid = dep.refobjid
AND attr.attnum = dep.refobjsubid
AND attr.attrelid = cons.conrelid
AND attr.attnum = cons.conkey[1]
AND cons.contype = 'p'
AND dep.refobjid = 'activities'::regclass
Almost all slow queries are of this type, though most of those do
finish really fast. From time to time it gets really slow.
Some details on the setup:
Dual Opteron with 4GB RAM
RAID1 for WAL on 10K SCSI
RAID10 over 6 x 10K scsi drives for main the rest for the DB files
Auto vaccum is on, and in addition I do some vacuuming for specific
high use tables nightly
Any ideas how to start finding the culprit?
Bye,
Guy.
--
Family management on rails: http://www.famundo.com
My development related blog: http://devblog.famundo.com