pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 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