pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Database Server Tuning
10+ messages / 5 participants
[nested] [flat]

* Database Server Tuning
@ 2004-02-26 21:28  John Allgood <john@turbocorp.com>
  0 siblings, 3 replies; 10+ messages in thread

From: John Allgood @ 2004-02-26 21:28 UTC (permalink / raw)
  To: pgsql-performance

I sent this to the admin list the other day and got no responses. Maybe this
list can give me some pointers.

Hello

	I am working on installing and configuring a Postgres database
server. I am running Redhat Enterprise ES 3.0 and Redhat Database 3.0.
"Postgres version 7.3.4-11". This server will host 150-200 users. There will
be about 9 databases in our cluster ranging anywhere from 500MB to 3GB The
hardware is a dual Xeon running at 2.8GHZ, 4GB RAM, Ultra 320 SCSI hard
drives running on Adaptec Ultra Raid Controllers. 
	I am planning on separating the OS, Data, WAL on to separate drives
which will be mirrored. I am looking for input on setting kernel parameters,
and Postgres server runtime parameters and other settings relating to
tuning. Also is there any benchmarking tools available that will help me
tune this server.


Thanks

John Allgood - ESC
System Administrator
770.535.5049





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

* Re: Database Server Tuning
@ 2004-02-26 22:01  Troels Arvin <troels@arvin.dk>
  parent: John Allgood <john@turbocorp.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Troels Arvin @ 2004-02-26 22:01 UTC (permalink / raw)
  To: pgsql-performance

On Thu, 26 Feb 2004 16:28:07 -0500, John Allgood wrote:

> 	I am planning on separating the OS, Data, WAL on to separate drives
> which will be mirrored.

Have you considered RAID-10 in stead of RAID-1?

> I am looking for input on setting kernel
> parameters, and Postgres server runtime parameters and other settings
> relating to tuning.

http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
(See the "Performance" section.)

-- 
Greetings from Troels Arvin, Copenhagen, Denmark





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

* Re: Database Server Tuning
@ 2004-02-26 22:48  Josh Berkus <josh@agliodbs.com>
  parent: John Allgood <john@turbocorp.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Josh Berkus @ 2004-02-26 22:48 UTC (permalink / raw)
  To: John Allgood <john@turbocorp.com>; pgsql-performance

John,

> and Postgres server runtime parameters and other settings relating to
> tuning. Also is there any benchmarking tools available that will help me
> tune this server.

Check out 
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

Also, I'd like to see what you get under heavy load for context-switching.   
We've been having issues with RH+Xeon with really large queries.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: Database Server Tuning
@ 2004-02-27 03:07  John Allgood <john@turbocorp.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 10+ messages in thread

From: John Allgood @ 2004-02-27 03:07 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus wrote:

>John,
>
>  
>
>>and Postgres server runtime parameters and other settings relating to
>>tuning. Also is there any benchmarking tools available that will help me
>>tune this server.
>>    
>>
>
>Check out 
>http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
>
>Also, I'd like to see what you get under heavy load for context-switching.   
>We've been having issues with RH+Xeon with really large queries.
>
>  
>
This is exactly what I was looking for.  I will keep you posted on what 
kinda results I get when I start putting a load on this server.

Thanks
John Allgood - ESC
Systems Administrator
770.535.5049



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

* Re: Database Server Tuning
@ 2004-03-02 20:51  Vivek Khera <khera@kcilink.com>
  parent: John Allgood <john@turbocorp.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Vivek Khera @ 2004-03-02 20:51 UTC (permalink / raw)
  To: pgsql-performance

>>>>> "JA" == John Allgood <john@turbocorp.com> writes:

JA> 	I am planning on separating the OS, Data, WAL on to separate drives
JA> which will be mirrored. I am looking for input on setting kernel parameters,
JA> and Postgres server runtime parameters and other settings relating to

I did a bunch of testing with different RAID levels on a 14 disk
array.  I finally settled on this:  RAID5 across 14 disks for the
data, the OS (including syslog directory) and WAL on a RAID1 pair on
the other channel of the same controller (I didn't want to spring for
dual RAID controllers).  The biggest bumps in performance came from
increasing the checkpoint_buffers since my DB is heavily written to,
and increasing sort_mem.


-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/



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

* Re: Database Server Tuning
@ 2004-03-02 21:27  Josh Berkus <josh@agliodbs.com>
  parent: Vivek Khera <khera@kcilink.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Josh Berkus @ 2004-03-02 21:27 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; pgsql-performance

Vivek,

> I did a bunch of testing with different RAID levels on a 14 disk
> array.  I finally settled on this:  RAID5 across 14 disks for the
> data, the OS (including syslog directory) and WAL on a RAID1 pair on
> the other channel of the same controller (I didn't want to spring for
> dual RAID controllers).  The biggest bumps in performance came from
> increasing the checkpoint_buffers since my DB is heavily written to,
> and increasing sort_mem.

With large RAID, have you found that having WAL on a seperate array actually 
boosts performance?   The empirical tests we've seen so far don't seem to 
support this.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: Database Server Tuning
@ 2004-03-02 21:32  Vivek Khera <khera@kcilink.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Vivek Khera @ 2004-03-02 21:32 UTC (permalink / raw)
  To: pgsql-performance


On Mar 2, 2004, at 4:27 PM, Josh Berkus wrote:

> Vivek,
>
>> I did a bunch of testing with different RAID levels on a 14 disk
>> array.  I finally settled on this:  RAID5 across 14 disks for the
>> data, the OS (including syslog directory) and WAL on a RAID1 pair on
>> the other channel of the same controller (I didn't want to spring for
>


> With large RAID, have you found that having WAL on a seperate array 
> actually
> boosts performance?   The empirical tests we've seen so far don't seem 
> to
> support this.

Yes, it was a noticeable improvement.




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

* Re: Database Server Tuning
@ 2004-03-03 18:59  Josh Berkus <josh@agliodbs.com>
  parent: Vivek Khera <khera@kcilink.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Josh Berkus @ 2004-03-03 18:59 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; pgsql-performance

Vivek,

> > With large RAID, have you found that having WAL on a seperate array 
> > actually
> > boosts performance?   The empirical tests we've seen so far don't seem 
> > to
> > support this.
> 
> Yes, it was a noticeable improvement.

Do you have any stats?   This would be useful for your talk, as well.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: Database Server Tuning
@ 2004-06-10 16:02  Anjan Dave <adave@vantage.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Anjan Dave @ 2004-06-10 16:02 UTC (permalink / raw)
  To: josh@agliodbs.com; Vivek Khera <khera@kcilink.com>; pgsql-performance

Vivek,
 
Was there anything specific that helped you decide on a RAID-5 and not a RAID-10?
 
I have my DBs on RAID10, and would soon be moving them on FC drives, and i am considering RAID-10.
 
Thanks,
Anjan

	-----Original Message----- 
	From: Josh Berkus [mailto:josh@agliodbs.com] 
	Sent: Tue 3/2/2004 4:27 PM 
	To: Vivek Khera; pgsql-performance@postgresql.org 
	Cc: 
	Subject: Re: [PERFORM] Database Server Tuning
	
	

	Vivek,
	
	> I did a bunch of testing with different RAID levels on a 14 disk
	> array.  I finally settled on this:  RAID5 across 14 disks for the
	> data, the OS (including syslog directory) and WAL on a RAID1 pair on
	> the other channel of the same controller (I didn't want to spring for
	> dual RAID controllers).  The biggest bumps in performance came from
	> increasing the checkpoint_buffers since my DB is heavily written to,
	> and increasing sort_mem.
	
	With large RAID, have you found that having WAL on a seperate array actually
	boosts performance?   The empirical tests we've seen so far don't seem to
	support this.
	
	--
	-Josh Berkus
	 Aglio Database Solutions
	 San Francisco
	
	
	---------------------------(end of broadcast)---------------------------
	TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
	



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

* Re: Database Server Tuning
@ 2004-06-10 16:51  Vivek Khera <khera@kcilink.com>
  parent: Anjan Dave <adave@vantage.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Vivek Khera @ 2004-06-10 16:51 UTC (permalink / raw)
  To: Anjan Dave <adave@vantage.com>; +Cc: pgsql-performance; pgsql-performance


On Jun 10, 2004, at 12:02 PM, Anjan Dave wrote:

> Vivek,
>
> Was there anything specific that helped you decide on a RAID-5 and not 
> a RAID-10?

performance testing on restore times.  My DB is more than 50% write, so 
I needed to optimize for writes.

> I have my DBs on RAID10, and would soon be moving them on FC drives, 
> and i am considering RAID-10.

If I had to do it over again, I'd most likely go with RAID-50, and take 
the hit on restore time for the advantage on reads.  I have to dig 
through my records again to see the details... but then I have to do 
all that for my OSCON presentation on this topic at the end of July in 
Portland, OR. ;-)

Attachments:

  smime.p7s (application/pkcs7-signature, 2.4K) download

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


end of thread, other threads:[~2004-06-10 16:51 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-26 21:28 Database Server Tuning John Allgood <john@turbocorp.com>
2004-02-26 22:01 ` Troels Arvin <troels@arvin.dk>
2004-02-26 22:48 ` Josh Berkus <josh@agliodbs.com>
2004-02-27 03:07   ` John Allgood <john@turbocorp.com>
2004-03-02 20:51 ` Vivek Khera <khera@kcilink.com>
2004-03-02 21:27   ` Josh Berkus <josh@agliodbs.com>
2004-03-02 21:32     ` Vivek Khera <khera@kcilink.com>
2004-03-03 18:59       ` Josh Berkus <josh@agliodbs.com>
2004-06-10 16:02 Re: Database Server Tuning Anjan Dave <adave@vantage.com>
2004-06-10 16:51 ` Vivek Khera <khera@kcilink.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