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.
|
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
