Short Tip: tuning Zabbix Database Part 2

[part 1]

Besides disabling autovacuum you can also try to increase the performance if you have got enough free resources. There are three important options for this:

autovacuum_vacuum_cost_delay
I lowered the sleep between two autovacuums to 0.2ms

autovacuum_vacuum_cost_limit/vacuum_cost_limit
(setting autovacuum_vacuum_cost_limit to -1 will instruct zabbix to use the vacuum_cost_limit value for autovacuum). The accumulated cost limit that will cause a vacuum to sleep, increasing it will result in a way higher IO usage. Be careful with this if you do not have a very fast IO storage which can deliver high random IO power (SSD Raid in my case). I increased the default 200 to 6000.

autovacuum_max_workers
The default is pretty low and my machine had some free cores so I increased it to 12.

Puppet code to apply these changes to a Postgres 9.4 instance with Puppetlabs/PostgreSQL module:

  postgresql::server::config_entry{'autovacuum_max_workers':
    value => 12,
  }
  postgresql::server::config_entry{'autovacuum_vacuum_cost_delay':
    value => '2ms',
  }
  postgresql::server::config_entry{'autovacuum_vacuum_cost_limit':
    value => 6000,
  }
  postgresql::server::config_entry{'vacuum_cost_limit':
    value => 6000,
  }