How about your start by giving us a little useful information? Show us your_longest_query and the output from EXPLAIN your_longest_query;
Although you say you have indexes, they may not be the correct indexes that you really need.
Also, how many physical disks do you have?
Do you have multiple tablespaces, if so, are your tables and indexes assigned separate tablespaces?
Although you say you have indexes, they may not be the correct indexes that you really need.
Also, how many physical disks do you have?
Do you have multiple tablespaces, if so, are your tables and indexes assigned separate tablespaces?
On Tue, Jun 30, 2015 at 6:16 PM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx> wrote:
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
