Re: very slow queries and ineffective vacuum

Lukasz Wrobel wrote:
> Hello.
>
> I have multiple problems with my database, the biggest of which is how to
> find out what is actually wrong.
>
> First of all I have a 9.3 postgres database that is running for about a
> month. Right now the queries on that database are running very slowly
> (select with a simple "where" on a non-indexed column on a table with about
> 5000 records takes 1,5s, a complicated hibernate select with 7 joins on
> tables of about 5000 records takes about 15s, insert or update on a table
> with 35000 records takes up to 20 mins).

What's your operating system?

What does pg_stat_user_tables tell you about the vacuum times for the
bloated tables?  Mainly, is autovacuum processing them at all?  If not,
are there log entries about autovacuum trouble (those would show up as
ERROR mentioning automatic vacuuming)?  If not, is autovacuum running at
all, and is the stats collector working properly?

I'd recommend setting log_autovacuum_min_duration to a value other than
the default -1 and see whether it is doing anything.

Also useful for debugging would be the VACUUM VERBOSE output for
problematic tables.

Maybe your tuple death rate is higher than what autovacuum can cope
with, with default settings.  In that case maybe you need a larger
autovacuum_max_workers setting and/or a decrease of
autovacuum_vacuum_cost_delay and/or a change of autovacuum_naptime.
Sometimes, manual vacuuming of individual problematic tables also helps.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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