Re: PostgreSQL on ZFS: performance tuning

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