Thread: Initial queries of day slow
Hi all.
I'm a bit stumped. At present I'm finding that queries to my database, that normally execute promptly, are taking a long time when they are executed first thing in the morning (after the database has been inactive for several hours). After the first execution, everything is back to normal.
A while back I turned autovacuum off and now instead I run a daily cron at 3am that executes a script which does a VACUUM ANALYZE on each table.
These are my details:
Debian GNU/Linux 6.0
Postgresql 9.1
Memory 4GB
shared_buffers = 1024MB
work_mem = 16MB
maintenance_work_mem = 128MB
effective_cache_size = 2048MB
Would love peoples opinions on what the issue could be.
Thanks
On Mon, Apr 7, 2014 at 3:02 PM, Rebecca Clarke <r.clarke83@gmail.com> wrote:
Hi all.I'm a bit stumped. At present I'm finding that queries to my database, that normally execute promptly, are taking a long time when they are executed first thing in the morning (after the database has been inactive for several hours). After the first execution, everything is back to normal.A while back I turned autovacuum off and now instead I run a daily cron at 3am that executes a script which does a VACUUM ANALYZE on each table.These are my details:Debian GNU/Linux 6.0Postgresql 9.1Memory 4GBshared_buffers = 1024MBwork_mem = 16MBmaintenance_work_mem = 128MBeffective_cache_size = 2048MB
Could it be cold cache behaviour?
--
Regards,
Atri
l'apprenant
Rebecca Clarke wrote: > I'm a bit stumped. At present I'm finding that queries to my database, that normally execute promptly, > are taking a long time when they are executed first thing in the morning (after the database has been > inactive for several hours). After the first execution, everything is back to normal. > > A while back I turned autovacuum off and now instead I run a daily cron at 3am that executes a script > which does a VACUUM ANALYZE on each table. It could be that during the day the necessary pages are cached in the buffer pool or the file system cache, but have dropped out of the cache during the night. Try EXPLAIN (ANALYZE, BUFFERS) SELECT ... first thing in the morning and during the day and compare the "shared read" and "shared hit" values. It may well be the nightly VACUUM ANALYZE that does that - is autovacuum not doing ist job for you? Is there anything else going on on the machine during the night, like backups or batch jobs? Yours, Laurenz Albe
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.
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.
On Mon, Apr 7, 2014 at 10:50 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Rebecca Clarke wrote:It could be that during the day the necessary pages are cached in
> I'm a bit stumped. At present I'm finding that queries to my database, that normally execute promptly,
> are taking a long time when they are executed first thing in the morning (after the database has been
> inactive for several hours). After the first execution, everything is back to normal.
>
> A while back I turned autovacuum off and now instead I run a daily cron at 3am that executes a script
> which does a VACUUM ANALYZE on each table.
the buffer pool or the file system cache, but have dropped out of
the cache during the night.
Try EXPLAIN (ANALYZE, BUFFERS) SELECT ...
first thing in the morning and during the day and compare the
"shared read" and "shared hit" values.
It may well be the nightly VACUUM ANALYZE that does that - is autovacuum
not doing ist job for you?
Is there anything else going on on the machine during the night, like
backups or batch jobs?
Yours,
Laurenz Albe
On Mon, Apr 07, 2014 at 10:32:59AM +0100, Rebecca Clarke wrote: > normally execute promptly, are taking a long time when they are executed > first thing in the morning (after the database has been inactive for > several hours). After the first execution, everything is back to normal. Just guessing, but perhaps because your system's disk buffers have all been blown away, so things that are normally in memory aren't any more. In particular, > A while back I turned autovacuum off and now instead I run a daily cron at > 3am that executes a script which does a VACUUM ANALYZE on each table. this goes through every table in the database, and probably not in the order such that the most-frequently-used tables are last in the set. But also, why did you turn off autovacuum? In the earliest implementations of autovacuum that was sometimes worth doing for very specific workloads, but in more recent releases (9.1.x certainly qualifies) you are much better to tune autovacuum. Best regards, A -- Andrew Sullivan ajs@crankycanuck.ca
On Mon, Apr 7, 2014 at 6:14 AM, Andrew Sullivan <ajs@crankycanuck.ca> wrote: > On Mon, Apr 07, 2014 at 10:32:59AM +0100, Rebecca Clarke wrote: > >> normally execute promptly, are taking a long time when they are executed >> first thing in the morning (after the database has been inactive for >> several hours). After the first execution, everything is back to normal. > > Just guessing, but perhaps because your system's disk buffers have all > been blown away, so things that are normally in memory aren't any > more. In particular, > >> A while back I turned autovacuum off and now instead I run a daily cron at >> 3am that executes a script which does a VACUUM ANALYZE on each table. > > this goes through every table in the database, and probably not in the > order such that the most-frequently-used tables are last in the set. > But also, why did you turn off autovacuum? In the earliest > implementations of autovacuum that was sometimes worth doing for very > specific workloads, but in more recent releases (9.1.x certainly > qualifies) you are much better to tune autovacuum. yes. first think to check is iowait (say, via top). If it's high, then it could be vanilla cache warm up issue or interference from some other long running process. Another possible culprit is host machine resources getting harvested or re-allocated if you're in a virtualized environment. merlin
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
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
Rebecca Clarke wrote: > 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. You vacuum the catalog tables as well, right? Yours, Laurenz Albe