> > 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@romaxtech.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. =================================================
