1.几个涉及到的配置讲解
shared_buffers (integer)
设置数据库服务器将使用的共享内存缓冲区量。默认通常是 128 兆字节(128MB),但是如果你的内核设置不支持(在initdb时决定),那么可以会更少。这个设置必须至少为 128 千字节(BLCKSZ的非默认值将改变最小值)。不过为了更好的性能,通常会使用明显高于最小值的设置。
如果有一个专用的 1GB 或更多内存的数据库服务器, 一个合理的shared_buffers开始值是系统内存的 25%。 即使很大的shared_buffers有效, 也会造成一些工作负载, 但因为PostgreSQL同样依赖操作系统的高速缓冲区, 将shared_buffers设置为超过 40% 的RAM不太可能比一个小点值工作得更好。 为了能把对写大量新的或改变的数据的处理分布在一个较长的时间段内, shared_buffers更大的 设置通常要求对max_wal_size也做相应增加。
如果系统内存小于 1GB,一个较小的 RAM 百分数是合适的,这样可以为操作系统留下足够的空间。 同时,在 Windows 上,shared_buffers设置得较大也不一定有效。你会发现保持相对低的设置并且更多使用操作系统高速缓存会得到更好的结果。Windows 上可用的shared_buffers值通常是从 64MB 到 512 MB。
max_wal_size (integer)
在自动WAL检查点使得WAL增长到最大尺寸。这是软限制;特殊情况下WAL大小可以超过 max_wal_size,如重负载下,错误archive_command,或者 较大wal_keep_segments的设置。缺省是1GB。 增加这个参数会延长崩溃恢复所需要的时间。 这个参数只能在postgresql.conf文件或者服务器命令行上设置。
work_mem (integer)
指定在写到临时磁盘文件之前被内部排序操作和哈希表使用的内存量。该值默认为四兆字节(4MB)。注意对于一个复杂查询, 可能会并行运行好几个排序或者哈希操作;每个操作都会被允许使用这个参数指定的内存量,然后才会开始写数据到临时文件。同样,几个正在运行的会话可能并发进行这样的操作。因此被使用的总内存可能是work_mem值的好几倍,在选择这个值时一定要记住这一点。ORDER BY、DISTINCT和归并连接都要用到排序操作。哈希连接、基于哈希的聚集以及基于哈希的IN子查询处理中都要用到哈希表。
effective_cache_size (integer)
设置规划器对一个单一查询可用的有效磁盘缓冲区尺寸的假设。这个参数会被考虑在使用一个索引的代价估计中,更高的数值会使得索引扫描更可能被使用,更低的数值会使得顺序扫描更可能被使用。在设置这个参数时,你还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件的内核磁盘缓冲区。另外,还要考虑预计在不同表上的并发查询数目,因为它们必须共享可用的空间。这个参数对PostgreSQL分配的共享内存尺寸没有影响,它也不会保留内核磁盘缓冲,它只用于估计的目的。系统也不会假设在查询之间数据会保留在磁盘缓冲中。默认值是 4吉字节(4GB)。
maintenance_work_mem (integer)
指定在维护性操作(例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY)中使用的 最大的内存量。其默认值是 64 兆字节(64MB)。因为在一个数据库会话中,一个时刻只有一个这样的操作可以被执行,并且一个数据库安装通常不会有太多这样的操作并发执行, 把这个数值设置得比work_mem大很多是安全的。 更大的设置可以改进清理和恢复数据库转储的性能。
注意当自动清理运行时,可能会分配最多达这个内存的autovacuum_max_workers倍,因此要小心不要把该默认值设置得太高。 通过独立地设置autovacuum_work_mem可能会对控制这种情况 有所帮助。
2.修改 postgresql.conf 配置
1 | $ vi /var/lib/postgresql/data/postgresql.conf |
退出docker命令行,重启postgres容器
1 | $ docker restart postgres |
3.性能测试
优化前
1 | # pgbench -i -s 20 test --username=postgres |
优化后
1 | # pgbench -i -s 20 test --username=postgres |
相关链接