Re: PostgreSQL on ZFS: performance tuning

On 9/27/2016 23:06, Jov wrote:
>
> +1
> larger record size can increase compression ratio，so reduce the io.
>
> Did you set atime off for zfs?
>
>
> 2016年9月28日 6:16 AM，"Karl Denninger" <karl(at)denninger(dot)net
> <mailto:karl(at)denninger(dot)net>>写道：
>
> On 9/27/2016 16:38, Tomas Vondra wrote:
>> On 09/27/2016 06:00 PM, Torsten Zuehlsdorff wrote:
>>>
>>>
>>> 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?
>>
>> Because it's not a global value, but an estimate of how much RAM
>> is available as a cache for a single query. So if you're running
>> 10 queries at the same time, they'll have to share the memory.
>>
>> It's a bit trickier as there's often a fair amount of
>> cross-backend sharing (backends accessing the same data, so it's
>> likely one backend loads data into cache, and then other backends
>> access it too).
>>
>> It also ignores that memory may get allocated for other reasons -
>> some queries may allocate quite a bit of memory for
>> sorts/aggregations, so not only is
>>
>> effective_cache_size = RAM - shared_buffers
>>
>> excessive as it ignores the per-query nature, but also because it
>> neglects these other allocations.
>>
>> regards
>>
> You may well find that with lz4 compression a 128kb record size on
> that filesystem is materially faster -- it is here for most
> workloads under Postgres.
>
>
>
> --
> Karl Denninger
> karl(at)denninger(dot)net <mailto:karl(at)denninger(dot)net>
> /The Market Ticker/
> /[S/MIME encrypted email preferred]/
>

Yes.

Non-default stuff...

dbms/ticker-9.5 compressratio 1.88x -
dbms/ticker-9.5 mounted yes -
dbms/ticker-9.5 quota none default
dbms/ticker-9.5 reservation none default
dbms/ticker-9.5 recordsize 128K default
dbms/ticker-9.5 mountpoint /dbms/ticker-9.5 local
dbms/ticker-9.5 sharenfs off default
dbms/ticker-9.5 checksum on default
dbms/ticker-9.5 compression lz4 inherited
from dbms
dbms/ticker-9.5 atime off inherited
from dbms
dbms/ticker-9.5 logbias throughput inherited
from dbms

--
Karl Denninger
karl(at)denninger(dot)net <mailto:karl(at)denninger(dot)net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/