On Wed, Mar 2, 2016 at 8:25 AM, Artem Tomyuk <ad...@leboutique.com> wrote: > Hi. > > I've noticed that autovac. process worked more than 10 minutes, during this > zabbix logged more than 90% IO disk utilization on db volume.... > > ===========>29237 2016-03-02 15:17:23 EET 00000 [24-1]LOG: automatic > vacuum of table "lb_upr.public._reference32": index scans: 1 > pages: 0 removed, 263307 remain > tuples: 298 removed, 1944753 remain, 0 are dead but not yet removable > buffer usage: 67814 hits, 265465 misses, 15647 dirtied > avg read rate: 3.183 MB/s, avg write rate: 0.188 MB/s > system usage: CPU 5.34s/6.27u sec elapsed 651.57 sec > > Is it possible to log autovac. io impact during it execution? > Is there any way to limit or "nice" autovac. process?
I'll assume you're running a fairly recent version of postgresql. There are a few settings that adjust how hard autovacuum works when it's working. autovacuum_max_workers tells autovacuum how many threads to vacuum with. Lowering this will limit the impact of autovacuum, but generally the default setting of 3 is reasonable on most machines. autovacuum_vacuum_cost_delay sets how to wail between internal rounds. Raising this makes autovacuum take bigger pauses internally. The default of 20ms is usually large enough to keep you out of trouble, but feel free to raise it and see if your IO utilization lowers. autovacuum_vacuum_cost_limit sets a limit to how much work to do between the pauses set by the cost delay above. Lowering this will cause autovac to do less work between pauses. Most of the time I'm adjusting these I'm making vacuum more aggressive, not less aggressive because vacuum falling behind is a problem on the large, fast production systems I work on. In your case you want to watch for when autovacuum IS running, and using a tool like vmstat or iostat or iotop, watch it for % utilization. You can then adjust cost delay and cost limit to make it less aggressive and see if your io util goes down. Note though that 90% utilization isn't 100% so it's not likely flooding the IO. But if you say raise cost delay from 20 to 40ms, it might drop to 75% or so. The primary goal here is to arrive at numbers that left autovacuum keep up with reclaiming the discarded tuples in the database without getting in the way of the workload. If your workload isn't slowing down, or isn't slowing down very much, during autobvacuum then you're OK. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
