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

> On Wed, Oct 17, 2012 at 1:53 AM, Martin French
> <Martin(dot)French(at)romaxtech(dot)com> wrote:
>
> Thanks for your response.
>
> > What are the settings for:
> > work_mem
> 100MB
This is a little higher than I would ordinarily set. I tend to cap at
about 64MB

>
> > maintenance_work_mem
> 64MB
In Contrast, this is a little low for me, but I guess that table size is a
big factor here.

>
> > How many concurrent connections are there?
> ~20
>
> > Have you ran explain analyze on the query that doesn't crash (i.e the
old
> > box) to get the exact execution plan?
>
> I can try that in the morning, but I didn't think this was relevant. I
> know cost estimates can be off, but can the plan actually change
> between a vanilla explain and an explain analyze?
>
The explain analyze gives a more detailed output.

>
> > Has the DB been vacuum analyzed?
>
> Not outside of autovacuum, no, but it's actually a former replica of
> the first database (sorry I neglected to mention this earlier).
>

This may be worthwhile. Even with autovacuum on, I still Vac Analyze
manually during quiet periods. whether it's actually necessary or not,
figure it's belt and braces.

Looking at the explain, It'd suggest the tables aren't very large, so I
can't see really why there'd be a problem. Notwithstanding the fact that
you are only relatively small shared_buffers.

Are there no other messages in the log files re: out of memory. There
should be a dump which will show you where the memory usage is occurring.

Other than that, you may want to consider increasing the shared buffers
and see if that has any effect. Alternately, you may want to increase
max_pred_locks_per_transaction beyond the default of 64, although this is
not a parameter I've had to yet adjust.

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