On 29.07.2016 08:30, Tomas Vondra wrote:
On 07/29/2016 08:04 AM, trafdev wrote:
Hi.
I have an OLAP-oriented DB (light occasional bulk writes and heavy
aggregated selects over large periods of data) based on Postgres 9.5.3.
Server is a FreeBSD 10.3 with 64GB of RAM and 2x500GB SSD (root on ZFS,
mirror).
The largest table is 13GB (with a 4GB index on it), other tables are 4,
2 and less than 1GB.
After reading a lot of articles and "howto-s" I've collected following
set of tweaks and hints:
ZFS pools creation:
zfs create zroot/ara/sqldb
zfs create -o recordsize=8k -o primarycache=all zroot/ara/sqldb/pgsql
zfs get primarycache,recordsize,logbias,compression zroot/ara/sqldb/pgsql
NAME PROPERTY VALUE SOURCE
zroot/ara/sqldb/pgsql primarycache all local
zroot/ara/sqldb/pgsql recordsize 8K local
zroot/ara/sqldb/pgsql logbias latency local
zroot/ara/sqldb/pgsql compression lz4 inherited from zroot
L2ARC is disabled
VDEV cache is disabled
pgsql -c "mkdir /ara/sqldb/pgsql/data_ix"
pgsql -c "initdb --locale=en_US.UTF-8 -E UTF-8 -D /ara/sqldb/pgsql/data"
/etc/sysctl.conf
vfs.zfs.metaslab.lba_weighting_enabled=0
postgresql.conf:
listen_addresses = '*'
max_connections = 100
shared_buffers = 16GB
effective_cache_size = 48GB
It may not be a problem for your workload, but this effective_cache_size
value is far too high.
May i asked why? ZFS in default caches your size of RAM minus 1 GB.
Getting the shared buffer from the 64 GB RAM i would asume 47 GB would
be a better value. But this would not be far too high. So please can you
explain this?
Greetings,
Torsten
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
