On Monday 31 March 2008 15:13:25 Just Someone wrote: > 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 Hi 'Just Someone' I'm wondering... just 4GB of ram? What's the "normal" "hammering" -- a.k.a. user access -- to all of this? PG, as expected, launches a separate process for each connection. this eats up resources quite quickly.... Did you check your system processes with 'top' ? how's it looking for swap usage? Regards, -- Pedro Doria Meunier Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y 9125-162 Caniço Madeira - Portugal -------------------------------------------------- Skype : pdoriam Mobile: +351961720188
Attachment:
signature.asc
Description: This is a digitally signed message part.
