Autovacumm postgres 9.3 Zabbix 2.2 + strong machine

[pietro54]

Hi,
I got new zabbix, new machine, new postgres.
Could you provide some helpful tips how should look autovacumm config in postgres, please?

My config:
Zabbix 2.2
Postgres 9.3
8 Ssd disk in raid 10
Adaptec controlr
Over 2000MB/s with probe 1MB.
thousands of IO in lower probes.
Size of base ~300GB
After dump and restore ~150


My conf looks like that:

autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
autovacuum_max_workers = 5 # max number of autovacuum subprocesses
# (change requires restart)
autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
autovacuum_freeze_max_age = 20000000 # maximum XID age before forced vacuum
# (change requires restart)
autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit

[avecsi]

Hi,

what is your OS?

what filesystem do you use?

what is your psql config?

how much is your Required server performance, new values per second?

[pietro54]

Hi
I wrote most this info on top of my last message.

file system - ext4
NVPS ~550

you need full pg config?

[Colttt]

please post the following:

 egrep -v '^#|^$' /etc//postgres/pg.conf

how much RAM do you have?

[avecsi]

are you using dedicated box for database?

ext4 refer that you are using linux/unix

kernel shmmax and kernel shmall also worth a check

cat /proc/sys/kernel/shmmax
cat /proc/sys/kernel/shmall

[pietro54]

Hi

it looks like this

PG config

egrep -v '^#|^$' /var/lib/pgsql/9.3/data/postgresql.conf
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 300 # (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# 0 selects the system default
# 0 selects the system default
# 0 selects the system default
shared_buffers = 12288MB # min 128kB
# (change requires restart)
temp_buffers = 80MB # min 800kB
# (change requires restart)
work_mem = 1024MB # min 64kB
maintenance_work_mem = 2048MB # min 1MB
# in kB, or -1 for no limit
# (change requires restart)
# (change requires restart)
# off, local, remote_write, or on
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
full_page_writes = off # recover from partial page writes
# (change requires restart)
checkpoint_segments = 16 # in logfile segments, min 1, 16MB each
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
# (change requires restart)
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
# number of seconds; 0 disables
# (change requires restart)
# comma-separated list of application_name
# from standby(s); '*' = all
# (change requires restart)
# when reading WAL from archive;
# -1 allows indefinite delay
# when reading streaming WAL;
# -1 allows indefinite delay
# 0 disables
# query conflicts
# communication from master
# in milliseconds; 0 disables
cpu_tuple_cost = 0.0030 # same scale as above
cpu_index_tuple_cost = 0.0010 # same scale as above
cpu_operator_cost = 0.0005 # same scale as above
effective_cache_size = 12384MB
# JOIN clauses
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
log_line_prefix = '< %m >' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'Poland'
# requires track_counts to also be on.
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
# (change requires restart)
# vacuum
# analyze
# (change requires restart)
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
# autovacuum, -1 means use
# vacuum_cost_limit
# only default tablespace
datestyle = 'iso, dmy'
timezone = 'Poland'
# abbreviations. Currently, there are
# Default
# Australia
# India
# You can create your own file in
# share/timezonesets/.
# encoding
lc_messages = 'pl_PL.UTF-8' # locale for system error message
# strings
lc_monetary = 'pl_PL.UTF-8' # locale for monetary formatting
lc_numeric = 'pl_PL.UTF-8' # locale for number formatting
lc_time = 'pl_PL.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.simple'
# (change requires restart)
# (change requires restart)
# directory 'conf.d'

Memory

cat /proc/sys/kernel/shmmax
68719476736
cat /proc/sys/kernel/shmall
4294967296

free -m
total used free shared buffers cached
Mem: 24016 23575 441 0 3 18462
-/+ buffers/cache: 5109 18906
Swap: 1023 690 333

If there will necessary, or reasonable to do I can add another 24gb of ram.
But for now, I maybe cannot use or don`t know how to made good use of memory.


This is just one machine for whole zabbix (zabbix frontend, apache, postgres).



This will be good hint - performance of disk from iometer:

https://www.flickr.com/photos/123690158@N08/13933538663/

100% means 100% of reads.
0% = 100% writes.

[soulhunter]

Hi!

Well, I don't have a lot of free time right now, but I can tell you this:


shared_buffers = 8G # well, I am assuming you will have lots of items, and thus will need to increase Zabbix caches too.

effective_cache_size = 12384MB # 12GB will be good, and a good approximation would be up to 15GB, depending on Zabbix memory configuration.

work_mem = 256MB # erm... remember this will be per connection, although it is a maximum (ie, the connection will not use this much unless it needs to), I believe 1024 is too high, maybe set to 128MB or something.

checkpoint_segments = 100 # set to something higher, like 100, maybe even 300, watch the logs for messages regarding checkpoint interval.

checkpoint_timeout = 30min

checkpoint_warning = 15min

effective_io_concurrency = 8

seq_page_cost = 1.0

random_page_cost = 1.0

default_statistics_target = 150


As for autovacuum, defaults should be fine, except that I would reduce this one:

autovacuum_vacuum_cost_delay = 2ms # or, if you prefer, maybe to 5ms, but with 8SSDs in RAID10, I believe 2ms will be ok


You will want to tune overcommit too, but I don't have the time for that right now, just make sure it is properly configured, or you will get unwanted OOM kills from time to time.

Remember that, if you are using Ubuntu 12.04, update to kernel 3.11 (at least to 3.9), due to a kernel bug that seriously affect pgsql performance.

Additionally, it is better to disable housekeeper, and use table partitioning, but with your array it could be possible to use housekeeper (at least for some time).

Ildefonso