本文探讨了PostgreSQL中关键参数的设置方法，包括shared_buffers、effective_cache_size和checkpoint_segments等，提供了不同内存配置下的推荐值，并解释了这些设置如何影响数据库性能。
shared_buffers
[quote]if you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the memory in your system. If you have less ram you'll have to account more carefully for how much RAM the OS is taking up, closer to 15% is more typical there. [/quote]
[quote]on Windows (and on PostgreSQL versions before 8.1), large values for shared_buffers aren't as effective, and you may find better results keeping it relatively low and using the OS cache more instead. On Windows the useful range is 64MB to 512MB[/quote]
effective_cache_size
[quote]Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount. HUP is enough ,shared_buffers 一半[/quote]
checkpoint_segments
[quote]doing one every 48MB will be a serious performance bottleneck. 小于3性能慢
Unless you're running on a very small configuration, you'll almost certainly be better setting this to at least 10, which also allows usefully increasing the completion target.至少10
For more write-heavy systems, values from 32 (checkpoint every 512MB) to 256 (every 4GB) are popular nowadays.稍重的写,32 - 256
Normally the large settings (>64/1GB) are only used for bulk loading. 大负载,用64以上[/quote]
fsync关闭会使数据不一致
full_page_writes synchronous_commit只会丢失整个事务,不会不一致
[quote]if you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the memory in your system. If you have less ram you'll have to account more carefully for how much RAM the OS is taking up, closer to 15% is more typical there. [/quote]
[quote]on Windows (and on PostgreSQL versions before 8.1), large values for shared_buffers aren't as effective, and you may find better results keeping it relatively low and using the OS cache more instead. On Windows the useful range is 64MB to 512MB[/quote]
effective_cache_size
[quote]Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount. HUP is enough ,shared_buffers 一半[/quote]
checkpoint_segments
[quote]doing one every 48MB will be a serious performance bottleneck. 小于3性能慢
Unless you're running on a very small configuration, you'll almost certainly be better setting this to at least 10, which also allows usefully increasing the completion target.至少10
For more write-heavy systems, values from 32 (checkpoint every 512MB) to 256 (every 4GB) are popular nowadays.稍重的写,32 - 256
Normally the large settings (>64/1GB) are only used for bulk loading. 大负载,用64以上[/quote]
fsync关闭会使数据不一致
full_page_writes synchronous_commit只会丢失整个事务,不会不一致
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
