[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] very slow queries and ineffective vacuum
From: Melvin Davidson <melvin6925 () gmail ! com>
Date: 2015-06-30 23:14:46
Message-ID: CANu8FiwrW7iBJM8gG2vA7oVukBKNc1R7HihpvQ9Dg+Ji-aXxMw () mail ! gmail ! com
[Download RAW message or body]
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?
On Tue, Jun 30, 2015 at 6:16 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
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@postgresql.org)
> 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.
[Attachment #3 (text/html)]
<div dir="ltr">How about your start by giving us a little useful information? Show us \
your_longest_query and the output from EXPLAIN your_longest_query;<br>Although you \
say you have indexes, they may not be the correct indexes that you really \
need.<br>Also, how many physical disks do you have?<br>Do you have multiple \
tablespaces, if so, are your tables and indexes assigned separate \
tablespaces?<br></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, \
Jun 30, 2015 at 6:16 PM, Alvaro Herrera <span dir="ltr"><<a \
href="mailto:alvherre@2ndquadrant.com" \
target="_blank">alvherre@2ndquadrant.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><span class="">Lukasz Wrobel wrote:<br> > Hello.<br>
><br>
> I have multiple problems with my database, the biggest of which is how to<br>
> find out what is actually wrong.<br>
><br>
> First of all I have a 9.3 postgres database that is running for about a<br>
> month. Right now the queries on that database are running very slowly<br>
> (select with a simple "where" on a non-indexed column on a table with \
about<br> > 5000 records takes 1,5s, a complicated hibernate select with 7 joins \
on<br> > tables of about 5000 records takes about 15s, insert or update on a \
table<br> > with 35000 records takes up to 20 mins).<br>
<br>
</span>What's your operating system?<br>
<br>
What does pg_stat_user_tables tell you about the vacuum times for the<br>
bloated tables? Mainly, is autovacuum processing them at all? If not,<br>
are there log entries about autovacuum trouble (those would show up as<br>
ERROR mentioning automatic vacuuming)? If not, is autovacuum running at<br>
all, and is the stats collector working properly?<br>
<br>
I'd recommend setting log_autovacuum_min_duration to a value other than<br>
the default -1 and see whether it is doing anything.<br>
<br>
Also useful for debugging would be the VACUUM VERBOSE output for<br>
problematic tables.<br>
<br>
Maybe your tuple death rate is higher than what autovacuum can cope<br>
with, with default settings. In that case maybe you need a larger<br>
autovacuum_max_workers setting and/or a decrease of<br>
autovacuum_vacuum_cost_delay and/or a change of autovacuum_naptime.<br>
Sometimes, manual vacuuming of individual problematic tables also helps.<br>
<span class="HOEnZb"><font color="#888888"><br>
--<br>
Álvaro Herrera <a href="http://www.2ndQuadrant.com/" \
rel="noreferrer" target="_blank">http://www.2ndQuadrant.com/</a><br> PostgreSQL \
Development, 24x7 Support, Remote DBA, Training & Services<br> </font></span><div \
class="HOEnZb"><div class="h5"><br> <br>
--<br>
Sent via pgsql-general mailing list (<a \
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>)<br> To \
make changes to your subscription:<br> <a \
href="http://www.postgresql.org/mailpref/pgsql-general" rel="noreferrer" \
target="_blank">http://www.postgresql.org/mailpref/pgsql-general</a><br> \
</div></div></blockquote></div><br><br clear="all"><br>-- <br><div \
class="gmail_signature"><div dir="ltr"><font size="4"><b><span \
style="font-family:courier new,monospace">Melvin Davidson</span></b></font><br><font \
style="font-weight:bold" size="3"><span style="color:rgb(128,0,255)">I reserve the \
right to fantasize. Whether or not you </span><br style="color:rgb(128,0,255)"> \
<span style="color:rgb(128,0,255)">wish to share my fantasy is entirely up to you. \
</span><img style="color:rgb(128,0,255)" \
src="http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif"></font><br></div></div>
</div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic