pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Best tweak for fast results.. ?
5+ messages / 5 participants
[nested] [flat]

* Best tweak for fast results.. ?
@ 2003-08-26 13:42 JM <jerome@gmanmi.tv>
  2003-08-26 17:11 ` Re: [PERFORM] Best tweak for fast results.. ? Ron Johnson <ron.l.johnson@cox.net>
  2003-08-26 17:44 ` Re: Best tweak for fast results.. ? Richard Huxton <dev@archonet.com>
  2003-08-26 22:10 ` Re: Best tweak for fast results.. ? scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 3 replies; 5+ messages in thread

From: JM @ 2003-08-26 13:42 UTC (permalink / raw)
  To: pgsql-performance

need input on parameter values on confs...

our database is getting 1000 transactions/sec on peak periods..

sitting on RH 7.3 
2.4.7-10smp
RAM: 1028400
SWAP: 2040244

queries are just simple select statements based on timestamps, varchars...
less on joins... on a 300K rows..


TIA



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [PERFORM] Best tweak for fast results.. ?
  2003-08-26 13:42 Best tweak for fast results.. ? JM <jerome@gmanmi.tv>
@ 2003-08-26 17:11 ` Ron Johnson <ron.l.johnson@cox.net>
  2003-08-26 17:25   ` Re: [PERFORM] Best tweak for fast results.. ? Dennis Gearon <gearond@fireserve.net>
  2 siblings, 1 reply; 5+ messages in thread

From: Ron Johnson @ 2003-08-26 17:11 UTC (permalink / raw)
  To: PgSQL General ML <pgsql-general@postgresql.org>

On Tue, 2003-08-26 at 08:42, JM wrote:
> need input on parameter values on confs...
> 
> our database is getting 1000 transactions/sec on peak periods..
> 
> sitting on RH 7.3 
> 2.4.7-10smp
> RAM: 1028400
> SWAP: 2040244
> 
> queries are just simple select statements based on timestamps, varchars...
> less on joins... on a 300K rows..

Could it be that 1000tps is as good as your h/w can do?  You didn't
mention what kind and speed of CPU(s), SCSI-or-IDE controller(s) and
type/speed of disk(s) you have.

-- 
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Oh, great altar of passive entertainment, bestow upon me thy 
discordant images at such speed as to render linear thought impossible"
Calvin, regarding TV




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [PERFORM] Best tweak for fast results.. ?
  2003-08-26 13:42 Best tweak for fast results.. ? JM <jerome@gmanmi.tv>
  2003-08-26 17:11 ` Re: [PERFORM] Best tweak for fast results.. ? Ron Johnson <ron.l.johnson@cox.net>
@ 2003-08-26 17:25   ` Dennis Gearon <gearond@fireserve.net>
  0 siblings, 0 replies; 5+ messages in thread

From: Dennis Gearon @ 2003-08-26 17:25 UTC (permalink / raw)
  To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>

actually, isin't 1000 tps pretty good?

Ron Johnson wrote:

>On Tue, 2003-08-26 at 08:42, JM wrote:
>  
>
>>need input on parameter values on confs...
>>
>>our database is getting 1000 transactions/sec on peak periods..
>>
>>sitting on RH 7.3 
>>2.4.7-10smp
>>RAM: 1028400
>>SWAP: 2040244
>>
>>queries are just simple select statements based on timestamps, varchars...
>>less on joins... on a 300K rows..
>>    
>>
>
>Could it be that 1000tps is as good as your h/w can do?  You didn't
>mention what kind and speed of CPU(s), SCSI-or-IDE controller(s) and
>type/speed of disk(s) you have.
>
>  
>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Best tweak for fast results.. ?
  2003-08-26 13:42 Best tweak for fast results.. ? JM <jerome@gmanmi.tv>
@ 2003-08-26 17:44 ` Richard Huxton <dev@archonet.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Richard Huxton @ 2003-08-26 17:44 UTC (permalink / raw)
  To: pgsql-performance

On Tuesday 26 August 2003 14:42, JM wrote:
> need input on parameter values on confs...
>
> our database is getting 1000 transactions/sec on peak periods..
>
> sitting on RH 7.3
> 2.4.7-10smp
> RAM: 1028400
> SWAP: 2040244
>
> queries are just simple select statements based on timestamps, varchars...
> less on joins... on a 300K rows..

Assuming you're getting good query plans (check the output of EXPLAIN 
ANALYSE)...

Start by checking the output of vmstat/iostat during busy periods - this will 
tell you whether CPU/IO/RAM is the bottleneck.

There is a good starter for tuning PG at:
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

Assuming your rows aren't too wide, they're probably mostly cached by Linux, 
so you probably don't want to overdo the shared buffers/sort memory and make 
sure the effective cache size is accurate.
-- 
  Richard Huxton
  Archonet Ltd



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Best tweak for fast results.. ?
  2003-08-26 13:42 Best tweak for fast results.. ? JM <jerome@gmanmi.tv>
@ 2003-08-26 22:10 ` scott.marlowe <scott.marlowe@ihs.com>
  2 siblings, 0 replies; 5+ messages in thread

From: scott.marlowe @ 2003-08-26 22:10 UTC (permalink / raw)
  To: JM <jerome@gmanmi.tv>; +Cc: pgsql-performance

On Tue, 26 Aug 2003, JM wrote:

> need input on parameter values on confs...
> 
> our database is getting 1000 transactions/sec on peak periods..
> 
> sitting on RH 7.3 
> 2.4.7-10smp
> RAM: 1028400
> SWAP: 2040244

1:  Upgrade your kernel.  2.4.7 on RH3 was updated to 2.4.18-24 in March, 
and the 2.4.18 kernel is MUCH faster and has many bugs squashed.

2:  Upgrade to the latest stable version of postgresql, 7.3.4

3:  Make sure your kernels file-nr settings, and shm settings are big 
enough to handle load.  

4:  Edit the $PGDATA/postgresql.conf file to reflect all that extra cache 
you've got etc....  

shared_buffers = 5000
sort_mem = 16384
effective_cache_size = (size of cache/buffer mem divided by 8192)

5:  Look at moving WAL to it's own spindle(s), as it is often the choke 
point when doing lots of transactions.

6:  Look at using more drives in a RAID 1+0 array for the data (as well as 
a seperate one for WAL if you can afford it.)

7:  Make sure your drives are mounted noatime.

8:  If you don't mind living dangerously, or the data can be reproduced 
from source files (i.e. catastrophic failure of your data set won't set 
you back) look at both mounting the drives async (the default for linux, 
slightly dangerous) and turning fsync off (quite dangerous, in case of 
crashed hardware / OS, you very well might lose data.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-08-26 22:10 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-26 13:42 Best tweak for fast results.. ? JM <jerome@gmanmi.tv>
2003-08-26 17:11 ` Ron Johnson <ron.l.johnson@cox.net>
2003-08-26 17:25   ` Dennis Gearon <gearond@fireserve.net>
2003-08-26 17:44 ` Richard Huxton <dev@archonet.com>
2003-08-26 22:10 ` scott.marlowe <scott.marlowe@ihs.com>

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