which mysql version to use - 5.1 or 5.5?

Question

I am designing a new database. There are going to be around 1000 write queries per second. There may be as much as 10000 read per second.

Which MySQL version is recommended?

I came across a post on this site which says that 5.5 might be slower than 5.1 in some cases but some tuning will make it good enough. If tuning is done, then is it advisable to use 5.5? Or 5.1 performs better?

Edit

Here's the link to the post I referred to above - https://stackoverflow.com/questions/9202825/mysql-5-5-perfomance

Answer 1

There is no substitute of tuning MySQL. Left unconfigured, there are cases when even MySQL 4.1 outperforms MySQL 5.x on the same level playing field.

Here are my past posts on the subject

• Oct 05, 2011 : Query runs a long time in some newer MySQL versions
• Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
• Feb 08, 2012 : MySQL 5.5 perfomance (StackOverflow)
• Feb 22, 2012 : MySQL 5.1 vs MySQL 5.5 (5.1 twice as fast) (ServerFault)
• Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

Bottom Line

You should always use the most stable release of MySQL because there are just certain bug and features that are fixed that do not carry over to the next major release.

Note that

• MySQL 5.0 is up to 5.0.96, but the bugs that MySQL 5.5 Addressed still reside in 5.0.96
• MySQL 5.1 is up to 5.1.67, but the bugs that MySQL 5.5 Addressed still reside in 5.1.67

There are many exciting things that MySQL 5.5 features tuning for the following:

• Multicore Processing
  • May 26, 2011 : About single threaded versus multithreaded databases performance
  • Sep 12, 2011 : Possible to make MySQL use more than one core?
  • Sep 20, 2011 : Multi cores and MySQL Performance
• Semisynchronous Replication
  • Aug 05, 2011 : Is MySQL Replication Affected by a High-Latency Interconnect?
  • Oct 29, 2012 : Installing and enabling both master and slave semisynchronous plugins on same server
• Buffer Pool Instances : Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?

Answer 2

MySQL 5.1 has reached end of line. This means not more bug fixing. No further improvements.

5.5 is the stable version, and I can expect it to remain supported for some years to come. 5.5 introduces mostly performance and scale out improvements, and little feature improvement. Which is why I do not understand "that post on this site" -- if you provide a specific link I can relate. It's perfectly possible that some specific scenario would run faster/better on a particular version. But in general you can expect 5.5 to "behave" better.

Answer 3

One big gotcha for us with 5.1 was UTF-8 support. MySQL's "UTF8" is actually 3-byte UTF-8.

"UTF8mb4" encoding was added in 5.5.3 and supports 4-byte UTF-8 characters. (Emoji)

You're going to see a lot of projects making 5.5.3 a minimum supported version because of this.

Answer 4

5.1 and 5.5 (and even 4.0) can handle 1000 & 10000 simple queries.

Any version will croak on 100 & 1000 complex queries.

1000 individual INSERTs will be hard to achieve. A single (batched) INSERT of 1000 rows will perform at least 10x faster. Ditto for LOAD DATA.

InnoDB will outperform MyISAM in the scenario you described. XtraDB may perform even better.

Let's see the SHOW CREATE TABLE and the SELECTs.

Meanwhile, use 5.5, not 5.1. And, for better performance in the near future, plan on upgrading to 5.6 (Or MariaDB 5.5 or 10.0)