pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Optimize
8+ messages / 5 participants
[nested] [flat]
* Optimize
@ 2003-11-24 20:48 Torsten Schulz <comic@be-a-part.de>
2003-11-24 20:20 ` Re: Optimize Gaetano Mendola <mendola@bigfoot.com>
2003-11-25 02:24 ` Re: Optimize Neil Conway <neilc@samurai.com>
0 siblings, 2 replies; 8+ messages in thread
From: Torsten Schulz @ 2003-11-24 20:48 UTC (permalink / raw)
To: pgsql-performance
Yes, I know: very difficult question, but I don't know what to do now.
Our Server:
Dual-CPU with 1.2 GHz
1.5 GB RAM
Our Problem: We are a Community. Between 19 and 21 o clock we have >350
User in the Community. But then, the Database are very slow. And we have
per CPU ~20-30% idle-time.
Has anyone an idea what's the best configuration for thta server?
Many Greetings
T. Schulz (with very bad english, i know)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimize
2003-11-24 20:48 Optimize Torsten Schulz <comic@be-a-part.de>
@ 2003-11-24 20:20 ` Gaetano Mendola <mendola@bigfoot.com>
2003-11-24 21:33 ` Re: Optimize Torsten Schulz <comic@be-a-part.de>
1 sibling, 1 reply; 8+ messages in thread
From: Gaetano Mendola @ 2003-11-24 20:20 UTC (permalink / raw)
To: pgsql-performance; +Cc: Torsten Schulz <comic@be-a-part.de>
Torsten Schulz wrote:
> Yes, I know: very difficult question, but I don't know what to do now.
>
> Our Server:
> Dual-CPU with 1.2 GHz
> 1.5 GB RAM
>
> Our Problem: We are a Community. Between 19 and 21 o clock we have >350
> User in the Community. But then, the Database are very slow. And we have
> per CPU ~20-30% idle-time.
May we know the postgres version that you are running and
see the query that run slow ?
Is also usefull take a look at your postgresql configuration.
You can see doing select * from pg_stat_activity the
queries that are currently running on your server, and
do a explain analize on it to see which one is the
bottleneck. If you are running the 7.4 you can see on
the log the total ammount for each query.
Let us know.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimize
2003-11-24 20:48 Optimize Torsten Schulz <comic@be-a-part.de>
2003-11-24 20:20 ` Re: Optimize Gaetano Mendola <mendola@bigfoot.com>
@ 2003-11-24 21:33 ` Torsten Schulz <comic@be-a-part.de>
2003-11-24 20:56 ` Re: Optimize Gaetano Mendola <mendola@bigfoot.com>
2003-11-24 21:45 ` Re: Optimize Dennis Bjorklund <db@zigo.dhs.org>
0 siblings, 2 replies; 8+ messages in thread
From: Torsten Schulz @ 2003-11-24 21:33 UTC (permalink / raw)
To: pgsql-performance
Gaetano Mendola wrote:
> Torsten Schulz wrote:
>
>> Yes, I know: very difficult question, but I don't know what to do now.
>>
>> Our Server:
>> Dual-CPU with 1.2 GHz
>> 1.5 GB RAM
>>
>> Our Problem: We are a Community. Between 19 and 21 o clock we have
>> >350 User in the Community. But then, the Database are very slow. And
>> we have per CPU ~20-30% idle-time.
>
>
> May we know the postgres version that you are running and
> see the query that run slow ?
Postgres: 7.3.2
Query: All queries
Configuration:
max_connections = 1000 # Must be, if lower then 500 we become
connection-errors
shared_buffers = 5000 # 2*max_connections, min 16
max_fsm_relations = 1000 # min 10, fsm is free space map
max_fsm_pages = 2000000 # min 1000, fsm is free space map
max_locks_per_transaction = 64 # min 10
wal_buffers = 2000 # min 4
sort_mem = 32768 # min 32
vacuum_mem = 32768 # min 1024
fsync = false
enable_seqscan = true
enable_indexscan = true
enable_tidscan = true
enable_sort = true
enable_nestloop = true
enable_mergejoin = true
enable_hashjoin = true
effective_cache_size = 96000 # default in 8k pages
That are all uncommented lines. I've found the values in internet and
had tested it. But in performance are no difference between old
configuration an this.
> Is also usefull take a look at your postgresql configuration.
> You can see doing select * from pg_stat_activity the
> queries that are currently running on your server, and
> do a explain analize on it to see which one is the
> bottleneck. If you are running the 7.4 you can see on
> the log the total ammount for each query.
>
I'll show tomorrow for this, today it is too late, the performance is
now perfect. It's only slow on this 2 hours with so many users on server.
Oh, and i can't update to 7.4. The Chat don't run with libraries of 7.4
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimize
2003-11-24 20:48 Optimize Torsten Schulz <comic@be-a-part.de>
2003-11-24 20:20 ` Re: Optimize Gaetano Mendola <mendola@bigfoot.com>
2003-11-24 21:33 ` Re: Optimize Torsten Schulz <comic@be-a-part.de>
@ 2003-11-24 20:56 ` Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 0 replies; 8+ messages in thread
From: Gaetano Mendola @ 2003-11-24 20:56 UTC (permalink / raw)
To: pgsql-performance; +Cc: Torsten Schulz <comic@be-a-part.de>
Torsten Schulz wrote:
> Gaetano Mendola wrote:
>
>> Torsten Schulz wrote:
>>
>>> Yes, I know: very difficult question, but I don't know what to do now.
>>>
>>> Our Server:
>>> Dual-CPU with 1.2 GHz
>>> 1.5 GB RAM
>>>
>>> Our Problem: We are a Community. Between 19 and 21 o clock we have
>>> >350 User in the Community. But then, the Database are very slow. And
>>> we have per CPU ~20-30% idle-time.
>>
>>
>>
>> May we know the postgres version that you are running and
>> see the query that run slow ?
>
>
> Postgres: 7.3.2
> Query: All queries
>
> Configuration:
> max_connections = 1000 # Must be, if lower then 500 we become
> connection-errors
> shared_buffers = 5000 # 2*max_connections, min 16
> max_fsm_relations = 1000 # min 10, fsm is free space map
> max_fsm_pages = 2000000 # min 1000, fsm is free space map
> max_locks_per_transaction = 64 # min 10
> wal_buffers = 2000 # min 4
>
> sort_mem = 32768 # min 32
> vacuum_mem = 32768 # min 1024
>
> fsync = false
>
> enable_seqscan = true
> enable_indexscan = true
> enable_tidscan = true
> enable_sort = true
> enable_nestloop = true
> enable_mergejoin = true
> enable_hashjoin = true
>
> effective_cache_size = 96000 # default in 8k pages
With 500 connection at the sime time 32MB for sort_mem can be too much.
What say "iostat 1" and "vmstat 1" ?
Try also to reduce this costs:
random_page_cost = 2.5
cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.0005
BTW take a query and show us the result of explain analyze.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimize
2003-11-24 20:48 Optimize Torsten Schulz <comic@be-a-part.de>
2003-11-24 20:20 ` Re: Optimize Gaetano Mendola <mendola@bigfoot.com>
2003-11-24 21:33 ` Re: Optimize Torsten Schulz <comic@be-a-part.de>
@ 2003-11-24 21:45 ` Dennis Bjorklund <db@zigo.dhs.org>
1 sibling, 0 replies; 8+ messages in thread
From: Dennis Bjorklund @ 2003-11-24 21:45 UTC (permalink / raw)
To: Torsten Schulz <comic@be-a-part.de>; +Cc: pgsql-performance
On Mon, 24 Nov 2003, Torsten Schulz wrote:
> sort_mem = 32768 # min 32
32 meg per sort can be a lot in total if you have many clients sorting
things. I assume you have checked so that the computer is not pushed into
swapping when you have the peak with lots of users. A swapping computer is
never fast.
Using some swap space is not bad, but a lot of page in and page out to the
swap is not good.
--
/Dennis
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimize
2003-11-24 20:48 Optimize Torsten Schulz <comic@be-a-part.de>
@ 2003-11-25 02:24 ` Neil Conway <neilc@samurai.com>
1 sibling, 0 replies; 8+ messages in thread
From: Neil Conway @ 2003-11-25 02:24 UTC (permalink / raw)
To: Torsten Schulz <comic@be-a-part.de>; +Cc: pgsql-performance
Torsten Schulz <comic@be-a-part.de> writes:
> Our Server:
> Dual-CPU with 1.2 GHz
> 1.5 GB RAM
What kind of I/O subsystem is in this machine? This is an x86 machine,
right?
> Has anyone an idea what's the best configuration for thta server?
It is difficult to say until you provide some information on the
system's state during periods of heavy traffic.
BTW, in addition to the machine's hardware configuration, have you
looked at tuning the queries running on PostgreSQL? What about the OS
kernel?
-Neil
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimize
@ 2003-11-25 22:07 Torsten Schulz <comic@be-a-part.de>
2003-11-26 06:06 ` Re: Optimize Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 1 reply; 8+ messages in thread
From: Torsten Schulz @ 2003-11-25 22:07 UTC (permalink / raw)
To: pgsql-performance
Chester Kustarz wrote:
> On Mon, 24 Nov 2003, Torsten Schulz wrote:
>
>
>> shared_buffers = 5000 # 2*max_connections, min 16
>>
>
>
> that looks pretty small. that would only be 40MBytes (8k/page *
> 5000pages).
>
> http://www.varlena.com/GeneralBits/Tidbits/perf.html
>
>
>
Ok, thats it. I've set it to 51200, now it seems to be very fast.
Thank you!
-------- Original Message --------
Subject: Re: [PERFORM] Optimize
Date: Tue, 25 Nov 2003 23:04:06 +0100
From: Torsten Schulz <comic@be-a-part.de>
To: Chester Kustarz <chester@arbor.net>
References: <Pine.BSO.4.44.0311241718030.19584-100000@detroit.arbor.net>
Chester Kustarz wrote:
>On Mon, 24 Nov 2003, Torsten Schulz wrote:
>
>
>>shared_buffers = 5000 # 2*max_connections, min 16
>>
>>
>
>that looks pretty small. that would only be 40MBytes (8k/page * 5000pages).
>
>http://www.varlena.com/GeneralBits/Tidbits/perf.html
>
>
>
Ok, thats it. I've set it to 51200, now it seems to be very fast.
Thank you!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimize
2003-11-25 22:07 Re: Optimize Torsten Schulz <comic@be-a-part.de>
@ 2003-11-26 06:06 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 0 replies; 8+ messages in thread
From: Shridhar Daithankar @ 2003-11-26 06:06 UTC (permalink / raw)
To: Torsten Schulz <comic@be-a-part.de>; +Cc: pgsql-performance
Torsten Schulz wrote:
> Chester Kustarz wrote:
>> On Mon, 24 Nov 2003, Torsten Schulz wrote:
>>> shared_buffers = 5000 # 2*max_connections, min 16
>> that looks pretty small. that would only be 40MBytes (8k/page *
>> 5000pages).
>> http://www.varlena.com/GeneralBits/Tidbits/perf.html
> Ok, thats it. I've set it to 51200, now it seems to be very fast.
Whoa..That is too much. You acn get still better performance at something low
like 10,000 or even 5000.
Bumping up shared buffers stops being useful after a point and later it actually
degrades the performance..
Shridhar
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-11-26 06:06 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-24 20:48 Optimize Torsten Schulz <comic@be-a-part.de>
2003-11-24 20:20 ` Gaetano Mendola <mendola@bigfoot.com>
2003-11-24 21:33 ` Torsten Schulz <comic@be-a-part.de>
2003-11-24 20:56 ` Gaetano Mendola <mendola@bigfoot.com>
2003-11-24 21:45 ` Dennis Bjorklund <db@zigo.dhs.org>
2003-11-25 02:24 ` Neil Conway <neilc@samurai.com>
2003-11-25 22:07 Re: Optimize Torsten Schulz <comic@be-a-part.de>
2003-11-26 06:06 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
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