[PERFORM] autovacuum fringe case?
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
Re: [PERFORM] autovacuum fringe case?
On 23.01.2013, at 20:53, AJ Weber awe...@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
Re: [PERFORM] autovacuum fringe case?
On Wed, Jan 23, 2013 at 8:53 AM, AJ Weber awe...@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
Re: [PERFORM] autovacuum fringe case?
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
Re: [PERFORM] autovacuum fringe case?
On 1/23/2013 2:13 PM, Jeff Janes wrote: On Wed, Jan 23, 2013 at 8:53 AM, AJ Weberawe...@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
Re: [PERFORM] autovacuum fringe case?
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 Herrerahttp://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
Re: [PERFORM] autovacuum fringe case?
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
