Postgres Pro
Downloads
Home   >   mailing lists

Re: Initial queries of day slow - Mailing list pgsql-general

From Rebecca Clarke
Subject Re: Initial queries of day slow
Date
Msg-id CAMChtdfeYHqc=q+TX0bqXn53kJPFyvGB0dDOO4rcNqmtTYaCZw@mail.gmail.com
Whole thread Raw
In response to Re: Initial queries of day slow  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: Initial queries of day slow  (Albe Laurenz <laurenz.albe@wien.gv.at>)
List pgsql-general
Tree view
Hi Jeff

Unfortunately it's not just the one particular query, there's no pattern that I can see besides the time they're being executed. 

We did go from Autovac only to nightly vac. I'm going to implement autovac again, we've been operating without for a few months now. Will run both nightly manual and autovac to see how things go. On a side not, we're not doing a vacuumdb, but individual vacuum analyze statements on each table. Not sure if that makes any difference.


On Mon, Apr 7, 2014 at 9:13 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Mon, Apr 7, 2014 at 3:58 AM, Rebecca Clarke <r.clarke83@gmail.com> wrote:
Thanks, I'll run the EXPLAIN (ANALYZE, BUFFERS) today and tomorrow morning. I just tried it now on a query that took 109035.116 ms this morning (Which returns one row). It has returned 675.496 ms. I will run on this same query at 5am tomorrow. Thank you.

If the problem is largely encapsulated by that one query, I'd just write a cron job to execute that query every morning 15 minutes before you open for business.
 

At present we run pg_dumps every three hours.

We orginally found autovacuum too intrusive so switched to manual. We've had no problems with performance at all, only this. We're going to turn autovacuum back on to see if it makes any impact to this particular issue.

Did you go from 'Autovacuum only' to 'nightly vacuum, no autovac' in one step?  Mostly likely adding the nightly vacuum while leaving autovac on would have solved the problem, while being less likely to cause other problems.  (This is a side note--having autovac off is unlikely to be causing the particular problem you are reporting here.)

Cheers,

Jeff

pgsql-general by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: Postgres as In-Memory Database?
Next
From: Michael Paquier
Date:
Subject: Re: How do you find the row count for all your tables in Postgres?