Thread: Database Server Tuning
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
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
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
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
>>>>> "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/
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
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.
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
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
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. ;-)