合 PG中的常见参数配置
Tags: PG参数参数配置effective_cache_sizeshared_buffers内存参数
- 优化参数
- shared_buffers
- wal_buffers
- 查看当前wal_buffers的值:
- effective_cache_size
- work_mem
- 查看当前work_mem的值:
- maintenance_work_mem
- 查看当前maintenance_work_mem的值:
- fsync
- synchronous_commit
- 查看当前synchronous_commit的设置值:
- checkpoint_timeout,checkpoint_completion_target
- 查看当前checkpoint_timeout和checkpoint_completion_target的值:
- MAX_CONNECTIONS
- superuser_reserved_connections
- temp_buffers
- max_wal_size
- wal_sync_method
- effective_cache_size
- default_statistics_target
- random_page_cost
- commit_delay
- commit_siblings
- 增加maintenance_work_mem参数大小
- 增加checkpoint_segments参数的大小
- 设置archive_mode无效
- autovacuum相关参数 (autovacuum介绍文章)
- PG中的effective_cache_size详解
- 参考
优化参数
参考:https://www.xmmup.com/pgzhongdeshared_bufferscanshupeizhi.html
wal_buffers
PostgreSQL将其WAL(预写日志)记录写入缓冲区,然后将这些缓冲区刷新到磁盘。
缓冲区的默认大小,由wal_buffers定义,但如果您有大量并发连接,则较高的值可以提供更好的性能。
PostgreSQL将其WAL(预写日志)记录写入缓冲区,然后将这些缓冲区刷新到磁盘。由wal_buffers定义的缓冲区的默认大小为16MB,但如果有大量并发连接的话,则设置为一个较高的值可以提供更好的性能。
查看当前wal_buffers的值:
1 2 3 4 5 | postgres=# show wal_buffers; wal_buffers ------------- 4MB (1 row) |
effective_cache_size
该effective_cache_size提供了可以用于磁盘缓存存储器的估计。
它只是一个指导原则,而不是确切分配的内存或缓存大小。
它不分配实际内存,而是告诉优化器内核中可用的缓存量。
如果将此值设置得太低,查询计划程序可以决定不使用某些索引,即使它们有用。
因此,设置较大的值总是有益的。
effective_cache_size提供可用于磁盘高速缓存的内存量的估计值。它只是一个建议值,而不是确切分配的内存或缓存大小。它不会实际分配内存,而是会告知优化器内核中可用的缓存量。在一个索引的代价估计中,更高的数值会使得索引扫描更可能被使用,更低的数值会使得顺序扫描更可能被使用。在设置这个参数时,还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件的内核磁盘缓冲区。默认值是4GB。
查看当前effective_cache_size的值:
1 2 3 4 5 | postgres=# show effective_cache_size; effective_cache_size ---------------------- 4GB (1 row) |
work_mem
指定在写入磁盘上的临时文件之前,ORDER BY,DISTINCT,JOIN和哈希表的内部操作将使用的内存量。
此配置用于复杂排序,如果必须进行复杂排序,则增加work_mem的值以获得良好结果。
内存中的排序比溢出到磁盘的排序快得多。
设置非常高的值可能会导致部署环境出现内存瓶颈,因为此参数是按用户排序操作。
如果您有许多用户尝试执行排序操作,系统将为所有用户分配 work_mem * 总排序操作 。
全局设置此参数可能会导致内存使用率过高,强烈建议在会话级别修改它。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | work_mem = 2MB testdb=# SET work_mem TO "2MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN \----------------------------------------------------------------------------------- Gather Merge (cost=509181.84..1706542.14 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=508181.79..514431.86 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) (5 rows) testdb=# SET work_mem TO "2MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN \----------------------------------------------------------------------------------- Gather Merge (cost=509181.84..1706542.14 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=508181.79..514431.86 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) (5 rows) |
初始查询的排序节点的估计成本为514431.86。成本是一个任意的计算单位。对于上面的查询,我们的work_mem只有2MB。出于测试目的,让我们将其增加到256MB并查看是否对成本有任何影响。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | work_mem = 256MB testdb=# SET work_mem TO "256MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN \----------------------------------------------------------------------------------- Gather Merge (cost=355367.34..1552727.64 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=354367.29..360617.36 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) testdb=# SET work_mem TO "256MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN \----------------------------------------------------------------------------------- Gather Merge (cost=355367.34..1552727.64 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=354367.29..360617.36 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) |
查询成本从514431.86减少到360617.36 - 减少30%。
此配置用于复合排序。内存中的排序比溢出到磁盘的排序快得多,设置非常高的值可能会导致部署环境出现内存瓶颈,因为此参数是按用户排序操作。如果有多个用户尝试执行排序操作,则系统将为所有用户分配大小为work_mem *
总排序操作数的空间。全局设置此参数可能会导致内存使用率过高,因此强烈建议在会话级别修改此参数值。默认值为4MB。
查看当前work_mem的值:
1 2 3 4 5 | postgres=# show work_mem; work_mem ---------- 4MB (1 row) |
maintenance_work_mem
maintenance_work_mem是用于维护任务的内存设置。默认值为64MB。
设置较大的值有助于执行VACUUM,RESTORE,CREATE INDEX,ADD FOREIGN KEY和ALTER TABLE等任务。
由于会话中只能同时执行其中一个操作,并且通常没有多个同时运行,因此它可能比work_mem大。
较大的配置可以提高VACUUM和数据库还原的性能。
执行autovacuum时,可以为此内存分配autovacuum_max_workers参数的配置次数,因此我们必须考虑这一点,或者配置autovacuum_work_mem参数来单独管理它。本参数可以针对每个session设置。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | maintenance_work_mem = 10MB postgres=# CHECKPOINT; postgres=# SET maintenance_work_mem to '10MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 170091.371 ms (02:50.091) postgres=# CHECKPOINT; postgres=# SET maintenance_work_mem to '10MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 170091.371 ms (02:50.091) maintenance_work_mem = 256MB postgres=# CHECKPOINT; postgres=# set maintenance_work_mem to '256MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 111274.903 ms (01:51.275) postgres=# CHECKPOINT; postgres=# set maintenance_work_mem to '256MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 111274.903 ms (01:51.275) |
当maintenance_work_mem设置为仅10MB时,索引创建时间为170091.371ms,但当我们将maintenance_work_mem设置增加到256MB时,该时间减少到111274.903 ms。
maintenance_work_mem
是用于维护任务的内存设置。默认值为64MB。设置较大的值对于VACUUM,RESTORE,CREATE INDEX,ADD FOREIGN KEY和ALTER TABLE等操作的性能提升效果显著。