pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedServer misconfiguration???
8+ messages / 4 participants
[nested] [flat]
* Server misconfiguration???
@ 2005-10-10 08:39 Andy <frum@ar-sd.net>
0 siblings, 2 replies; 8+ messages in thread
From: Andy @ 2005-10-10 08:39 UTC (permalink / raw)
To: pgsql-performance
Hi to all,
I have the following configuration:
Dual Xeon 2.8 Ghz, 1G RAM and postgre 8.0.3 installed.
Modified configuration parameters:
max_connections = 100
shared_buffers = 64000 # 500MB = 500 x 1024 x 1024 / (8 x 1024) (8KB)
work_mem = 51200 # 50MB = 50 x 1024 KB
maintenance_work_mem = 102400 # 50MB = 100 x 1024 KB
checkpoint_segments = 10
effective_cache_size = 25600 # 200MB = 50 x 1024 / 8
client_min_messages = notice
log_min_messages = notice
log_min_duration_statement = 2000
I get the feeling the server is somehow missconfigured or it does not work at full parameter. If I look at memory allocation, it never goes over 250MB whatever I do with the database. The kernel shmmax is set to 600MB. Database Size is around 550MB.
Need some advise.
Thanks.
Andy.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Server misconfiguration???
@ 2005-10-10 08:55 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Andy <frum@ar-sd.net>
1 sibling, 1 reply; 8+ messages in thread
From: Christopher Kings-Lynne @ 2005-10-10 08:55 UTC (permalink / raw)
To: Andy <frum@ar-sd.net>; +Cc: pgsql-performance
A lot of them are too large. Try:
Andy wrote:
> Hi to all,
>
> I have the following configuration:
> Dual Xeon 2.8 Ghz, 1G RAM and postgre 8.0.3 installed.
>
> Modified configuration parameters:
>
> max_connections = 100
>
> shared_buffers = 64000 # 500MB = 500 x 1024 x 1024 / (8 x 1024) (8KB)
shared_buffers = 10000
> work_mem = 51200 # 50MB = 50 x 1024 KB
work_mem = 4096
> maintenance_work_mem = 102400 # 50MB = 100 x 1024 KB
>
> checkpoint_segments = 10
>
> effective_cache_size = 25600 # 200MB = 50 x 1024 / 8
>
> client_min_messages = notice
> log_min_messages = notice
> log_min_duration_statement = 2000
>
>
>
> I get the feeling the server is somehow missconfigured or it does not
> work at full parameter. If I look at memory allocation, it never
> goes over 250MB whatever I do with the database. The kernel shmmax is
> set to 600MB. Database Size is around 550MB.
That's because you have work_mem set massively high. Remember that's
PER SORT. If you have 10 queries running each doing 3 sorts that's 30x
the work_mem right there.
Chris
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Server misconfiguration???
@ 2005-10-10 09:42 Andy <frum@ar-sd.net>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 8+ messages in thread
From: Andy @ 2005-10-10 09:42 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-performance
Yes you're right it really bosst a little.
I want to improve the system performance. Are there any more tipps?
On this server runs only a webserver with php application which uses postgre
Db. Should I give more memory to postgre? From what I noticed this is the
most memory "needing" service from this system.
Andy.
----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "Andy" <frum@ar-sd.net>
Cc: <pgsql-performance@postgresql.org>
Sent: Monday, October 10, 2005 11:55 AM
Subject: Re: [PERFORM] Server misconfiguration???
>A lot of them are too large. Try:
>
> Andy wrote:
>> Hi to all,
>> I have the following configuration:
>> Dual Xeon 2.8 Ghz, 1G RAM and postgre 8.0.3 installed.
>> Modified configuration parameters:
>> max_connections = 100
>> shared_buffers = 64000 # 500MB = 500 x 1024 x 1024 / (8 x 1024) (8KB)
>
> shared_buffers = 10000
>
>> work_mem = 51200 # 50MB = 50 x 1024 KB
>
> work_mem = 4096
>
>> maintenance_work_mem = 102400 # 50MB = 100 x 1024 KB
>> checkpoint_segments = 10
>> effective_cache_size = 25600 # 200MB = 50 x 1024 / 8
>> client_min_messages = notice log_min_messages = notice
>> log_min_duration_statement = 2000
>> I get the feeling the server is somehow missconfigured or it does not
>> work at full parameter. If I look at memory allocation, it never goes
>> over 250MB whatever I do with the database. The kernel shmmax is set to
>> 600MB. Database Size is around 550MB.
>
> That's because you have work_mem set massively high. Remember that's PER
> SORT. If you have 10 queries running each doing 3 sorts that's 30x the
> work_mem right there.
>
> Chris
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Server misconfiguration???
@ 2005-10-10 14:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andy <frum@ar-sd.net>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2005-10-10 14:18 UTC (permalink / raw)
To: Andy <frum@ar-sd.net>; +Cc: pgsql-performance
"Andy" <frum@ar-sd.net> writes:
> I get the feeling the server is somehow missconfigured or it does not
> work at full parameter. If I look at memory allocation, it never goes
> over 250MB whatever I do with the database.
That is not wrong. Postgres expects the kernel to be doing disk
caching, so the amount of memory that's effectively being used for
database work includes not only what is shown as belonging to the
PG processes, but some portion of the kernel disk buffers as well.
You don't really *want* the processes eating all of available RAM.
I concur with Chris K-L's comments that you should reduce rather
than increase your settings.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Server misconfiguration???
@ 2005-10-10 14:31 Andy <frum@ar-sd.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Andy @ 2005-10-10 14:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
When I ment memory allocation, I look with htop to see the process list, CPU
load, memory, swap. So I didn't ment the a postgre process uses that amount
of memory.
I read some tuning things, I made the things that are written there, but I
think that there improvements can be made.
regards,
Andy.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andy" <frum@ar-sd.net>
Cc: <pgsql-performance@postgresql.org>
Sent: Monday, October 10, 2005 5:18 PM
Subject: Re: [PERFORM] Server misconfiguration???
> "Andy" <frum@ar-sd.net> writes:
>> I get the feeling the server is somehow missconfigured or it does not
>> work at full parameter. If I look at memory allocation, it never goes
>> over 250MB whatever I do with the database.
>
> That is not wrong. Postgres expects the kernel to be doing disk
> caching, so the amount of memory that's effectively being used for
> database work includes not only what is shown as belonging to the
> PG processes, but some portion of the kernel disk buffers as well.
> You don't really *want* the processes eating all of available RAM.
>
> I concur with Chris K-L's comments that you should reduce rather
> than increase your settings.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Server misconfiguration???
@ 2005-10-10 14:56 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Andy <frum@ar-sd.net>
0 siblings, 0 replies; 8+ messages in thread
From: Christopher Kings-Lynne @ 2005-10-10 14:56 UTC (permalink / raw)
To: Andy <frum@ar-sd.net>; +Cc: pgsql-performance
> Yes you're right it really bosst a little.
> I want to improve the system performance. Are there any more tipps?
The rest of the numbers look vaguely ok...
> On this server runs only a webserver with php application which uses
> postgre Db. Should I give more memory to postgre? From what I noticed
> this is the most memory "needing" service from this system.
The best thing you can do is use two servers so that pgsql does not
compete with web server for RAM... Personally I'd start looking at my
queries themselves next, see where I could optimise them.
Chris
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Server misconfiguration???
@ 2005-10-13 15:01 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Andy <frum@ar-sd.net>
0 siblings, 1 reply; 8+ messages in thread
From: Andrew Sullivan @ 2005-10-13 15:01 UTC (permalink / raw)
To: pgsql-performance
On Mon, Oct 10, 2005 at 05:31:10PM +0300, Andy wrote:
> I read some tuning things, I made the things that are written there, but I
> think that there improvements can be made.
Have you tried the suggestions people made? Because if I were you,
I'd be listing very carefully to what Chris and Tom were telling me
about how to tune my database.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Server misconfiguration???
@ 2005-10-14 07:23 Andy <frum@ar-sd.net>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 8+ messages in thread
From: Andy @ 2005-10-14 07:23 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-performance
Yes I did, and it works better(on a test server). I had no time to put it in
production.
I will try to do small steps to see what happens.
Regards,
Andy.
----- Original Message -----
From: "Andrew Sullivan" <ajs@crankycanuck.ca>
To: <pgsql-performance@postgresql.org>
Sent: Thursday, October 13, 2005 6:01 PM
Subject: Re: [PERFORM] Server misconfiguration???
> On Mon, Oct 10, 2005 at 05:31:10PM +0300, Andy wrote:
>> I read some tuning things, I made the things that are written there, but
>> I
>> think that there improvements can be made.
>
> Have you tried the suggestions people made? Because if I were you,
> I'd be listing very carefully to what Chris and Tom were telling me
> about how to tune my database.
>
> A
>
> --
> Andrew Sullivan | ajs@crankycanuck.ca
> In the future this spectacle of the middle classes shocking the avant-
> garde will probably become the textbook definition of Postmodernism.
> --Brad Holland
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-10-14 07:23 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-10 08:39 Server misconfiguration??? Andy <frum@ar-sd.net>
2005-10-10 08:55 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-10-10 09:42 ` Andy <frum@ar-sd.net>
2005-10-10 14:56 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-10-10 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-10 14:31 ` Andy <frum@ar-sd.net>
2005-10-13 15:01 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-10-14 07:23 ` Andy <frum@ar-sd.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