Search Postgresql Archives

Very slow catalog query

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

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[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