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 k...@denninger.net <mailto:k...@denninger.net> /The Market Ticker/ /[S/MIME encrypted email preferred]/
smime.p7s
Description: S/MIME Cryptographic Signature
