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