Database tuning when using zfs datasets.

[Alain De Vos]

Note, info on internet can be outdated ,so compare sources.


1. Postgres

zfs set primarycache=all
zfs set logbias=latency
zfs set recordsize=32K

2.Mysql/Mariadb

zfs set primarycache=metadata
zfs set logbias=throughput
zfs set recordsize=16K

3.Influxdb

zfs set primarycache=all
zfs set logbias=latency
zfs set recordsize=64K

4.Sqlite

zfs set primarycache=all
zfs set logbias=latency
zfs set recordsize=4K

For 64GB memory :

posgresql.conf

max_connections = 100            # (change requires restart)
shared_buffers = 8GB            # min 128kB
work_mem = 32MB                # min 64kB
maintenance_work_mem = 1GB        # min 64kB
dynamic_shared_memory_type = posix    # the default is usually the first option
wal_sync_method = fdatasync
full_page_writes = off            # recover from partial page writes
wal_init_zero = off
wal_init_zero = off
wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
max_wal_size = 2GB
min_wal_size = 1GB
effective_cache_size = 24GB
autovacuum_worker_slots = 16    # autovacuum worker slots to allocate
logging_collector = off

my.cnf

[mysqld]
innodb_buffer_pool_size         = 16G
innodb_log_file_size            = 2G
innodb_log_buffer_size          = 64M
innodb_page_size                = 16k
innodb_buffer_pool_instances    = 16
innodb_flush_method             = fsync
innodb_flush_log_at_trx_commit  = 1
innodb_doublewrite              = 0
max_connections                 = 100
key_buffer_size                 = 64M
tmp_table_size                  = 64M
max_heap_table_size             = 64M
sort_buffer_size                = 2M
read_buffer_size                = 2M
read_rnd_buffer_size            = 4M
join_buffer_size                = 2M

That's all folks !!!