using zfs，you can tune full page write off for pg，which can save wal write io.
2016年7月29日 2:05 PM，"trafdev" <traf...@mail.ru>写道： > 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 >
