Search Postgresql Archives

Re: Very slow catalog query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux