From: Tom Lane <tgl@sss.pgh.pa.us>
To: Maciek Sakrejda <m.sakrejda@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6
Date: Wed, 17 Oct 2012 10:18:46 -0400
Message-ID: <19833.1350483526@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOtHd0C_BbxwnMVbTvAdmx0O=8z6bPEyxfeGFp=ZnTDkzysgeg@mail.gmail.com>
References: <CAOtHd0C_BbxwnMVbTvAdmx0O=8z6bPEyxfeGFp=ZnTDkzysgeg@mail.gmail.com>
Maciek Sakrejda <m.sakrejda@gmail.com> writes:
> 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.
This has nothing to do with work_mem nor maintenance_work_mem; rather,
it means you're running out of space in the database-wide lock table.
You need to take the hint's advice.
> 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 query in itself doesn't seem very exceptional. I wonder whether
you recently switched your application to use serializable mode? But
anyway, a query's demand for predicate locks can depend on a lot of
not-very-visible factors, such as how many physical pages the tuples
it accesses are spread across. I don't find it too hard to credit
that yesterday you were just under the limit and today you're just
over even though "nothing changed".
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, m.sakrejda@gmail.com
Subject: Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6
In-Reply-To: <19833.1350483526@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox