我也试过运行pgtune,它提供了以下建议,调整了更多参数,但这没有改变任何东西.它建议使用1/4 RAM大小的shared_buffers,这似乎符合其他地方的建议(特别是PG维基).
default_statistics_target = 50 maintenance_work_mem = 960MB constraint_exclusion = on checkpoint_completion_target = 0.9 effective_cache_size = 11GB work_mem = 96MB wal_buffers = 8MB checkpoint_segments = 16 shared_buffers = 3840MB max_connections = 80
我尝试在更改设置后重新索引整个数据库(使用reindex数据库),但这也无济于事.我玩过shared_buffers和work_mem.逐渐从非常保守的默认值(128k / 1MB)改变它们逐渐降低性能.
我在几个查询上运行了EXPLAIN(ANALYZE,BUFFERS),罪魁祸首似乎是Hash Join明显变慢了.我不清楚为什么.
举一些具体的例子,我有以下查询.它在默认配置下运行~2100ms,在配置上运行~3300ms,缓冲区大小增加：
select count(*) from contest c left outer join contestparticipant cp on c.id=cp.contestId left outer join teammember tm on tm.contestparticipantid=cp.id left outer join staffmember sm on cp.id=sm.contestparticipantid left outer join person p on p.id=cp.personid left outer join personinfo pi on pi.id=cp.personinfoid where pi.lastname like '%b%' or pi.firstname like '%a%';
上述查询的EXPLAIN(ANALYZE,BUFFERS)：
>默认缓冲区：http://explain.depesz.com/s/xaHJ
>更大的缓冲区：http://explain.depesz.com/s/Plk
问题是,当我增加缓冲区大小时,为什么我观察到性能下降？机器肯定没有内存不足.如果OS中的共享内存(shmmax和shmall)的分配设置为非常大的值,那应该不是问题.我也没有在Postgres日志中收到任何错误.我在默认配置中运行autovacuum,但我不认为它与它有任何关系.所有查询都在几秒钟内在同一台机器上运行,只需更改配置(并重新启动PG).
编辑：
我刚刚发现了一个特别有趣的事实：当我在2010年中期的iMac(OSX 10.7.5)上使用Postgres 9.2.1和16GB RAM执行相同的测试时,我没有遇到减速.
特别：
set work_mem='1MB'; select ...; // running time is ~1800 ms set work_mem='96MB'; select ...' // running time is ~1500 ms
当我在服务器上执行完全相同的查询(上面的查询)时,我得到2100毫秒,work_mem = 1MB,3200毫秒,96 MB.
Mac有SSD,因此可以理解的速度更快,但它表现出我期望的行为.
看看你的查询计划,有一件事让我印象深刻,看两个计划的缓冲区命中是非常不同的,甚至连续扫描都比较慢.我怀疑这个问题与预读缓存有关,并且空间较小.这意味着你是在为重用索引和反对在磁盘上读表而偏置内存.
我的理解是Postgresql在从磁盘读取之前会查找页面的缓存,因为它不知道操作系统缓存是否会包含该页面.因为页面然后停留在缓存中,并且因为缓存比OS缓存慢,所以这会更改快速查询的类型.事实上,除了work_mem问题之外,阅读计划看起来所有的查询信息都来自缓存,但这是一个关于哪个缓存的问题.
work_mem：我们可以为排序或相关的连接操作分配多少内存.这是每个操作,而不是每个语句或每个后端,因此单个复杂查询可以使用此数量的内存.目前尚不清楚你是否达到了这个限制,但值得注意并注意到.如果增加太多,则会丢失可用于读取缓存和共享缓冲区的内存.
shared_buffers：分配给实际Postgresql页面队列的内存量.现在,理想情况下,数据库的有趣集合将保留在此处和读取缓冲区中缓存的内存中.但是,这样做可确保所有后端中最常用的信息得到缓存而不会刷新到磁盘.在Linux上,这个缓存明显慢于操作系统磁盘缓存,但它保证了操作系统磁盘缓存不会对Postgresql透明.这显然是你的问题所在.
所以当我们有一个请求时,我们首先检查共享缓冲区,因为Postgresql对这个缓存有深入的了解,并查找这些页面.如果它们不存在,我们要求操作系统从文件中打开它们,如果操作系统缓存了结果,则返回缓存副本(这比共享缓冲区快,但Pg无法判断它是缓存还是磁盘和磁盘要慢得多,所以Postgresql通常不会抓住这个机会).请记住,这也会影响随机页面和顺序页面访问.因此,使用较低的shared_buffers设置可以获得更好的性能.
我的直觉是,在具有更大shared_buffer设置的高并发环境中,您可能会获得更好或至少更一致的性能.还要记住Postgresql抓住这个内存并保留它,所以如果你在系统上运行其他东西,读缓冲区将保存其他进程读取的文件.这是一个非常庞大而复杂的话题.较大的共享缓冲区设置可提供更好的性能保证,但在某些情况下可能会降低性能.
