From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Torsten Zuehlsdorff <mailinglists@toco-domains.de>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL on ZFS: performance tuning
Date: Tue, 27 Sep 2016 23:38:42 +0200
Message-ID: <2a522e5a-c79b-9bd5-46c8-441e8e762a87@2ndquadrant.com> (raw)
In-Reply-To: <5974493a-6785-1d7f-3b06-ba36468763ae@toco-domains.de>
References: <1cdf08f7-de8f-c55f-c1ec-29a5f15d5011@mail.ru>
<c3e5970c-312f-2c98-c3a7-b3273f1d7c07@2ndquadrant.com>
<5974493a-6785-1d7f-3b06-ba36468763ae@toco-domains.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, mailinglists@toco-domains.de
Subject: Re: PostgreSQL on ZFS: performance tuning
In-Reply-To: <2a522e5a-c79b-9bd5-46c8-441e8e762a87@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox