To: pgsql-performance@postgresql.org
From: trafdev <trafdev@mail.ru>
Subject: PostgreSQL on ZFS: performance tuning
Message-ID: <1cdf08f7-de8f-c55f-c1ec-29a5f15d5011@mail.ru>
Date: Thu, 28 Jul 2016 23:04:55 -0700
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:45.0) Gecko/20100101
 Thunderbird/45.2.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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
work_mem = 500MB
maintenance_work_mem = 2GB
min_wal_size = 4GB
max_wal_size = 8GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1
log_lock_waits = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_destination = 'csvlog'
logging_collector = on
log_min_duration_statement = 10000
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 10000
track_io_timing = on


zfs-stats -A
------------------------------------------------------------------------
ZFS Subsystem Report				Thu Jul 28 21:58:46 2016
------------------------------------------------------------------------
ARC Summary: (HEALTHY)
	Memory Throttle Count:			0
ARC Misc:
	Deleted:				14.92b
	Recycle Misses:				7.01m
	Mutex Misses:				4.72m
	Evict Skips:				1.28b
ARC Size:				53.27%	32.59	GiB
	Target Size: (Adaptive)		53.28%	32.60	GiB
	Min Size (Hard Limit):		12.50%	7.65	GiB
	Max Size (High Water):		8:1	61.18	GiB
ARC Size Breakdown:
	Recently Used Cache Size:	92.83%	30.26	GiB
	Frequently Used Cache Size:	7.17%	2.34	GiB
ARC Hash Breakdown:
	Elements Max:				10.36m
	Elements Current:		78.09%	8.09m
	Collisions:				9.63b
	Chain Max:				26
	Chains:					1.49m
------------------------------------------------------------------------

zfs-stats -E
------------------------------------------------------------------------
ZFS Subsystem Report				Thu Jul 28 21:59:57 2016
------------------------------------------------------------------------
ARC Efficiency:					49.85b
	Cache Hit Ratio:		70.94%	35.36b
	Cache Miss Ratio:		29.06%	14.49b
	Actual Hit Ratio:		66.32%	33.06b
	Data Demand Efficiency:		84.85%	25.39b
	Data Prefetch Efficiency:	17.85%	12.90b
	CACHE HITS BY CACHE LIST:
	  Anonymously Used:		4.10%	1.45b
	  Most Recently Used:		37.82%	13.37b
	  Most Frequently Used:		55.67%	19.68b
	  Most Recently Used Ghost:	0.58%	203.42m
	  Most Frequently Used Ghost:	1.84%	649.83m
	CACHE HITS BY DATA TYPE:
	  Demand Data:			60.92%	21.54b
	  Prefetch Data:		6.51%	2.30b
	  Demand Metadata:		32.56%	11.51b
	  Prefetch Metadata:		0.00%	358.22k
	CACHE MISSES BY DATA TYPE:
	  Demand Data:			26.55%	3.85b
	  Prefetch Data:		73.13%	10.59b
	  Demand Metadata:		0.31%	44.95m
	  Prefetch Metadata:		0.00%	350.48k

zfs-stats -Z
------------------------------------------------------------------------
ZFS Subsystem Report				Thu Jul 28 22:02:46 2016
------------------------------------------------------------------------
File-Level Prefetch: (HEALTHY)
DMU Efficiency:					49.97b
	Hit Ratio:			55.85%	27.90b
	Miss Ratio:			44.15%	22.06b
	Colinear:				22.06b
	  Hit Ratio:			0.04%	7.93m
	  Miss Ratio:			99.96%	22.05b
	Stride:					17.85b
	  Hit Ratio:			99.61%	17.78b
	  Miss Ratio:			0.39%	69.46m
DMU Misc:
	Reclaim:				22.05b
	  Successes:			0.05%	10.53m
	  Failures:			99.95%	22.04b
	Streams:				10.14b
	  +Resets:			0.10%	9.97m
	  -Resets:			99.90%	10.13b
	  Bogus:				0


Notes\concerns:

- primarycache=metadata (recommended in most articles) produces a 
significant performance degradation (in SELECT queries);

- from what I can see, Postgres uses memory too carefully. I would like 
somehow to force it to keep accessed data in memory as long as possible. 
Instead I often see that even frequently accessed data is pushed out of 
memory cache for no apparent reasons.

Do I miss something important in my configs? Are there any double 
writes\reads somewhere because of OS\ZFS\Postgres caches? How to avoid them?

Please share your experience\tips. Thanks.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance