LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Blogs > Musings on technology, philosophy, and life in the corporate world
User Name
Password

Notices



Hi. I'm a Unix Administrator, mathematics enthusiast, and amateur philosopher. This is where I rant about that which upsets me, laugh about that which amuses me, and jabber about that which holds my interest most: Unix.
Rate this Entry

PostgreSQL - installation/configuration/benchmarking - on Dell servers

Posted 03-27-2009 at 08:20 PM by rocket357
Updated 12-31-2009 at 11:44 PM by rocket357

Dell has historically had a pretty low-end line of RAID cards (the PowerEdge Expandable RAID Controller, or PERC for short) that have required tons of time and care to get them operating at a reasonable level. This blog post will cover my experiences with the PERC6/E line of cards (which aren't that bad, especially compared to previous versions) and how to get optimal performance out of them on Dell 1950/2950 machines, as well as PostgreSQL general configuration.

DISCLAIMER: I don't know everything there is to know about Dell/PostgreSQL/etc... If you notice I've overlooked something, please post a reply and I'll be sure to give credit where credit is due when I update this blog post. Also, you should NEVER blindly accept what you find on the internet as gospel. Posts such as this one can give insight into a problem, but you'll never know how your hardware will respond unless you TEST, TEST, TEST!

Ok, first off you need to ensure the hardware is sane (yeah, yeah, it's Dell, I know..."sane" is used here in a relative way). Ensure you don't have any IRQ conflicts, particularly with the PERC card. Nothing is more frustrating than troubleshooting slow disk speed for two days only to discover that an IRQ conflict exists!

Dell PERC cards have a BIOS configuration program that will ask you to "Hit Ctrl-R" when the machine is booting up. Hit Ctrl-R to open the configuration manager.

Before you touch anything, think a bit about the databases you will manage. Are they mostly read? Mostly write? Will they be subjected to small queries, like what a website might encounter, or wil they handle massive report queries that could take minutes to complete?

Knowing the data you're working with is *critical* to database performance regardless of the hardware/RDBMS/etc... You have to do your homework and determine what the system requirements are.

Concerning disk layout, it's generally best to break the following out onto their own spindle sets (given enough disk arrays, that is...):

Operating System
PostgreSQL data
PostgreSQL logs
PostgreSQL indexes
PostgreSQL temporary tablespace

Luckily, PostgreSQL allows you to add tablespaces to the server and utilize them in your databases, so if later down the road you realize that (for example) the security tables in your database cause enough I/O to rate their own spindle set, mapping them to another tablespace is relatively easy. If you don't have enough disk arrays right now to break all of the above listed items out onto their own arrays, you can accomplish that later down the road as more hardware becomes available.

For now, let's assume you have a Dell 1950 with 2x quad core Intel Xeons, 16 GB RAM, 2x internal 73 GB 15k SCSI disks, and a PERC6/E with an external MD-1000 with 15x 146 GB 15k SCSI disks. A typical setup for this would be like such:

Internal SAS Controller: RAID 1 (2 disks): Operating system
External MD-1000: RAID 10 (10 disks): PostgreSQL data(since we're dealing with a **reporting** database, RAID 5 would be ok here as long as ETL ops didn't take *too* much longer)
External MD-1000: RAID 10 (4 disks): PostgreSQL logs
External MD-1000: Single disk: Hotspare

If you have more disks it wouldn't be a bad idea to split off indexes or temp tablespace on their own spindles! (This particular machine is a reporting database that does massive sequential scans, so I don't bother much with indexes on it).

Once you have the basic disk layout figured out, initialize the arrays. Once that's done, pop in the Linux installation disk of choice (I prefer Gentoo, but we use Debian mostly where I work so I'll cover Debian) and reboot to the installer.

Ok, most everyone here knows how to install Linux, so I'll leave that alone. If you need help installing Debian (or whatever your preferred Linux distribution is), check http://linuxquestions.org or the like for assistance.

One caveat: Do NOT format the external disk arrays! We'll do that manually later so we can apply some performance tweaks!

Once Debian is installed, reboot into your new installation. First we need a baseline to compare benchmarks, so grab a few programs:

apt-get -y install xfsprogs hdparm bonnie sysstat alien

Next, download the following program:

LSI megaraid sas control suite

Then run the following commands:

Code:
linux ~ # unzip 1.01.39_Linux_Cli.zip 
Archive:  1.01.39_Linux_Cli.zip
  inflating: MegaCli-1.01.39-0.i386.rpm  
  inflating: 1.01.39_Linux_Cli.txt   
linux ~ # alien -d MegaCli-1.01.39-0.i386.rpm 
Warning: Skipping conversion of scripts in package MegaCli: postinst postrm
Warning: Use the --scripts parameter to include the scripts.
megacli_1.01.39-1_i386.deb generated
linux ~ # dpkg -i --force-architecture megacli_1.01.39-1_i386.deb
(Reading database ... 214775 files and directories currently installed.)
Preparing to replace megacli 1.01.39-1 (using megacli_1.01.39-1_i386.deb) ...
Unpacking replacement megacli ...
Setting up megacli (1.01.39-1) ...
linux ~ # ln -s /opt/MegaRAID/MegaCli/MegaCli64 /usr/bin/MegaCli
Once this is done, check out your PERC adapter like such:

Code:
linux ~ # MegaCli -adpCount
                                     

Controller Count: 1.
linux ~ # MegaCli -LDInfo -LALL -aALL
                                     

Adapter 0 -- Virtual Drive Information:

Virtual Disk: 0 (target id: 0)
Name:logs
RAID Level: Primary-1, Secondary-3, RAID Level Qualifier-0
Size:278784MB
State: Optimal
Stripe Size: 64kB
Number Of Drives:2
Span Depth:2
Default Cache Policy: WriteBack, ReadAheadNone, Cached, No Write Cache if Bad BBU
Current Cache Policy: WriteBack, ReadAheadNone, Cached, No Write Cache if Bad BBU
Access Policy: Read/Write
Disk Cache Policy: Disk's Default

Virtual Disk: 1 (target id: 1)
Name:data
RAID Level: Primary-1, Secondary-3, RAID Level Qualifier-0
Size:696960MB
State: Optimal
Stripe Size: 64kB
Number Of Drives:2
Span Depth:5
Default Cache Policy: WriteBack, ReadAheadNone, Cached, No Write Cache if Bad BBU
Current Cache Policy: WriteBack, ReadAheadNone, Cached, No Write Cache if Bad BBU
Access Policy: Read/Write
Disk Cache Policy: Disk's Default
Here you can see I have two disk arrays: data (10 disk RAID 10) and logs (4 disk RAID 10). To update a particular feature, you can run the following commands:

Code:
linux ~ # MegaCli -LdSetProp Cached -LALL -aALL
                                     
Set Cache Policy to Cached on Adapter 0, VD 0 (target id: 0) success
Set Cache Policy to Cached on Adapter 0, VD 1 (target id: 1) success
linux ~ # MegaCli -LdSetProp NORA -LALL -aALL
                                     
Set Read Policy to NoReadAhead on Adapter 0, VD 0 (target id: 0) success
Set Read Policy to NoReadAhead on Adapter 0, VD 1 (target id: 1) success
linux ~ # MegaCli -LdSetProp WB -LALL -aALL
                                     
Set Write Policy to WriteBack on Adapter 0, VD 0 (target id: 0) success
Set Write Policy to WriteBack on Adapter 0, VD 1 (target id: 1) success
linux ~ # MegaCli -LdSetProp EnDskCache -LALL -aALL
                                     
Set Disk Cache Policy to Enabled on Adapter 0, VD 0 (target id: 0) success
Set Disk Cache Policy to Enabled on Adapter 0, VD 1 (target id: 1) success
I've found that ReadAhead on the PERC Cards is pretty crappy...I manually set block device readahead with blockdev since it seems to perform better. YMMV.

Ok, let's partition these disks...use fdisk to partition your arrays, but be sure to set the beginning to the data partition to some multiple of 8 that is greater than 63 (The default offset of 63 (sectors) throws array alignment off). You can accomplish this in fdisk by hitting "x" (advanced), "b" (beginning of partition), "1" (or whatever partition number you created), and then, say, "128". Hit "w" to save your partition table. Do this for each array.

Some people have reported performance increases by "short-stroking" the drives (i.e. only partitioning the middle 75% or so). The theory is that the disk heads have a shorter distance to travel and as such seek time is reduced. If you feel up to the task, do so, but make sure the start of the disk is a multiple of 8!!

Continues here
Views 2873 Comments 0
« Prev     Main     Next »
Total Comments 0

Comments

 

  



All times are GMT -5. The time now is 07:08 PM.

Main Menu
Advertisement

Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration