Nick Khamis wrote:
I should also point out that we are interested in load balancing and
high availability.
Regards,
Ninus.
Alright there's a lot going on here so I'm going to break down the last
ten years of dealing with sort of thing into three pages. :-)
Stability vs Flexibility
I'm a start up guy (five and counting) so I always prefer flexibility,
but you need to decide based on your application. Also depends on how
much money you have to build in fault tolerance, back ups, etc. You
yourself as the admin also need to be disciplined in your methods. That
means having actual QA processes, test/stage VMs, unit tests, and being
able to enforce those processes. Gentoo allows enormous flexibility and
being able to have things like glibc-2.9 immediately while RHEL4 shipped
with 2.3 and RHEL5 with 2.5 means you can take advantage of incremental
fixes in NPTL that is missing in stable distros. Also having gcc-4.4 is
a big win on modern processors.
Mysql
Definitely go with Mysql 5.1 and hell if you're going to be building
your own or if it's already in an overlay somewhere look at Mysql 5.4.
Basically it's 5.1 plus the Google, Percona, and everyone else that has
been rolling custom patches for Mysql. If you don't want to be that far
out on the bleeding edge look at using Percona's build, linked below.
If you want to go way way way out to the bleeding edge and can wait a
year to ramp up, Drizzle is very interesting.
http://dev.mysql.com/tech-resources/articles/mysql-54.html
http://www.percona.com/percona-lab.html
http://www.mysqlperformanceblog.com/
http://drizzle.org/wiki/Drizzle_Features
High Availability
Round Robin db masters almost never works unless you've designed your
schema from the ground up to work that way. If you're wondering if yours
was, it wasn't. Even when you do it right it can be flakey. Easier and
simpler to write to one master which then writes to a number of slaves.
If you want to get fancy to you can have two round robin masters with
two slave each. When a master fails you need to point to the other
master as well as pull the two slaves from the broken master out or
rotation. How to accomplish that is up to you, but I prefer a somewhat
manual process. Swapping masters around automatically is usually a good
way to end up with corrupt data somewhere. YMMV.
Simple round robin VIPs should work with your Mysql slaves. Not sure if
Ultramonkey does that. Connection pools usually suck and I wouldn't
bother with them as modern OS threading makes it nearly pointless. Make
sure your application is closing Mysql connections properly which I've
had issue with far too often.
Storage Engines in Mysql
Sphinx
Don't use myisam tables for full text searches. Hell if you have the
time don't use your database for full text search, but if you do look at
using the Sphinx full text engine. You'll need to build the plugin yourself.
Innodb
Use the innodb plugins, it's much faster
Myisam
Don't use. Really.
xtradb
Innodb fork by Percona. Looks interesting and I have tried it.
Things to remember about databases
Buffers are configured on a per storage engine basis. If you give 12GB
to Innodb you can't also give 12GB to Sphinx... unless you have a 32GB
machine.
RAID 10 is your friend, but RAM is almost always better *if* your
database will fit into RAM. Make sure your RAID card has battery backup,
write cache on your disks is turned off, and that you actually check
your RAID card's config to make sure cache is turned on an DMA or
whatever is enabled. It's almost never correct out of the box.
Fixing your queries, index, and schema is 10-100x more effective than
dicking around with Mysql settings, custom compile, and hardware tweaks
unless you've done something really moronic.
mysqldump will not give consistent backups of Innodb. Use a slave, stop
the slave, take a backup preferably through LVM snapshotting so it
doesn't take forever, bring the slave back up and put it into rotation.
Stored procedures will make your life difficult. It's easy to say
code-1.3.2 is on production. It's hard to say code-1.3.2 and
stored-procs-1.1.1 are on production when the push process is different,
the teams are different, etc. You *can* manage it, but given a choice it
buys you very little and I never meet a DBA that didn't like to tweak
things directly. Hell I've meet far too many that needed to taught how
to checkin code.
kashani
