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

>
> 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?
>

What are the settings for:

work_mem
maintenance_work_mem

How many concurrent connections are there?

Have you ran explain analyze on the query that doesn't crash (i.e the old
box) to get the exact execution plan?

Has the DB been vacuum analyzed?

Cheers

=============================================

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info(at)romaxtech(dot)com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf
of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your
system and contact the sender. Thank you for your cooperation.
=================================================