Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6

Поиск
Список
Период
Сортировка
От Maciek Sakrejda
Тема Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6
Дата
Msg-id CAOtHd0C_BbxwnMVbTvAdmx0O=8z6bPEyxfeGFp=ZnTDkzysgeg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6  (Martin French <Martin.French@romaxtech.com>)
Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
We've run into a perplexing issue with a customer database. He moved
from a 9.1.5 to a 9.1.6 and upgraded from an EC2 m1.medium (3.75GB
RAM, 1.3 GB shmmax), to an m2.xlarge (17GB RAM, 5.7 GB shmmax), and is
now regularly getting constant errors regarding running out of shared
memory (there were none on the old system in the recent couple of
days' logs from before the upgrade):

ERROR:  out of shared memory
HINT:  You might need to increase max_pred_locks_per_transaction.

The query causing this has structurally identical plans on both systems:

old: http://explain.depesz.com/s/Epzq
new: http://explain.depesz.com/s/WZo

The settings ( "select name, setting from pg_settings where source <>
'default' and name not like 'log%' and name not like 'ssl%' and name
not like 'syslog%'" ) are almost identical
(max_pred_locks_per_transaction itself is at the default):

17c17
<  effective_cache_size         | 1530000
---
>  effective_cache_size         | 337500
38c38
<  shared_buffers               | 424960
---
>  shared_buffers               | 93696

The kernels are both 2.6.32. The workload has not changed
significantly. Could something in 9.1.6 be to blame here? Looking at
the changelog, this seems vanishingly unlikely. Any ideas?


В списке pgsql-performance по дате отправления:

Предыдущее
От: Chris Ruprecht
Дата:
Сообщение: have: seq scan - want: index scan
Следующее
От: Martin French
Дата:
Сообщение: Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6