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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> On Wed, Oct 17, 2012 at 1:53 AM, Martin French
> <Martin.French@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
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.
=================================================

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux