Re: Very slow catalog query

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