MySQL 5.1 or 5.5?
Solution 1:
These days, you can use MySQL 5.5 now. I have 5.5.9 in production right now and it's running great.
I have successfully scaled MySQL 5.5 with simple circular replication amonst 3 DB Servers. Here is the layout of each DB server and what's running
- MySQL 5.5.9
- 2TB RAID10 SAS
- 192GB RAM
- One monolithic 162GB InnoDB Buffer Pool
- Dual HexaCore (that's right, 12 CPUs)
/etc/my.cnf has the following
[mysqld]
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=65536
innodb_buffer_pool_instances=1
innodb_bufer_pool_size=162G
Each MySQL Server contains
- 942 client (tenant) databases
- 935GB total data
- 95% of the buffer pool is full on all three servers
After 1 year, it still runs like a dream because of using MySQL 5.5 and getting InnoDB to engage all 12 CPUs.
So, I am quite happy with its performance thus far and so it the client.
Solution 2:
5.5 is a development release and not suitable for production. Unless you have a specific reason to, you should not even consider running 5.5 for any production use.
Solution 3:
Neither. Use percona 5.1 with xtra-db. Percona.com It's as fast as 5.5, and does lots of things MySQL should be doing. It's free, based on, and is binary compatible as a drop in replacement for MySQL. I've yet to have anything but good experiences running percona instead of MySQL. Make sure you set the default engine to innodb. Show engines; will show you are using xtradb instead of innodb.
Solution 4:
You should actually use mysql 5.0, the query planner does a better job with vbulletin than 5.5. We found several areas where 5.5's query planner resulted in worse performance overall than 5.0 and thus switched back.
http://www.makingitscale.com/2010/latest-mysql-query-planner-gone-looney.html