pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedConfiguration Issue ?
8+ messages / 4 participants
[nested] [flat]
* Configuration Issue ?
@ 2006-10-25 20:47 Mark Lonsdale <mark.lonsdale@wysdm.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mark Lonsdale @ 2006-10-25 20:47 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Configuration Issue ?
@ 2006-10-25 20:51 Joshua D. Drake <jd@commandprompt.com>
parent: Mark Lonsdale <mark.lonsdale@wysdm.com>
0 siblings, 0 replies; 8+ messages in thread
From: Joshua D. Drake @ 2006-10-25 20:51 UTC (permalink / raw)
To: Mark Lonsdale <mark.lonsdale@wysdm.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Configuration Issue ?
@ 2006-10-25 20:57 Mark Lonsdale <mark.lonsdale@wysdm.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mark Lonsdale @ 2006-10-25 20:57 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Configuration Issue ?
@ 2006-10-25 21:12 Richard Huxton <dev@archonet.com>
parent: Mark Lonsdale <mark.lonsdale@wysdm.com>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2006-10-25 21:12 UTC (permalink / raw)
To: Mark Lonsdale <mark.lonsdale@wysdm.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Configuration Issue ?
@ 2006-10-25 21:16 Joshua D. Drake <jd@commandprompt.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Joshua D. Drake @ 2006-10-25 21:16 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Mark Lonsdale <mark.lonsdale@wysdm.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Configuration Issue ?
@ 2006-10-25 21:31 Mark Lonsdale <mark.lonsdale@wysdm.com>
0 siblings, 2 replies; 8+ messages in thread
From: Mark Lonsdale @ 2006-10-25 21:31 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Configuration Issue ?
@ 2006-10-25 21:42 Joshua D. Drake <jd@commandprompt.com>
parent: Mark Lonsdale <mark.lonsdale@wysdm.com>
1 sibling, 0 replies; 8+ messages in thread
From: Joshua D. Drake @ 2006-10-25 21:42 UTC (permalink / raw)
To: Mark Lonsdale <mark.lonsdale@wysdm.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Configuration Issue ?
@ 2006-10-26 19:08 Jim C. Nasby <jim@nasby.net>
parent: Mark Lonsdale <mark.lonsdale@wysdm.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-10-26 19:08 UTC (permalink / raw)
To: Mark Lonsdale <mark.lonsdale@wysdm.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Richard Huxton <dev@archonet.com>; pgsql-performance
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-10-26 19:08 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-25 20:47 Configuration Issue ? Mark Lonsdale <mark.lonsdale@wysdm.com>
2006-10-25 20:51 ` Joshua D. Drake <jd@commandprompt.com>
2006-10-25 20:57 Re: Configuration Issue ? Mark Lonsdale <mark.lonsdale@wysdm.com>
2006-10-25 21:12 ` Richard Huxton <dev@archonet.com>
2006-10-25 21:16 ` Joshua D. Drake <jd@commandprompt.com>
2006-10-25 21:31 Re: Configuration Issue ? Mark Lonsdale <mark.lonsdale@wysdm.com>
2006-10-25 21:42 ` Joshua D. Drake <jd@commandprompt.com>
2006-10-26 19:08 ` Jim C. Nasby <jim@nasby.net>
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