Thread: Database Server Tuning

Database Server Tuning

From
"John Allgood"
Date:
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



Re: Database Server Tuning

From
Josh Berkus
Date:
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


Re: Database Server Tuning

From
John Allgood
Date:
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

Re: Database Server Tuning

From
Troels Arvin
Date:
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


Re: Database Server Tuning

From
Vivek Khera
Date:
>>>>> "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/

Re: Database Server Tuning

From
Josh Berkus
Date:
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


Re: Database Server Tuning

From
Vivek Khera
Date:
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.


Re: Database Server Tuning

From
Josh Berkus
Date:
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


Re: Database Server Tuning

From
"Anjan Dave"
Date:
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
    


Re: Database Server Tuning

From
Vivek Khera
Date:
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. ;-)


Attachment