pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL on ZFS: performance tuning
11+ messages / 5 participants
[nested] [flat]
* PostgreSQL on ZFS: performance tuning
@ 2016-07-29 06:04 trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-28 04:11 ` Re: PostgreSQL on ZFS: performance tuning Jov <zhao6014@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: trafdev @ 2016-07-29 06:04 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
@ 2016-07-29 06:30 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-07-29 06:47 ` Re: PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-09-27 16:00 ` Re: PostgreSQL on ZFS: performance tuning Torsten Zuehlsdorff <mailinglists@toco-domains.de>
1 sibling, 2 replies; 11+ messages in thread
From: Tomas Vondra @ 2016-07-29 06:30 UTC (permalink / raw)
To: pgsql-performance
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.
> work_mem = 500MB
> maintenance_work_mem = 2GB
> min_wal_size = 4GB
> max_wal_size = 8GB
> checkpoint_completion_target = 0.9
You probably need to increase the checkpoint_timeout too.
> 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);
Those articles are wrong. PostgreSQL relies of filesystem cache, so it
needs primarycache=all.
>
> - 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.
>
This is probably a consequence of the primarycache misconfiguration.
>
> 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.
>
>
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2016-07-29 06:47 ` trafdev <trafdev@mail.ru>
1 sibling, 0 replies; 11+ messages in thread
From: trafdev @ 2016-07-29 06:47 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance
> > - 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.
> >
>
> This is probably a consequence of the primarycache misconfiguration.
Thanks! And I'm using "primarycache=all" in my deployment...
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2016-09-27 16:00 ` Torsten Zuehlsdorff <mailinglists@toco-domains.de>
2016-09-27 21:38 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 sibling, 1 reply; 11+ messages in thread
From: Torsten Zuehlsdorff @ 2016-09-27 16:00 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-performance
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?
Greetings,
Torsten
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 16:00 ` Re: PostgreSQL on ZFS: performance tuning Torsten Zuehlsdorff <mailinglists@toco-domains.de>
@ 2016-09-27 21:38 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 22:15 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 11+ messages in thread
From: Tomas Vondra @ 2016-09-27 21:38 UTC (permalink / raw)
To: Torsten Zuehlsdorff <mailinglists@toco-domains.de>; pgsql-performance
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
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 16:00 ` Re: PostgreSQL on ZFS: performance tuning Torsten Zuehlsdorff <mailinglists@toco-domains.de>
2016-09-27 21:38 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2016-09-27 22:15 ` Karl Denninger <karl@denninger.net>
2016-09-28 04:06 ` Re: PostgreSQL on ZFS: performance tuning Jov <zhao6014@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Karl Denninger @ 2016-09-27 22:15 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 16:00 ` Re: PostgreSQL on ZFS: performance tuning Torsten Zuehlsdorff <mailinglists@toco-domains.de>
2016-09-27 21:38 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 22:15 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
@ 2016-09-28 04:06 ` Jov <zhao6014@gmail.com>
2016-09-28 11:44 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 11+ messages in thread
From: Jov @ 2016-09-28 04:06 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: pgsql-performance
+1
larger record size can increase compression ratio，so reduce the io.
Did you set atime off for zfs?
2016年9月28日 6:16 AM，"Karl Denninger" <karl@denninger.net>写道：
> 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
> *The Market Ticker*
> *[S/MIME encrypted email preferred]*
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 16:00 ` Re: PostgreSQL on ZFS: performance tuning Torsten Zuehlsdorff <mailinglists@toco-domains.de>
2016-09-27 21:38 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 22:15 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
2016-09-28 04:06 ` Re: PostgreSQL on ZFS: performance tuning Jov <zhao6014@gmail.com>
@ 2016-09-28 11:44 ` Karl Denninger <karl@denninger.net>
2016-09-28 18:42 ` Re: PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
0 siblings, 1 reply; 11+ messages in thread
From: Karl Denninger @ 2016-09-28 11:44 UTC (permalink / raw)
To: pgsql-performance
On 9/27/2016 23:06, Jov wrote:
>
> +1
> larger record size can increase compression ratio，so reduce the io.
>
> Did you set atime off for zfs?
>
>
> 2016年9月28日 6:16 AM，"Karl Denninger" <karl@denninger.net
> <mailto:karl@denninger.net>>写道：
>
> 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]/
>
Yes.
Non-default stuff...
dbms/ticker-9.5 compressratio 1.88x -
dbms/ticker-9.5 mounted yes -
dbms/ticker-9.5 quota none default
dbms/ticker-9.5 reservation none default
dbms/ticker-9.5 recordsize 128K default
dbms/ticker-9.5 mountpoint /dbms/ticker-9.5 local
dbms/ticker-9.5 sharenfs off default
dbms/ticker-9.5 checksum on default
dbms/ticker-9.5 compression lz4 inherited
from dbms
dbms/ticker-9.5 atime off inherited
from dbms
dbms/ticker-9.5 logbias throughput inherited
from dbms
--
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 16:00 ` Re: PostgreSQL on ZFS: performance tuning Torsten Zuehlsdorff <mailinglists@toco-domains.de>
2016-09-27 21:38 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 22:15 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
2016-09-28 04:06 ` Re: PostgreSQL on ZFS: performance tuning Jov <zhao6014@gmail.com>
2016-09-28 11:44 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
@ 2016-09-28 18:42 ` trafdev <trafdev@mail.ru>
2016-09-28 19:06 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 11+ messages in thread
From: trafdev @ 2016-09-28 18:42 UTC (permalink / raw)
To: pgsql-performance
Thanks Jov and Karl!
What do you think about:
primarycache=all
for SELECT queries over same data sets?
> Yes.
>
> Non-default stuff...
>
> dbms/ticker-9.5 compressratio 1.88x -
> dbms/ticker-9.5 mounted yes -
> dbms/ticker-9.5 quota none default
> dbms/ticker-9.5 reservation none default
> dbms/ticker-9.5 recordsize 128K default
> dbms/ticker-9.5 mountpoint /dbms/ticker-9.5 local
> dbms/ticker-9.5 sharenfs off default
> dbms/ticker-9.5 checksum on default
> dbms/ticker-9.5 compression lz4 inherited from dbms
> dbms/ticker-9.5 atime off inherited from dbms
> dbms/ticker-9.5 logbias throughput inherited from dbms
>
>
> --
> Karl Denninger
> karl@denninger.net <mailto:karl@denninger.net>
> /The Market Ticker/
> /[S/MIME encrypted email preferred]/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 16:00 ` Re: PostgreSQL on ZFS: performance tuning Torsten Zuehlsdorff <mailinglists@toco-domains.de>
2016-09-27 21:38 ` Re: PostgreSQL on ZFS: performance tuning Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 22:15 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
2016-09-28 04:06 ` Re: PostgreSQL on ZFS: performance tuning Jov <zhao6014@gmail.com>
2016-09-28 11:44 ` Re: PostgreSQL on ZFS: performance tuning Karl Denninger <karl@denninger.net>
2016-09-28 18:42 ` Re: PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
@ 2016-09-28 19:06 ` Karl Denninger <karl@denninger.net>
0 siblings, 0 replies; 11+ messages in thread
From: Karl Denninger @ 2016-09-28 19:06 UTC (permalink / raw)
To: pgsql-performance
On 9/28/2016 13:42, trafdev wrote:
> Thanks Jov and Karl!
>
> What do you think about:
>
> primarycache=all
>
> for SELECT queries over same data sets?
>
>> Yes.
>>
>> Non-default stuff...
>>
>> dbms/ticker-9.5 compressratio 1.88x -
>> dbms/ticker-9.5 mounted yes -
>> dbms/ticker-9.5 quota none default
>> dbms/ticker-9.5 reservation none default
>> dbms/ticker-9.5 recordsize 128K default
>> dbms/ticker-9.5 mountpoint /dbms/ticker-9.5 local
>> dbms/ticker-9.5 sharenfs off default
>> dbms/ticker-9.5 checksum on default
>> dbms/ticker-9.5 compression lz4
>> inherited from dbms
>> dbms/ticker-9.5 atime off
>> inherited from dbms
>> dbms/ticker-9.5 logbias throughput
>> inherited from dbms
>>
>>
>> --
>> Karl Denninger
>> karl@denninger.net <mailto:karl@denninger.net>
>> /The Market Ticker/
>> /[S/MIME encrypted email preferred]/
>
Primarycache=all is the default; changing it ought to be contemplated
only under VERY specific circumstances. In the case of a database if
you shut off "all" then an 8kb data read with 128kb blocksize will
result in reading 128kb (the block size), returning the requested piece
out of the 128kb and then /throwing away /the rest of the data read
since you prohibited it from going into the ARC. That's
almost-certainly going to do bad things for throughput!
Note that having an L2ARC, which is the place where you might find
setting primarycache to have a benefit, is itself something you need to
instrument under your specific workload to see if its worth it. If you
want to know if it *might* be worth it you can use (on FreeBSD)
zfs-stats -E; if you're seeing materially more than 15% cache misses
then it *might* help, assuming what you put it on is *very* fast (e.g. SSD)
In addition if you're on FreeBSD (and you say you are) be aware that the
vm system and ZFS interact in some "interesting" ways under certain load
profiles. UMA is involved to a material degree in the issue. I have
done quite a bit of work on the internal ZFS code in this regard; 11.x
is better-behaved than 10.x to a quite-material degree. I have a patch
set out against both 10.x and 11.x that address some (but not all) of
the issues.
--
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL on ZFS: performance tuning
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
@ 2016-09-28 04:11 ` Jov <zhao6014@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jov @ 2016-09-28 04:11 UTC (permalink / raw)
To: trafdev <trafdev@mail.ru>; +Cc: pgsql-performance
using zfs，you can tune full page write off for pg，which can save wal write
io.
2016年7月29日 2:05 PM，"trafdev" <trafdev@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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2016-09-28 19:06 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-07-29 06:04 PostgreSQL on ZFS: performance tuning trafdev <trafdev@mail.ru>
2016-07-29 06:30 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-07-29 06:47 ` trafdev <trafdev@mail.ru>
2016-09-27 16:00 ` Torsten Zuehlsdorff <mailinglists@toco-domains.de>
2016-09-27 21:38 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-09-27 22:15 ` Karl Denninger <karl@denninger.net>
2016-09-28 04:06 ` Jov <zhao6014@gmail.com>
2016-09-28 11:44 ` Karl Denninger <karl@denninger.net>
2016-09-28 18:42 ` trafdev <trafdev@mail.ru>
2016-09-28 19:06 ` Karl Denninger <karl@denninger.net>
2016-09-28 04:11 ` Jov <zhao6014@gmail.com>
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