Re: [gentoo-user] Gentoo, MySQL, UltraMonkey Clusters

[kashani <kashani-list@badapple.net>]

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