From: Karl Denninger <karl@denninger.net>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL on ZFS: performance tuning
Date: Tue, 27 Sep 2016 17:15:26 -0500
Message-ID: <d2ead42c-3428-a562-0d83-dec4ac6551f1@denninger.net> (raw)
In-Reply-To: <2a522e5a-c79b-9bd5-46c8-441e8e762a87@2ndquadrant.com>
References: <1cdf08f7-de8f-c55f-c1ec-29a5f15d5011@mail.ru>
<c3e5970c-312f-2c98-c3a7-b3273f1d7c07@2ndquadrant.com>
<5974493a-6785-1d7f-3b06-ba36468763ae@toco-domains.de>
<2a522e5a-c79b-9bd5-46c8-441e8e762a87@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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@denninger.net <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 2.9K) download
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: karl@denninger.net
Subject: Re: PostgreSQL on ZFS: performance tuning
In-Reply-To: <d2ead42c-3428-a562-0d83-dec4ac6551f1@denninger.net>
* 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