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

Поиск
Список
Период
Сортировка
От Martin French
Тема Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6
Дата
Msg-id OFCB9AC532.DBD5E8AD-ON80257A9A.0032C4CA-80257A9A.0034166A@romaxtech.com
обсуждение исходный текст
Ответ на Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6  (Maciek Sakrejda <m.sakrejda@gmail.com>)
Список pgsql-performance
Дерево обсуждения
> On Wed, Oct 17, 2012 at 1:53 AM, Martin French
> <Martin.French@romaxtech.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@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.
=================================================

В списке pgsql-performance по дате отправления:

Предыдущее
От: Maciek Sakrejda
Дата:
Сообщение: Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6
Следующее
От: houmanb
Дата:
Сообщение: Re: SELECT AND AGG huge tables