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
