Over the last year the number of customers, the amount of revenue, and the degree of stability (both financially and technically) has increased dramatically. It is a completely different company than 12 months ago with serious traction. My role in that progression was in my opinion minor and I chalk the success up to the absolutely stellar team that makes the company run now. I could not be more satisfied or proud of what has been accomplished and could not be happier or more complementary about the team that has gathered around it.

Those facts make it difficult and potentially confusing to mention my decision to step way from an active role in the company. I will still be involved as an adviser, supporter, and proud evangelist but will no longer be actively contributing to the technical development or operations of the company. This change is being made slowly over the next month or two to ensure the transition is smooth and I genuinely believe the company is going to be a great example of success for Atlanta.

What is next for me?

I am joining another startup in Atlanta working on an utterly different product. The founders consist of guys I worked with for years in the undergraduate computer labs at Georgia Tech and the technology is around areas I am very passionate about – those that I was focused on as a student before jumping at a chance to help take WTC off the ground as an early engineer. This opportunity combined with the strides made in the last year to put WTC in a stable, growing position are the reasons for this decision.

Needless to say 2010 should be interesting, I am looking forward to it. I’ll be trying to keep this blog a bit more active as well. I had a few posts in 2009 that really took off and hope to give it more attention in 2010.

Here are a few questions for vetting firms with the goal of purging the truly unqualified ones. These questions are based on actual experiences where the lack of understanding was so complete that I felt embarrassed for them. They can come in handy when looking for a firm to assist with some work.

Filtering the Worst

In what country is your coding done?

Generally I want to hear USA (point being – same country I am in). There are exceptions and I don’t want to get into my broader feelings on offshoring but that is what I need to hear.

What is DNS? Have you ever updated a DNS record?

Don’t care at all about getting a technically correct explanation here. I just want to get a feeling that they understand domains don’t magically point at the right place and that they could handle making simple adjustments when I don’t control the domain.

Using only notepad, textedit, or similar, write a page that has the following:

• a green div
• a button underneath that div
• some Javascript that toggles the green div from visible to not visible when the button is clicked

You would likely be amazed at how many agencies this would blow the minds of.

Do you know the difference between GET and POST? When would you generally use each of those?

Want to find general understanding here. Not concerned about a complete, technically correct answer but just want to hear vague familiarity.

Do you know what SSH, SCP, or SFTP are?

Them being familiar at all with just one of these is sufficient. A similar question is how would you get files on a server without FTP?

What are steps you could take to speed up the load time of a page?

At a minimum, hoping for an understanding that reducing the size of pictures/images helps. Beyond that mentions of caching, combining/compressing Javascript, not using every Javascript framework in existence, content distribution networks, or whatever else are icing on the cake.

What is the difference between server side and client side code? What would you personally prefer for each?

Basic blocks here but there are firms with paying customers that could not answer this question well.

Follow Up Questions

Only if the above completely trivial questions can be answered with some confidence should you consider asking follow up questions. Some ideas:

• How do you structure your code/css?
• What do you use for version control?
• Let me see some stuff you have worked on.
• What libraries do you commonly use?

I am not kidding when I say that I know of professional agencies that cannot answer a single question from that first set satisfactorily. Don’t assume the basics. You have to start from nothing when considering a web dev firm or else you might find yourself in a painful situation down the road. There are some excellent firms out there that can really get good work done so don’t take this as a blanket statement covering them all. It is just fair warning that you have to be careful when looking for someone to work on your stuff.

In any case, Open Manage is a big pile of rpms with lots of dependencies so it wouldn’t be easy to transfer to Arch.  I posted awhile back about using LSI’s Megaraid CLI tool to monitor Dell raid arrays but what about everything else?  One big item that was really haunting me was power supplies.  I had no monitoring on those things so if I went too long between datacenter visits to check for amber lights on the fronts of the servers, we could have a double PSU failure on a really important server and be in a lot of trouble.  Server PSUs fail A LOT so don’t discount the importance of monitoring them.  My personal experience might be unusual, but i’ve had more PSUs fail than hard drives.

Yesterday I read up on Intelligent Platform Management Interface (IPMI) and saw that Dell has supported it for awhile. I suspect their Open Manage tools are simply a proprietary wrapper around this.

So you just need some other software that will let you access this stuff. Some quick searches revealed ipmitools and freeipmi being popular.  I went with freeipmi because it is a very active project with recent updates (the Arch Linux AUR had them both but both PKGBUILDs were broken).

Installing it is straight forward:  download tarball, unpack, ./configure, make, make install.  This will place a bunch of command line tools in /usr/local/sbin/.  You’ll probably want to script the install process if you have to setup lots of servers.  Or, don’t be lazy like me and bundle up a PKGBUILD for the Arch AUR and just use yaourt for the installs.

You can check out the README and man pages for information about all the various commands but I am just using one: ipmi-sel.  This prints out the contents of the “System Event Log” and seems to correspond very nicely with any messages that appear on the front LCD of the server.  I removed, replaced, and removed again a PSU in a server and saw this perfectly parseable and useful output:

28:21-Aug-2009 09:00:36:Power Supply Status  resence detected
29:21-Aug-2009 09:00:37:Power Supply PS Redundancy:Redundancy Lost
30:21-Aug-2009 09:04:56:Power Supply Status  resence detected
31:21-Aug-2009 09:04:57:Power Supply PS Redundancy:Fully Redundant (formerly "Redundancy Regained")
32:21-Aug-2009 09:05:16:Power Supply Status  resence detected
33:21-Aug-2009 09:05:17:Power Supply PS Redundancy:Redundancy Lost

Other events I have seen in this log are memory DIMM failures and the case being open – and those are conveniently the only other alerts i’ve seen on the LCDs before. You can clear out the SEL and have a few other options just with that one ipmi-sel tool – read the man page for more information.

For monitoring PSU failure I am using this script. I am sure there are tighter approaches but this one works fine:

/usr/local/sbin/ipmi-sel | grep "PS Redundancy:" | tail -n1 | grep "Redundancy Lost" | wc -l

That will return a 1 if the last “PS Redundancy” related item in the log is a failure, and 0 otherwise. You can then easily snap that into Zabbix or whatever monitoring software you prefer. I did a post with more detail on adding items to Zabbix awhile back that might be helpful if you are not familiar with the process.

#1: Don’t do sequential scans – use indexes

Do SELECTs against indexes. Sequential scans will devastate your IO and in most cases should be avoided. I read on a bogus guide somewhere that needing indexes was a sign of a bad database schema. That is complete BS.

The only time indexes can hurt is when doing bulk inserts. I did a post about dropping/recreating to temporarily boost bulk insert performance awhile back.

Most of the time it doesn’t matter though. Throw down as many indexes as you need.

#2: Index all foreign keys

There are certain rare cases where this doesn’t help, but as your database grows in size this becomes increasingly important. Say you have a “people” table. You then have an “emails” table with a person_id column that is a FK to the people table. As an example say you have 10 million rows in the emails table.

Now delete a row from that people table. PostgreSQL must scan the emails table and ensure that no email rows reference the person row you just deleted. If that person_id column on emails is not indexed you just kicked off a sequential scan of 10 million rows with your deletion attempt. Index that column and the pain goes away. My general rule of thumb is to index every single FK. You can always drop the indexes later if they aren’t needed.

Here’s a handy query for discovering all the FKs pointed at a table in a database. You can then shoot through the results and verify that each table has indexes for those FKs.

select t.constraint_name, t.table_name, t.constraint_type,
   c.table_name, c.column_name
from information_schema.table_constraints t,
   information_schema.constraint_column_usage c
where t.constraint_name = c.constraint_name
   and t.constraint_type = 'FOREIGN KEY'
   and c.table_name = 'MY_TABLE_NAME';

#3: Don’t be afraid to selectively de-normalize

It is only a matter of time before you realize the normal forms you learned about in school can seriously hurt your database performance as size gets huge. De-normalize (usually meaning duplicate a value in two tables) when it provides a big performance increase. You must be mindful of the maintenance concerns with de-normalization. The maintenance can be done in code, with triggers or similar in the DB, or sometimes the data is static enough that it isn’t a huge concern.

Edit: I hedged this advice from the original post (originally was a blanket “de-normalize all the time” that could burn someone new to databases). You should know the normal forms and understand them. My point was that you shouldn’t feel that wavering from them with de-normalization makes your database a bad one – it is simply a reality of nontrivial production databases. But, if you don’t understand them, and/or don’t understand when and why de-normalization can help blindly ignoring the normal forms is only going to get you into more trouble. So learn them and understand them but then don’t be afraid to selectively de-normalize where it can give you big boosts of performance. A common scenario that is often very helpful is de-normalizing to eliminate a join or multiple reads.

#4: Avoid joins on huge tables

As your tables get bigger and bigger (many millions of rows) joins cannot be done in memory and spill over to disk. Once that happens your performance is gone. For smaller tables it doesn’t matter but as tables get bigger avoid joining when you can. Try using unions, subselects, or denormalization instead. If you have two big tables that are 1-to-1 that you are joining a lot combine them into one wide table.

#5: Don’t do unanchored text searches or full text searching

That is don’t try something like this with wildcards on both ends:
select * from people where first_name like '%joe%'

The reason is that no index can be used so PostgreSQL will be doing a sequential scan no matter what. If you truly need full text searching use Lucene or PostgreSQL 8.3+ full text searching capabilities instead of standard indexes.

If you are in a pinch and can get by with the left side being anchored you can create a special index using varchar_pattern_ops like this:

create index idx_people_first_name on people (first_name varchar_pattern_ops);

That index can be used by this query (no wildcard on left side):
select * from people where first_name like 'joe%';

But be aware that same index cannot be used by a query using the standard “=” operator so you have to create two indexes to get both (one with varchar_pattern_ops, one without). Yes this is messy and its why you should use Lucene, PostgreSQL 8.3+ full text searching, or something similar to do text searching.

#6: Use the COPY command for bulk data loading

It is orders of magnitude faster. Check a previous post of mine if you want numbers.

#7: Use multi-column indexes

For common queries with compound WHERE clauses create a multi-column index for maximum performance. An example:
create index idx_people_full_name on people (first_name, last_name);

Now you can do queries like this and hit that index:
select * from people where first_name = 'joe' and last_name= 'smith';

Additionally, any WHERE clause using just first_name can take full advantage of this index (because first_name is the first column) so don’t waste disk space creating redundant single column indexes if you are already covered by a multi-column.

Finally, you can align a multi-column index with a common ORDER BY clause to make that query instant. Using the above index a query against people ending in order by first_name, last_name would be super fast.

As a final note PostgreSQL can combine multiple single-column indexes so multi-column indexes on every combination are not necessary. But, a single multi-column is going to be faster so they can be helpful for queries you run a lot. You can read more about combining of multiple indexes here.

#8: Don’t bother indexing evenly distributed booleans or enum columns

In PostgreSQL indexes do not store data, they are instead pointers to the data. This means that after scanning the index a random read must be done for each matching record to get the data. Random IO is a lot more expensive than sequential IO (though SSDs are changing that) so if the PostgreSQL planner (it does sampling of your data in the background) knows you have a very evenly distributed column (a boolean split 50/50 for instance) it won’t use the index at all. It will instead make the assumption that a straight up sequential scan will be faster than index scanning for half the rows and having to do a ton of random IO. Indexes on these types of columns are a waste of disk space and you are slowing down your inserts for no benefit.

#9: Use EXPLAIN ANALYZE to benchmark and compare queries

Using EXPLAIN on its own will give you the planner’s guess at how it will run your query. This is pretty useful if you have a very huge query that is going to take a long time and you want to tune it without having to actually run it after each tweak. The complete EXPLAIN ANALYZE will run the query, show the plan predicted, show what actually happened, and how long it took.

Be sure to ANALYZE after significant table modifications so that the planner can do its sampling and adjust its plans to account for any index changes.

#10: Use expression indexes – they are awesome

In PostgreSQL you can index an expression. Here is a simple example:
create index idx_people_lower_first_name on people (lower(first_name));

Now you can run queries like this and use the index – no need to create a separate column with lowercase values:
select * from people where lower(first_name) = 'joe';

You can similarly index combinations of your data-containing columns:

create index idx_people_lower_full_name on people (lower(coalesce(first_name, '')) || lower(coalesce(last_name, '')));

#11: Indexes can have WHERE clauses

You can create conditional indexes that only apply for certain rows. A useful scenario is creating a unique constraint on a column or set of columns only where certain conditions apply:

create unique index idx_people_person_id on people (person_id) where deleted = false;

That’s a simple example that feels a bit cleaner than changing the ids of deleted records to avoid conflicting with a unique constraint. Anything can be placed in that WHERE clause, they aren’t limited in any way.

#12: Don’t use the database if it isn’t a good fit

Databases only scale so far alone. At some point you are going to have to explore memcached, terracotta, or something similar to continue to scale. The NoSQL (over)excitement going on right now offers alternatives as well for certain scenarios. The main point is that at some point your database will just be one component in a set of services and technologies that provide the persistence layer for your application.

It was a nightmare to get working – the RIM software has literally a half dozen separate flaky services all running at the same time and tripping on one another that have to align perfectly. The worst part though was having to work with a Window server.

That leads me to my question: why does anyone use Windows on servers any more? How is it manageable at all? For any Windows admins out there, would love to get some perspective on why people use it.

• How do you manage updates? Especially with lots of machines, it seems borderline impossible to keep them all updated without downtime considering the mandatory restart needed for everything. Linux only needs a restart for some kernel modifications (i.e. barely ever). I have machines in production with 700 days of uptime.
• What do you do for remote access? Does an admin really have to login with a GUI remote desktop to each server? Is there a VPN endpoint that has to be used or are all the servers left open? How are bulk operations configured and run in that kind of mess? With Linux I can whip up a bash script in minutes to do whatever I need done to as many machines as I wish.
• Is there a fast approach to setting up new servers on a variety of hardware? I can install Arch Linux in less than 10 minutes and I am not even doing anything advanced like PXE boots or disk imaging.
• Windows fails in large environments. The latest big example is The London Stock Exchange completely abandoning Windows after substantial investment of money and time to set it up on that platform. As an older example I know for awhile (maybe even still today) Hotmail ran on FreeBSD after Microsoft purchased it because they couldn’t make it stable on Windows.
• Aside from the second rate Microsoft products that are decisively outperformed by Linux alternatives (IIS, MSSQL, Exchange) is there any decent server or DB software that doesn’t run better on Linux than on Windows? I am thinking things like Apache, PostgreSQL, Tomcat (and the Java VM in general), Mongrel, JBoss, and innumerable others.

This is really just my frustrated attempt at understanding why people put themselves through the utter pain of running Windows servers. Why would you not use Linux? As long as there are people using Windows it means crapware like Blackberry Server has no incentive to port to Linux and that means I have to suffer at work. We’re 4:1 ratio for iPhones to BBs so maybe the best option is to get those final BB users switched over to a better phone.

If you are starting a company don’t even bother with Windows on the server. Their new Bizspark program may seem like a good idea on the surface but I view it as a trap. You get to use an inferior product for a few years and then must pay through the nose at the end or rebuild everything with the better technology you should have used from the beginning.

There are a lot of network attached storage options out there now but they are a bit expensive and have arbitrary limitation mixtures in the software. Plus Lindsey was not keen on me spending more money on gadgets.

Instead I looked at the random gear I had around the house and was able to quickly get raided network storage setup for no cost that is more flexible than most of what you can buy. Nothing new here, this is a pretty familiar setup. It just worked out well for me so wanted to share.

I had the following to work with:

• An old Dell laptop purchased about 4-5 years ago. 2GB of ram, I don’t know any of the other specs.
• The cheap WRT54GS Linksys router I was already using at the house.
• A couple 500 GB external hard drives.

That isn’t much but I imagine many people have a similar combination just lying around. Most of my extra gear (including the last desktop I owned) got absorbed when the company was starting up.

Here’s the steps to set this up for yourself.

[1] Put DD-WRT on the router

The big items I wanted were reserved DHCP (so I could reserve an IP for the new “server”) and VPN (so I could connect remotely to my home network). DD-WRT offers both of these along with loads of additional features. It makes the stock Linksys firmware look like garbage in comparison.

My WRT54GS is an especially cheap router with a tiny amount of memory so I had to install the pared down “micro” version of DD-WRT but it still works great.

For help and information on DD-WRT go visit their site. You can enter your specific router model number and get the exact files you need along with specific instructions. Its really easy to do.

[2] Put Arch Linux on the laptop

Arch Linux is a preference thing but I do believe it is the best Linux distribution. I use it on all of our servers at work now and am glad Sean told me about it. Since in this setup I am creating a dedicated server out of the old laptop and don’t plan to ever use it directly Arch is a perfect fit – just do a base install. No need for x windows or any of that cruft. Arch makes installing things so incredibly easy. In 95% of the cases the steps are to grab the software with pacman, add it to the DAEMONS array in rc.conf (if its something that should start on its own on reboot), and then start it.

Obviously you can use a different Linux distribution if you would prefer but subsequent content below assumes Arch.

To get Arch or to view their fantastic wiki and documentation go to archlinux.org.

At this point I gave the Arch server’s mac address an IP reservation in DD-WRT. That’s optional but I would highly recommend either doing that or using a static IP.

[3] Connect external drives to laptop and raid them

SSH into the Arch server and plug the drives in. The faster the hard drive connection the better but honestly for stuff you are going over a network for it doesn’t really matter. Your laptop hard drive is probably going to be /dev/sda and your plugged in external drives are probably going to be /dev/sdb and /dev/sdc. I am making that assumption in the following, you can ls -l /dev/sd* to verify what is showing up. On my machine the external drives showed up as “storage” (for the group permission) while the laptop drive was “disk”.

Install mdadm with pacman -Sy mdadm. This is for the software raid. Also install parted with pacman -Sy parted. This is what I use for partitioning instead of fdisk. For more information about mdadm read the wikipedia page which has good tutorials and more information in the external links section.

Now, assuming /dev/sdb and /dev/sdc for the external drives as mentioned above do the following. This is all done as root.

• Partition the first drive. Type parted /dev/sdb and you will get the parted prompt.
• From the parted prompt type mklabel gpt.
• Type mkpart primary 0 100%.
• Type quit to leave the parted prompt.
• Now partition the second external drive in the same way (just do parted /dev/sdc and repeat the steps).
• Create your raid array with mdadm --create --verbose /dev/md0 --level=raid1 --raid-devices=2 /dev/sdb1 /dev/sdc1. This is creating a raid 1 with 2 drives.
• Now format your new raid array with mkfs.ext3 -b 4096 /dev/md0. This will format with the ext3 file system but you could use whatever you wanted instead just as well.
• Create a directory wherever you want for mounting the new raided disk. I used /var/raid_array. That would be cd var and mkdir raid_array.
• Mount the new disk with mount /dev/md0 /var/raid_array. The raided disk should be usable now. Do a df -h to make sure it mounted and verify the usable space is what you expected.
• Edit /etc/fstab and add a line containing /dev/md0 /var/raid_array auto defaults 0 1 to ensure the new disk gets mounted on reboot. You can obviously change those mount parameters to whatever values you prefer.
• If you want, do some benchmarking and consider bumping up readahead on your new raid array. See my last post for information about that and how important it can be. BUT, if these are USB-connected external drives no need to waste time tuning. With software raid at least the OS knows there are two drives involved and so will tune up your readhead to 512. Just wanted to mention it as generally when setting up a raid array (or any decent hard drive) you want to be aware of that setting.

In the above I was assuming the external drives were the same size. If they are not then in the “mkpart” command use a fixed MB amount or smaller percentage for the second number instead of 100%. You could then separately partition any remaining space so it is at least usable if not in the raid.

The above uses raid 1 but mdadm can handle a lot of different configurations, just tweak the “–level” and “–raid-devices” parameters as needed.

At this point you have a raid array usable at /var/raid_array on the laptop “server”.

[4] Setup samba

Your new raid array wouldn’t be much use if you couldn’t easily access it. Use samba to set it up as a network share that all of your home computers can see or use. I’d recommend checking out the consistently awesome Arch Wiki for samba instructions. As with all things Arch, installation and configuration is very straight forward and cruft free. Your mileage may vary on other distributions.

[5] Finish up

From here you can setup whatever you like. Here is what I did.

• Setup an iTunes share so my music on the network just shows up when I am at home. You can do this by setting up a daapd server, check out the wikipedia article about daap for an overview of what it is. There is unfortunately not an Arch Wiki article for this so here are the steps if interested.
• Grab the needed software with pacman -Sy mt-daapd and pacman -Sy dbus.
• Edit /etc/mt-daapd/mt-daapd.conf. Set the “mp3_dir” to the root location of your music and the “servername” to your server name. You will probably want to add more extensions to “extensions”. I added “.m4a,.m4p,.aac”.
• Add dbus, avahi-daemon, and mt-daapd (in that specific order) to the DAEMONS array in rc.conf.
• Start them up in that order with /etc/rc.d/dbus start, /etc/rc.d/avahi-daemon start, and /etc/rc.d/mt-daapd start.
• That is it. Your music should show up in iTunes as a shared library with whatever name you set as “servername” in mt-daapd.conf.
• Setup a backup script that pushes select important items up to s3 each week. I used s3sync to do this but you can use whatever you like. s3sync is straight forward and easy to use in command line scripts. Here is the s3sync site and here is a blog post that describes usage well.
• Setup a basic monitoring script, particularly to watch the status of your raid. You can use mdadm --detail /dev/md0 to get the status of your drives and write a script to parse out the pieces you want. Since this is software raid with random external hard drives you aren’t going to get a nice red LED telling you when a drive is dead. Have it check each morning and email you if things are busted up. If you are sending email in your scripts do a pacman -Sy postfix first.

Conclusion

The above may look like a lot of steps but it really is more straight forward than it seems. With Arch especially, getting things setup is a snap. This is a cheap solution and isn’t going to be a screamer on performance but it offers a lot of advantages:

• Cost $0 if you have an old laptop and a couple hard drives.
• Even though its a cheap solution you still have complete features (raid, networked, iTunes share, plus whatever else you install on the server).
• Can be easily expanded with additional drives, software raid in general offers a lot of flexibility and easy monitoring.
• Laptops actually make decent home servers because they have batteries in them – power outtages aren’t a big deal. On the other hand, your external hard drives will still die so maybe not a huge advantage.
• You have a actual machine on top of the drives so you can easily add whatever server software you want. You can use it as an SSH tunnel, web server, whatever.

We recently ordered one of Dell’s MD1120 units and a Perc 6/E raid card with 512MB battery-backed cache to beef up our production database.

Dell’s raid controllers are rebranded models manufacturered by other companies and they have been hit or miss. They’ve done some horrible things (including advertising raid1-concatenated as raid10 a long while back) but my impression from reading online and from my own benchmarking are that these Perc6 cards are decent (but not exceptional). You still get the lockin aspect – Dell won’t support your machine if there is a non-Dell raid card in it and the MD1xxx units supposedly only connect to Perc5 and Perc6 cards.

The MD1120 itself is a pretty cool unit. Its only 2U and packs a lot of drives. We ordered one with 24x 73GB 15K SAS drives. No SSDs, I am amazed by SSD numbers but figure we can wait a few more years before shelling out the cash to fill an array with them. I want more data on their reliability in a 24×7 high IO server environment. Here’s a picture of the new guy.

The next time we have the need and budget to purchase a new database server from the ground up I plan to go whitebox but in this case we were looking for a relatively inexpensive way to get more capacity out of our existing Dell server and this seemed like a good option. I just had to ignore their storage tech guy who wanted me to buy a Gigabit SAN unit and screw our performance. So just ignore the tech guys that are part of the sales process and do your own research and benchmarking.

After benchmarking this Perc6+MD1120 combination extensively and putting it in production I am reasonably happy with its performance. Going to share those numbers now as it is sometimes hard to track down data on these things.

The Benchmark

Bunch of notes about the testing environment and configurations for anyone interested. If just want numbers skip past these.

• Perc 6/E upgraded to latest 6.2.0-0013 firmware and connected to a new PowerEdge 1950 with 2x Xeon E5410s and 8GB RAM.
• MD1120 connected directly to Perc 6/E.
• All hardware raid configured with 64kb stripes, write back enabled, read ahead disabled (Dell hardware read ahead isn’t good).
• Server running latest opensuse. Did this purely to make it easier to upgrade firmware, get Dell support etc. If you call Dell and are using opensuse just lie and say you are running Suse 10 – everything will work and they will never know the difference.
• All tests were run 3 times and the middle run was recorded.
• xfs mount options were just noatime and ext3 mount options were noatime,data=writeback.
• xfs file system params were -b size=4096 -d su=64k,sw=X where X was the appropriate value for the configuration involved. ext3 params were -b 4096 -E stride=16,stripe-width=192.
• dd params were “bs=8k, count=2000000″ ensuring a file 2 times size of RAM to bypass OS cache.
• The bonnie++ random seeks/second is the most important number for DB performance.
• I did a ton of tests with the first configuration and then settled into a groove of just testing the bits that seemed to matter. Hence the odd distribution of tests by config.

The dd and bonnie++ 1.02 results on opensuse

The distinct raid configurations are color coded and numbered. The winning individual tests are bolded.

Observations:

• Raid10 is the best option. Winning configurations are pure hardware raid10 with loads of readahead (test #9) and software-striped raid 10 for “raid 100″ (tests #19 and #34).
• I wasn’t impressed with raid6 or raid60 and raid0 isn’t a realistic option so that is why those setups aren’t as heavily hit in the above configurations.
• The IO scheduler didn’t really make much difference. CFQ seemed to be just as good or better so stuck with it (its the default).
• readahead makes a huge difference. Linux defaults this to 256 per drive and Linux sees a hardware raid array as 1 drive. You absolutely must increase that 256 default value to at least 4096 in my opinion. I increased it as high as 32768 for the pure raid10 config and performance didn’t suffer in the seeks/sec or as reported by pg_bench while sequential read speeds increased dramatically.
• xfs is faster than ext3 in all tests I compared them in. Included just a few ext3 numbers above. But, don’t use XFS (or ext3 with data=writeback) unless you have both a battery-backed cache on your raid controller and are connected to a UPS for main power (and ideally you should be monitoring the health of the BBU on the raid controller to ensure the battery isn’t dead). You could lose data if this advice is ignored.

The bonnie++ 1.03e results on Arch Linux

Next I installed Arch Linux which happens to come with bonnie++ 1.03e. I did a couple bonnie++ runs just to make sure the new OS didn’t mess anything up and was shocked to see dramatically better random seeks/second numbers. Sequential speeds were virtually the same, but seeks/second was massively faster. Here is a table showing some of the configs (I didn’t retest them all – was running out of time) with bonnie++ 1.03e seeks/second numbers. I am going to credit the newer version of bonnie++ for this difference. I retested enough configurations to feel good about the trends seen when testing with opensuse and bonnie++ 1.02 still holding. Open to hearing other possible explanations for the performance increase. I was glad to see these numbers as I was disappointed with the apparent 800ish ceiling I was seeing in the first batch of tests.

Here are the configs I retested (middle of 3 runs again). The Test# matches the row in the above table. The new 1.03e score is listed next to the old 1.02 score.

Observations:

Higher numbers across the board. No big new insights. Raid 10 still wins, raid 6/60 still substantially slower. At this point that pure raid 10 config (test #9) that scored a 1717 is looking pretty nice. The pure software raid 10 (test #38) fell behind the hardware version further. Biggest take away from this is to be absolutely certain that when you are benchmarking disks the OS and tools are identical.

The pgbench results

Finally, I took the 3 fastest configurations and did pgbench runs with those. I was running out of time so I took what looked like the winner (test #9) and additionally tuned readahead and schedules a bit to ensure I got the best combination. pgbench isn’t perfect and there are people who dislike it but it gives us another number to compare and consider along with the raw dd/bonnie++ numbers already known. Keep in mind I am using a PostgreSQL install and pgbench on this new server – not the actual production server. Doing the benchmarks on the actual final server just wasn’t an option. The only significant difference between the benchmark server and the production server is that the latter has 4 times the RAM and has had nontrivial postgresql.conf tuning so I can only assume these numbers would improve a good bit.

I just tweaked a few things in the postgresql.conf file on the benchmark server. The non default values:

• shared_buffers = 2048MB
• checkpoint_segments = 10
• effective_cache_size = 4096MB
• max_connections = 500
• work_mem = 20MB
• maintenance_work_mem = 128MB
• synchronous_commits = off
• random_page_cost = 2.0

Note that I did have to increase SHMMAX to get shared_buffers + connections that high. See my PostgreSQL setup post for more information about that.

Between each run I dropped the “test” database and recreated it. I initialized with these commands. Note the different scale factors to get nontrivial data amounts – scale factor 1000 is pretty large but was necessary before I saw the disks working constantly during the benchmarks.

pgbench -i -s 100 -U postgres -d test
pgbench -i -s 1000 -U postgres -d test

Then I ran the tests specifying 40 clients and 10,000 transactions per client in the params. Read more about the pgbench tool for the test differences and what the transactions involve.

Here are the pgbench results. Numbers are transactions/second. Values listed are again the middle of 3 runs. I would have liked to do these pgbench runs on more configs but I again was running out of time and these suckers took a long time to run with all the dropping and re-initializing.

Observations:

These pgbench tests were mostly a wash. But that isn’t a big surprise considering I only compared the 3 best configurations that were close in the dd/bonnie++ tests. Really wish there had been time to get some other configs in that table to see the difference. The #9 config did well with a top 3 number in all 4 tests.

Conclusion

I went with setup #9. Pure hardware raid 10. It won almost all tests (with the striped raid 10s being the only real competitors) and pure hardware raid is super easy to configure, maintain, and monitor.

More generally this MD1120 performs pretty well, especially for the relatively low price. As a quick note on price buying one of these MD1120s as configured above along with a PowerEdge 1950 with 16GB RAM and a Perc6/E card to connect them would cost less than $15k (about $540/mo for a $1 36mo lease) depending on the configuration and the deal you got. You could probably even shave a few thousand off that number if you got a real good deal.

If you do buy stuff from Dell be sure to get in touch with a small business sales team. They can offer nontrivial discounts on the price you can finagle in their online shopping cart, you get to talk to the same people every time you place an order, it gives you a contact if you have questions or run into issues with technical support, and it just generally is the way to go.

This MD1120+Perc6/E combo is connected to our existing DB server now and all I will say is performance is excellent. I am seeing zero query backup, barely any IO wait reported by vmstat, and hugely impressive random IO spikes when load gets heavy (though we haven’t gotten close to maxing out so who knows how high it could go). And, thanks to all this testing, I feel really good about having the optimal configuration for the hardware out there doing work.

For this post I am working with 600k records being loaded into a table that looks like this. The data plus delimiters is about 60MB.

                 Table "public.people"
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 id          | character varying(36)       | not null
 first_name  | character varying(255)      |
 last_name   | character varying(255)      |
 middle_name | character varying(255)      |
 client_id   | character varying(36)       |
 birth_date  | timestamp without time zone |
Indexes:
    "people_pkey" PRIMARY KEY, btree (id)
    "idx_people_client_id" btree (client_id)
    "idx_people_name_search" gin (to_tsvector('english'::regconfig,
      (((COALESCE(first_name,
      ''::character varying)::text || ' '::text) ||
      COALESCE(middle_name, ''::character varying)::text) || ' '::text) ||
      COALESCE(last_name, ''::character varying)::text))

A few points on this particular table and config:

• The data is a subset of a real production table made up of entries people provided on themselves so the distribution is realistic and balanced.
• The primary key id column is a UUID generated by code.
• A very basic index on the client_id column.
• A more complex expression gin index on the name fields to allow full text searching. Full text searching was added in PostgreSQL 8.3 and it’s pretty powerful. Read more about that here if interested.
• Overall it is a relatively small, simple table but not trivially so and its large enough to get decent timings from bulk load operations for comparison purposes.
• Performing tests against a clean DB, empty table, and freshly created indexes so we have a best case scenario.

So, let’s say you have that empty table and want to load 600k records in. Here is a sequence of methods going from worst to best. I am doing a VACUUM ANALZYE before and after each operation. All timings rounded up to nearest second. We are comparing INSERT and COPY.

First Setup: leave indexes and pkey in place.

INSERT: 6 minutes, 5 seconds

COPY: 3 minutes, 11 seconds.

Second Setup: drop/recreate indexes, don’t touch pkey

When timed it took 1 second to drop indexes and 13 seconds to rebuild them. I have added that overhead into these numbers.

INSERT: 3 minutes, 19 seconds

COPY: 1 minute, 5 seconds

Third Setup: drop/recreate indexes and pkey

When timed it took 1 second to drop indexes and 15 seconds to rebuild them (includes pkey). I have added that overhead into these numbers.

INSERT: 2 minutes, 49 seconds

COPY: 21 seconds

Conclusion

With that final setup, dropping/recreating all indexes and using the COPY command, I was able to load 600k records in 21 seconds. That includes the drop/recreate overhead. The actual COPY command only took 5 seconds.

General notes related to the tests done above:

• The COPY command is magical. It completely blows away INSERT for bulk load performance.
• Dropping and recreating indexes is far faster than leaving indexes in place and forcing thousands or millions of individual updates to them.
• Do a VACUUM ANALYZE before and after bulk loading data to be safe. If you dropped indexes be sure to recreate them before doing the ANALYZE.
• If you don’t think the speed improvement shown is significant keep in mind we are working with a narrow 6 column table, with just 3 indexes, and not very much data. The savings found by dropping and recreating indexes becomes enormous as your table height and width increases and/or your number of indexes goes up. Similarly if you are only working with thousands or low tens of thousands of rows you can just do whatever is easiest since the size is so trivial.

Some other notes worth mentioning:

• The same rules apply for updates. ALL indexes slow down updates, not just the ones on the column you are updating so if you need to update an entire table dropping and recreating the indexes will provide a massive speed increase.
• If you are doing a bulk update and using a field on the updated table for lookups you are generally better off dropping all indexes except the one on your lookup column. Say for each person in my people table I am looking up the specific person by id and setting their client_id. I would want to leave the index/pkey on the id column so those lookups were fast. You still pay a penalty at write time because the index/pkey must be updated but not having the index would mean a sequential scan is done on the entire table for each update.
• Though incredibly fast the COPY command is very picky about its input. It is an all or nothing operation and if there is even a slight issue with your data it will fail. You will want to VACUUM your table after a failed COPY to recover space. Thankfully if it does fail it prints out the exact line number and reason.
• When specifying a file for the COPY command you must use an absolute path and the file (and all directories in its path) must be accessible by the postgres user because it is the server that goes and reads the file not the client. I find it easiest to just toss my input files into /tmp.
• Consider partitioning your data to allow drop/add of indexes on certain partitions preventing an entire table from having awful read performance while data is being loaded. Partitioning is a whole separate topic but read the docs, be sure constraint_exclusion is on in your postgresql.conf, and be sure your check constraint is in the where clause of all queries if you take this route. Partitions and significant bulk data loading fit together really really well.
• Seriously read the PostgreSQL documentation on this stuff if you are bulk loading data. Aside from the additional SQL-level recommendations it covers the tuning options for your DB that can make a very significant difference.

Finally, you may find that expression index for full text searching in my people table above looks strange. I hope to do a post on indexing specifically that explains in detail. In my opinion tuning database indexes is a blast and made even greater by the amazing capabilities of PostgreSQL.

With recent customer additions we have had to deal with lots of scaling challenges the last few months. One puny fraction of that effort has been around tuning our database connection pools. In very short, if your database server is overwhelmed with concurrent connections it can get bogged down in context switching and resource/io waiting such that overall throughput or transactions per second can drop. In the best case things just get slow. In the worst case things completely lock up because the server just can’t service all of those connections or your client code starts throwing exceptions because the database has reached it maximum configured connection count.

One way to control the maximum number of connections hitting your database at once is via connection pooling. This can ensure a consistent load that your database can dispatch quickly to keep a better overall throughput going. You can also match your aggregate pool sizes to the maximum connection count configured in your database server to ensure your client code never asks for more than the DB can give. The other HUGE advantage to connection pooling is that you are essentially caching the overhead of obtaining new connections. To the database there is a set number of connections made and held open – the pooling software then distributes those pooled connections to clients that need them and when the clients are done the connection remains open and waiting to be used again. This avoids a surprisingly high amount of overhead.

There is definitely a trade off. If your application needs to have a certain number of connections simply because you have too many concurrent users it is time to tune your code/queries/indexes (HUGE gains can always be found there), push stuff into distributed caches, or buy new database hardware so you can service more connections. There are recommendations online of a connection count as low as 2-4 per core. We’ve settled in around 30 per core and that seems to be a sweet spot for stability and performance. With 8 cores we can sustain well over 1000 transactions per second on a single PostgreSQL server (running on decent but not exceptional hardware).

We use JDBC connection pooling with c3p0. Each of our application servers has its own pool. We ensure that the sum of those pool sizes matches the connections that the database allows and also equals our desired connections per core count.

We explored using PgBouncer instead. This differs in that the connection pooling happens on the server – clients just ask for as many connections as they need talking to PgBouncer instead of directly to PostgreSQL.

You cannot use both JDBC pooling and PgBouncer or other server-side pooling. Or more correctly you can but there is no point. Say you set PgBouncer to maintain a pool of 500 connections. If you have JDBC pooling in the mix that just means you have to tune your individual client JDBC pools such that the sum of their pools doesn’t exceed 500. If that is the case, why use PgBouncer? Just use PostgreSQL and set it to handle 500 connections. Instead you would drop JDBC pooling and just have your clients ask for whatever they wanted while PgBouncer did the pooling and connection management.

Comparison

In deciding to move to PgBouncer or stick with c3p0 I ran a few tests. Here are those results. The goal here was to compare the overhead of obtaining connections not to determine the overall best solution by throughput. That would require significantly different and more involved tests. We were considering a move to PgBouncer and just wanted to consider any difference in connection overhead. These tests were done with a pool size of 50 and with client code connecting to a separate database server on the same subnet and physical switch.

First Test
Ran 500 queries with each query being a simple select 1. This simple query means most of the time is connection overhead so its a good test to decide between pooling solutions.

Second Test
Ran 500 queries where the query is selected sequentially from an array of 5 distinct queries varying from very simple to very complex. Highly imperfect test, but trying to see how connection overhead factors into a more normal load.

So c3p0 definitely offers the fastest solution. Additionally PgBouncer definitely makes a difference over not using connection pooling (and thus having to obtain a new connection with each query). This makes some sense as c3p0 is closer to the client code and keeps the pooled connections local. With PgBouncer the clients have to hit the network to queue up with the server.

Conclusion

We stuck with c3p0. But in my opinion where PgBouncer really shines is on the administrative side. As we continue to add more servers a move to PgBouncer or something similar seems likely. It (and PgPool-II, another solution) offer other capabilities as well. PgPool-II even offers built-in load balancing of reads against replicated copies of a DB.

Say you have 10 application servers and 1 database server configured for 500 maximum connections. You set the individual application server pool sizes to 50. Now you need to add a new application server. You would have to tune all 10 of your old servers dropping their pool sizes down to make room for the new one. This problem gets more and more severe as you add more servers.

Also, tuning individual pool sizes per client means there is a chance that one server is starving while others have pooled connections sitting idle – the connections aren’t shared. Probably not a big issue if you are load balancing user connections to your application servers evenly but still a possibility.

As you get more and more machines the server-layer pooling with PgBouncer definitely makes more sense. It also is obviously client language neutral so if you are running in an environment that doesn’t have mature pooling solutions its a great option.