Thread: 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

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
>
>
>

"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

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
>
>

> 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

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

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
>
>