MIME-Version: 1.0
From: =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0KjQsNC70LDRiNC+0LI=?= <skaurus@gmail.com>
Date: Fri, 25 Apr 2014 11:47:51 +0400
Message-ID: 
 <CAKPeCUE60iAdwDCJv82DTNQYpJgrM_O2ehTPBAdbx6kqFWrunA@mail.gmail.com>
Subject: Server vacuuming the same table again and again
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary=047d7bdc18a00446a604f7d9304a
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--047d7bdc18a00446a604f7d9304a
Content-Type: text/plain; charset=UTF-8

Hi!

Half a day ago one of our production PG servers (arguably busiest one)
become very slow; I went to investigate the issue and found that it runs
simultaneously '(auto)VACUUM ANALYZE recommendations' - largest table on
that server - and checkpoint, giving a 100% disk load, that resulted with
queue of queries which only made things worse of course.
For a while I tried to set different ionice settings to wal writer and
checkpointer processes (-c 2 -n [5-7]) for no visible effect. Then I
cancelled autovacuum and it seems to help.

When things settled up and day was reaching end I started VACUUM ANALYZE of
this table by hand and continued observations.
Vacuum ended in about 2 hours and half. But soon I noticed that server
started another autovacuum of the same table...
Problems returned and resolved after it finished (not 100% sure it was the
reason though).

In the morning autovacuum was back. And then it finished and I gone to
work. And now I'm here and there is autovacuum again %)
And load too. But I had to say, sometimes there is autovacuum and no load.
I'm not really sure autovacuum is the culprit, but there is correlation and
it behaves strange anyway.
In the app code nothing changed I believe.

Any recommendations where to dig further?

PG version: 9.2.8

Server hardware: E5-2690 x 2, 96GB RAM, 146GB 15k SAS x 8, HP P420i 2G RAID
controller, raid 1 for system and raid 50 for DB.

Perfomance settings changed:
shared_buffers = 24GB
temp_buffers = 128MB
work_mem = 16MB
maintenance_work_mem = 1GB
effective_cache_size = 48GB
effective_io_concurrency = 6 (I just realised I have to set it to 4, right?)
synchronous_commit = off
checkpoint_segments = 64
checkpoint_timeout = 10min
checkpoint_completion_target = 0.8
checkpoint_warning = 3600s

Plus I set vm.dirty_background_bytes to 134217728 and vm.dirty_bytes to
1073741824.

Also I believe now that raid 1 for system might be a mistake. Maybe give it
for WAL?

Best regards,
Dmitriy Shalashov

--047d7bdc18a00446a604f7d9304a
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi!<div><br></div><div>Half a day ago one of our productio=
n PG servers (arguably busiest one) become very slow; I went to investigate=
 the issue and found that it runs simultaneously &#39;(auto)VACUUM ANALYZE =
recommendations&#39; - largest table on that server - and checkpoint, givin=
g a 100% disk load, that resulted with queue of queries which only made thi=
ngs worse of course.</div>

<div>For a while I tried to set different ionice settings to wal writer and=
 checkpointer processes (-c 2 -n [5-7]) for no visible effect. Then I cance=
lled autovacuum and it seems to help.</div><div><br></div><div>When things =
settled up and day was reaching end I started VACUUM ANALYZE of this table =
by hand and continued observations.</div>

<div>Vacuum ended in about 2 hours and half. But soon I noticed that server=
 started another autovacuum of the same table...</div><div>Problems returne=
d and resolved after it finished (not 100% sure it was the reason though).<=
/div>

<div><br></div><div>In the morning autovacuum was back. And then it finishe=
d and I gone to work. And now I&#39;m here and there is autovacuum again %)=
</div><div>And load too. But I had to say, sometimes there is autovacuum an=
d no load. I&#39;m not really sure autovacuum is the culprit, but there is =
correlation and it behaves strange anyway.</div>

<div>In the app code nothing changed I believe.</div><div><br></div><div>An=
y recommendations where to dig further?</div><div><br></div><div>PG version=
: 9.2.8</div><div><br></div><div>Server hardware: E5-2690 x 2, 96GB RAM, 14=
6GB 15k SAS x 8, HP P420i 2G RAID controller, raid 1 for system and raid 50=
 for DB.</div>

<div><br></div><div>Perfomance settings changed:</div><div>shared_buffers =
=3D 24GB</div><div>temp_buffers =3D 128MB</div><div>work_mem =3D 16MB</div>=
<div>maintenance_work_mem =3D 1GB<br></div><div>effective_cache_size =3D 48=
GB<br>
</div>
<div>effective_io_concurrency =3D 6 (I just realised I have to set it to 4,=
 right?)<br></div><div>synchronous_commit =3D off</div><div><div>checkpoint=
_segments =3D 64</div><div>checkpoint_timeout =3D 10min</div><div>checkpoin=
t_completion_target =3D 0.8</div>

<div>checkpoint_warning =3D 3600s</div></div><div><br></div><div>Plus I set=
 vm.dirty_background_bytes to 134217728 and vm.dirty_bytes to 1073741824.</=
div><div><br></div><div>Also I believe now that raid 1 for system might be =
a mistake. Maybe give it for WAL?</div>

<div><div><div dir=3D"ltr"><div><br></div>Best regards,<br>Dmitriy Shalasho=
v<br></div></div>
</div></div>

--047d7bdc18a00446a604f7d9304a--