pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum fringe case?
7+ messages / 5 participants
[nested] [flat]
* autovacuum fringe case?
@ 2013-01-23 16:53 AJ Weber <aweber@comcast.net>
2013-01-23 19:03 ` Re: autovacuum fringe case? Evgeniy Shishkin <itparanoia@gmail.com>
2013-01-23 19:13 ` Re: autovacuum fringe case? Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: AJ Weber @ 2013-01-23 16:53 UTC (permalink / raw)
To: pgsql-performance
I have a server that is IO-bound right now (it's 4 cores, and top
indicates the use rarely hits 25%, but the Wait spikes above 25-40%
regularly). The server is running postgresql 9.0 and tomcat 6. As I
have mentioned in a previous thread, I can't alter the hardware to add
disks unfortunately, so I'm going to try and move postgresql off this
application server to its own host, but this is a production
environment, so in the meantime...
Is it possible that some spikes in IO could be attributable to the
autovacuum process? Is there a way to check this theory?
Would it be advisable (or even permissible to try/test) to disable
autovacuum, and schedule a manual vacuumdb in the middle of the night,
when this server is mostly-idle?
Thanks for any tips. I'm in a bit of a jam with my limited hardware.
-AJ
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum fringe case?
2013-01-23 16:53 autovacuum fringe case? AJ Weber <aweber@comcast.net>
@ 2013-01-23 19:03 ` Evgeniy Shishkin <itparanoia@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Evgeniy Shishkin @ 2013-01-23 19:03 UTC (permalink / raw)
To: AJ Weber <aweber@comcast.net>; +Cc: pgsql-performance
On 23.01.2013, at 20:53, AJ Weber <aweber@comcast.net> wrote:
> I have a server that is IO-bound right now (it's 4 cores, and top indicates the use rarely hits 25%, but the Wait spikes above 25-40% regularly). The server is running postgresql 9.0 and tomcat 6. As I have mentioned in a previous thread, I can't alter the hardware to add disks unfortunately, so I'm going to try and move postgresql off this application server to its own host, but this is a production environment, so in the meantime...
>
> Is it possible that some spikes in IO could be attributable to the autovacuum process? Is there a way to check this theory?
>
Try iotop
> Would it be advisable (or even permissible to try/test) to disable autovacuum, and schedule a manual vacuumdb in the middle of the night, when this server is mostly-idle?
>
> Thanks for any tips. I'm in a bit of a jam with my limited hardware.
>
> -AJ
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum fringe case?
2013-01-23 16:53 autovacuum fringe case? AJ Weber <aweber@comcast.net>
@ 2013-01-23 19:13 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-23 22:48 ` Re: autovacuum fringe case? AJ Weber <aweber@comcast.net>
1 sibling, 1 reply; 7+ messages in thread
From: Jeff Janes @ 2013-01-23 19:13 UTC (permalink / raw)
To: AJ Weber <aweber@comcast.net>; +Cc: pgsql-performance
On Wed, Jan 23, 2013 at 8:53 AM, AJ Weber <aweber@comcast.net> wrote:
> I have a server that is IO-bound right now (it's 4 cores, and top indicates
> the use rarely hits 25%, but the Wait spikes above 25-40% regularly).
How long do the spikes last?
> The
> server is running postgresql 9.0 and tomcat 6. As I have mentioned in a
> previous thread, I can't alter the hardware to add disks unfortunately, so
> I'm going to try and move postgresql off this application server to its own
> host, but this is a production environment, so in the meantime...
>
> Is it possible that some spikes in IO could be attributable to the
> autovacuum process? Is there a way to check this theory?
set log_autovacuum_min_duration to 0 or some positive number, and see
if the vacuums correlate with periods of io stress (from sar or
vmstat, for example--the problem is that sar only takes snapshots
every 10 minutes, which is too coarse if the spikes are short).
> Would it be advisable (or even permissible to try/test) to disable
> autovacuum, and schedule a manual vacuumdb in the middle of the night, when
> this server is mostly-idle?
Scheduling a manual vacuum should be fine (but keep in mind that
vacuum has very different default cost_delay settings than autovacuum
does. If the server is completely idle that shouldn't matter, but if
it is only mostly idle, you might want to throttle the IO a bit). But
I certainly would not disable autovacuum without further evidence. If
a table only needs to be vacuumed once a day and you preemptively do
it at 3a.m., then autovac won't bother to do it itself during the day.
So there is no point, but much risk, in also turning autovac off.
Cheers,
Jeff
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum fringe case?
2013-01-23 16:53 autovacuum fringe case? AJ Weber <aweber@comcast.net>
2013-01-23 19:13 ` Re: autovacuum fringe case? Jeff Janes <jeff.janes@gmail.com>
@ 2013-01-23 22:48 ` AJ Weber <aweber@comcast.net>
2013-01-24 02:03 ` Re: autovacuum fringe case? Alvaro Herrera <alvherre@2ndquadrant.com>
2013-01-24 03:06 ` Re: autovacuum fringe case? Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: AJ Weber @ 2013-01-23 22:48 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 1/23/2013 2:13 PM, Jeff Janes wrote:
> On Wed, Jan 23, 2013 at 8:53 AM, AJ Weber<aweber@comcast.net> wrote:
>> I have a server that is IO-bound right now (it's 4 cores, and top indicates
>> the use rarely hits 25%, but the Wait spikes above 25-40% regularly).
> How long do the spikes last?
From what I can gather, a few seconds to a few minutes.
>
>> The
>> server is running postgresql 9.0 and tomcat 6. As I have mentioned in a
>> previous thread, I can't alter the hardware to add disks unfortunately, so
>> I'm going to try and move postgresql off this application server to its own
>> host, but this is a production environment, so in the meantime...
>>
>> Is it possible that some spikes in IO could be attributable to the
>> autovacuum process? Is there a way to check this theory?
> set log_autovacuum_min_duration to 0 or some positive number, and see
> if the vacuums correlate with periods of io stress (from sar or
> vmstat, for example--the problem is that sar only takes snapshots
> every 10 minutes, which is too coarse if the spikes are short).
I used iotop last time it was going crazy, and there were 5 postgres
procs at the top of the list (and virtually nothing else) all doing a
SELECT. So I'm also going to restart the DB this weekend with
log-min-duration enabled. Could also be some misbehaving queries...
Is there a skinny set of instructions on loading pg_stat_statements? Or
should I just log them and review them from there?
>
>> Would it be advisable (or even permissible to try/test) to disable
>> autovacuum, and schedule a manual vacuumdb in the middle of the night, when
>> this server is mostly-idle?
> Scheduling a manual vacuum should be fine (but keep in mind that
> vacuum has very different default cost_delay settings than autovacuum
> does. If the server is completely idle that shouldn't matter, but if
> it is only mostly idle, you might want to throttle the IO a bit). But
> I certainly would not disable autovacuum without further evidence. If
> a table only needs to be vacuumed once a day and you preemptively do
> it at 3a.m., then autovac won't bother to do it itself during the day.
> So there is no point, but much risk, in also turning autovac off.
If I set autovacuum_max_workers = 1, will that effectively single-thread
it so I don't have two running at once? Maybe that'll mitigate disk
contention a little at least?
>
> Cheers,
>
> Jeff
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum fringe case?
2013-01-23 16:53 autovacuum fringe case? AJ Weber <aweber@comcast.net>
2013-01-23 19:13 ` Re: autovacuum fringe case? Jeff Janes <jeff.janes@gmail.com>
2013-01-23 22:48 ` Re: autovacuum fringe case? AJ Weber <aweber@comcast.net>
@ 2013-01-24 02:03 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2013-01-24 02:03 UTC (permalink / raw)
To: AJ Weber <aweber@comcast.net>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-performance
AJ Weber escribió:
> On 1/23/2013 2:13 PM, Jeff Janes wrote:
> >Scheduling a manual vacuum should be fine (but keep in mind that
> >vacuum has very different default cost_delay settings than autovacuum
> >does. If the server is completely idle that shouldn't matter, but if
> >it is only mostly idle, you might want to throttle the IO a bit). But
> >I certainly would not disable autovacuum without further evidence. If
> >a table only needs to be vacuumed once a day and you preemptively do
> >it at 3a.m., then autovac won't bother to do it itself during the day.
> > So there is no point, but much risk, in also turning autovac off.
> If I set autovacuum_max_workers = 1, will that effectively
> single-thread it so I don't have two running at once? Maybe that'll
> mitigate disk contention a little at least?
If you have a single one, it will go three times as fast. If you want
to make the whole thing go slower (i.e. cause less impact on your I/O
system when running), crank up autovacuum_vacuum_cost_delay.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum fringe case?
2013-01-23 16:53 autovacuum fringe case? AJ Weber <aweber@comcast.net>
2013-01-23 19:13 ` Re: autovacuum fringe case? Jeff Janes <jeff.janes@gmail.com>
2013-01-23 22:48 ` Re: autovacuum fringe case? AJ Weber <aweber@comcast.net>
@ 2013-01-24 03:06 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jeff Janes @ 2013-01-24 03:06 UTC (permalink / raw)
To: AJ Weber <aweber@comcast.net>; +Cc: pgsql-performance
On Wednesday, January 23, 2013, AJ Weber wrote:
>
>
> Is there a skinny set of instructions on loading pg_stat_statements? Or
> should I just log them and review them from there?
>
Make sure you have installed contrib. (How you do that depends on how you
installed PostgreSQL in the first place. If you installed from source, then
just follow "sudo make install" with "cd contrib; sudo make install")
Then, just change postgresql.conf so that
shared_preload_libraries = 'pg_stat_statements'
And restart the server.
Then in psql run
create extension pg_stat_statements ;
Cheers,
Jeff
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum fringe case?
@ 2013-01-23 22:17 Kevin Grittner <kgrittn@mail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2013-01-23 22:17 UTC (permalink / raw)
To: AJ Weber <aweber@comcast.net>; pgsql-performance
AJ Weber wrote:
> Is it possible that some spikes in IO could be attributable to
> the autovacuum process? Is there a way to check this theory?
Taking a look at the ps aux listing, pg_stat_activity, and pg_locks
should help establish a cause, or at least rule out a number of
possibilities. There is a known issue with autovacuum when it tries
to reduce the size of a table which is found to be larger than it
currently needs to be while other transactions try to access the
table. This issue will be fixed in the next minor release for 9.0
and above. If this is the issue a manual VACUUM ANALYZE will fix
things -- at least for a while.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2013-01-24 03:06 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-23 16:53 autovacuum fringe case? AJ Weber <aweber@comcast.net>
2013-01-23 19:03 ` Evgeniy Shishkin <itparanoia@gmail.com>
2013-01-23 19:13 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-23 22:48 ` AJ Weber <aweber@comcast.net>
2013-01-24 02:03 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2013-01-24 03:06 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-23 22:17 Re: autovacuum fringe case? Kevin Grittner <kgrittn@mail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox