Обсуждение: Configuration Issue ?
Hi
Please help. I have got a postgres 7.3.4 database running on RedHat ES 3, with 8GB of physical memory in it. The machine is shared with my application which is pretty intensive in doing selects and updates against the database, but there are usually no more than 10 connections to the database at any time.
Despite having 8GB of RAM on the machine, the machine is frequently running out of physical memory and swapping which is hurting performance. Have read around on various of the message boards, and I suspect that the SHARED_BUFFERS setting on this server is set way to high, and that this in fact may be hurting performance. My current configuration settings are as follows:
shared_buffers = 393216 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 10000 # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 160001 # min 1000, fsm is free space map, ~6
bytes
sort_mem = 409600 # min 64, size in KB
vacuum_mem = 81920 # min 1024, size in KB
From what Ive read, Ive not seen anyone recommend a SHARED_BUFFERS setting higher than 50,000. Is a setting of 393216 going to cause significant problems, or does this sound about right on an 8GB system, bearing in mind that Id like to reserve at least a couple of GB for my application.
Also if you have any recommendations regarding effective_cache_size Id be interested as reading around this sounds important as well
Thanks
Mark
Mark Lonsdale wrote:
>
>
> Hi
>
>
>
> Please help. I have got a postgres 7.3.4 database running on RedHat ES
> 3, with 8GB of physical memory in it. The machine is shared with my
> application which is pretty intensive in doing selects and updates
> against the database, but there are usually no more than 10 connections
> to the database at any time.
>
>
> shared_buffers = 393216 # min max_connections*2 or 16, 8KB each
The above is likely hurting you more than helping you with 7.3.
>
> max_fsm_relations = 10000 # min 10, fsm is free space map, ~40
> bytes
>
> max_fsm_pages = 160001 # min 1000, fsm is free space map, ~6
>
> bytes
>
> sort_mem = 409600 # min 64, size in KB
The above will likely kill you :). Try 4096 or 8192, maybe 16384
depending on workload.
>
> vacuum_mem = 81920 # min 1024, size in KB
This is fine.
>
> Also if you have any recommendations regarding effective_cache_size Id
> be interested as reading around this sounds important as well
About 20-25% of available ram for 7.3.
The long and short is you need to upgrade to at least 7.4, preferrably 8.1.
Joshua D. Drake
>
>
>
> Thanks
>
>
>
> Mark
>
>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Hi Josh
Thanks for the feedback, that is most usefull. When you said one of the
settings was likely killing us, was it all of the settings for
max_fsm_relations, max_fsm_pages, and sort_mem or just the setting for
sort_mem ?
Can you explain why the setting would be killing me :-)
Thanks
Mark
-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: 25 October 2006 21:52
To: Mark Lonsdale
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Configuration Issue ?
Mark Lonsdale wrote:
>
>
> Hi
>
>
>
> Please help. I have got a postgres 7.3.4 database running on RedHat
ES
> 3, with 8GB of physical memory in it. The machine is shared with my
> application which is pretty intensive in doing selects and updates
> against the database, but there are usually no more than 10
connections
> to the database at any time.
>
>
> shared_buffers = 393216 # min max_connections*2 or 16, 8KB
each
The above is likely hurting you more than helping you with 7.3.
>
> max_fsm_relations = 10000 # min 10, fsm is free space map, ~40
> bytes
>
> max_fsm_pages = 160001 # min 1000, fsm is free space map, ~6
>
> bytes
>
> sort_mem = 409600 # min 64, size in KB
The above will likely kill you :). Try 4096 or 8192, maybe 16384
depending on workload.
>
> vacuum_mem = 81920 # min 1024, size in KB
This is fine.
>
> Also if you have any recommendations regarding effective_cache_size Id
> be interested as reading around this sounds important as well
About 20-25% of available ram for 7.3.
The long and short is you need to upgrade to at least 7.4, preferrably
8.1.
Joshua D. Drake
>
>
>
> Thanks
>
>
>
> Mark
>
>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Mark Lonsdale wrote: > > Hi Josh > > Thanks for the feedback, that is most usefull. When you said one of the > settings was likely killing us, was it all of the settings for > max_fsm_relations, max_fsm_pages, and sort_mem or just the setting for > sort_mem ? > > Can you explain why the setting would be killing me :-) The sort_mem is crucial. It's memory *per sort*, which means one query can use several times that amount. > The long and short is you need to upgrade to at least 7.4, preferrably > 8.1. Joshua means this too. Upgrade to 7.3.16 within the next few days, then test out something more recent. You should see some useful performance gains from 8.1. -- Richard Huxton Archonet Ltd
Richard Huxton wrote:
> Mark Lonsdale wrote:
>>
>> Hi Josh
>>
>> Thanks for the feedback, that is most usefull. When you said one of the
>> settings was likely killing us, was it all of the settings for
>> max_fsm_relations, max_fsm_pages, and sort_mem or just the setting for
>> sort_mem ?
>>
>> Can you explain why the setting would be killing me :-)
>
> The sort_mem is crucial. It's memory *per sort*, which means one query
> can use several times that amount.
Worse then that it is:
((sort memory) * (number of sorts)) * (number of connections) = amount
of ram possible to use.
Now... take the following query:
SELECT * FROM foo
JOIN bar on (bar.id = foo.id)
JOIN baz on (baz.id = foo_baz.id)
ORDER BY baz.name, foo.salary;
Over 5 million rows... How much ram you think you just used?
>
>> The long and short is you need to upgrade to at least 7.4, preferrably
>> 8.1.
>
> Joshua means this too. Upgrade to 7.3.16 within the next few days, then
> test out something more recent. You should see some useful performance
> gains from 8.1.
Right. The reason I suggested 7.4 is that he gets VACUUM VERBOSE in a
reasonable fashion but of course 8.1 is better.
Sincerely,
Joshua D. Drake
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Thanks guys, I think we'll certainly look to get the app certified with
7.4 and 8.x but that may take a little while. In the interim, Im
thinking of making the following changes then:-
Change Shared_buffers from 393216 to 80,000 ( ~15% of 4GB of RAM.
Server is 8GB but I want to leave space for App as well )
Set my effective_cache_size to 125,000 ( ~25% of 4GB of RAM )
Set my sort_mem to 8192
Do those numbers look a bit better? Will probably see if we can make
these changes asap as the server is struggling a bit now, which doesn't
really make sense given how much memory is in it.
Really appreciate your help and fast turnaround on this
Mark
-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: 25 October 2006 22:17
To: Richard Huxton
Cc: Mark Lonsdale; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Configuration Issue ?
Richard Huxton wrote:
> Mark Lonsdale wrote:
>>
>> Hi Josh
>>
>> Thanks for the feedback, that is most usefull. When you said one of
the
>> settings was likely killing us, was it all of the settings for
>> max_fsm_relations, max_fsm_pages, and sort_mem or just the setting
for
>> sort_mem ?
>>
>> Can you explain why the setting would be killing me :-)
>
> The sort_mem is crucial. It's memory *per sort*, which means one query
> can use several times that amount.
Worse then that it is:
((sort memory) * (number of sorts)) * (number of connections) = amount
of ram possible to use.
Now... take the following query:
SELECT * FROM foo
JOIN bar on (bar.id = foo.id)
JOIN baz on (baz.id = foo_baz.id)
ORDER BY baz.name, foo.salary;
Over 5 million rows... How much ram you think you just used?
>
>> The long and short is you need to upgrade to at least 7.4,
preferrably
>> 8.1.
>
> Joshua means this too. Upgrade to 7.3.16 within the next few days,
then
> test out something more recent. You should see some useful performance
> gains from 8.1.
Right. The reason I suggested 7.4 is that he gets VACUUM VERBOSE in a
reasonable fashion but of course 8.1 is better.
Sincerely,
Joshua D. Drake
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Mark Lonsdale wrote:
>
> Thanks guys, I think we'll certainly look to get the app certified with
> 7.4 and 8.x but that may take a little while. In the interim, Im
> thinking of making the following changes then:-
>
> Change Shared_buffers from 393216 to 80,000 ( ~15% of 4GB of RAM.
> Server is 8GB but I want to leave space for App as well )
You likely run into issues with anything over 16384. I have never seen a
benefit from shared_buffers over 12k or so with 7.3.
>
> Set my effective_cache_size to 125,000 ( ~25% of 4GB of RAM )
>
> Set my sort_mem to 8192
:)
Sincerely,
Joshua D. Drake
>
> Do those numbers look a bit better? Will probably see if we can make
> these changes asap as the server is struggling a bit now, which doesn't
> really make sense given how much memory is in it.
>
> Really appreciate your help and fast turnaround on this
>
> Mark
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: 25 October 2006 22:17
> To: Richard Huxton
> Cc: Mark Lonsdale; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Configuration Issue ?
>
> Richard Huxton wrote:
>> Mark Lonsdale wrote:
>>> Hi Josh
>>>
>>> Thanks for the feedback, that is most usefull. When you said one of
> the
>>> settings was likely killing us, was it all of the settings for
>>> max_fsm_relations, max_fsm_pages, and sort_mem or just the setting
> for
>>> sort_mem ?
>>>
>>> Can you explain why the setting would be killing me :-)
>> The sort_mem is crucial. It's memory *per sort*, which means one query
>> can use several times that amount.
>
> Worse then that it is:
>
> ((sort memory) * (number of sorts)) * (number of connections) = amount
> of ram possible to use.
>
> Now... take the following query:
>
> SELECT * FROM foo
> JOIN bar on (bar.id = foo.id)
> JOIN baz on (baz.id = foo_baz.id)
> ORDER BY baz.name, foo.salary;
>
> Over 5 million rows... How much ram you think you just used?
>
>>> The long and short is you need to upgrade to at least 7.4,
> preferrably
>>> 8.1.
>> Joshua means this too. Upgrade to 7.3.16 within the next few days,
> then
>> test out something more recent. You should see some useful performance
>> gains from 8.1.
>
> Right. The reason I suggested 7.4 is that he gets VACUUM VERBOSE in a
> reasonable fashion but of course 8.1 is better.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
On Wed, Oct 25, 2006 at 05:31:29PM -0400, Mark Lonsdale wrote: > Set my sort_mem to 8192 You really need to look at what your workload is before trying to tweak sort_mem. With 8G of memory, sort_mem=400000 (~400MB) with only 10 active connections might be a good setting. It's usually better to get a sort to fit into memory than spill to disk. Since you never mentioned what kind of workload you have or how many active connections there are, it's pretty much impossible to make a recommendation on that setting. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)