2

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

2
  • 5.6 is not officially released ("GA" - general availability) yet Jan 4, 2013 at 11:04
  • 1
    Ok. edited my question. I think 5.5 should be the preferred choice.
    – Tush
    Jan 4, 2013 at 12:07

4 Answers 4

4

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

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:

5
  • Rolando, the linked post compares configured MySQL. The unconfigured MySQL tests would not provide any useful information, not even a single anecdotal data point Jan 6, 2013 at 15:51
  • @LaurynasBiveinis I need to look that post over again. In principle, if all versions were configured the same, the results are still valid. Thank you. Jan 6, 2013 at 20:47
  • @RolandoMySQLDBA, When you say that we should be using the most stable version, do you mean that we should be using 5.6 now?
    – Pacerier
    Dec 18, 2014 at 11:30
  • @Pacerier yes the most recent GA version. For MySQL 5.6, use 5.6.21 and higher. For MySQL 5.5, 5.5.40 and higher. Dec 18, 2014 at 12:59
  • @Pacerier please keep in mind that I wrote this answer on January 4, 2013. I wrote this answer comparing 5.1 to 5.5. If you look at the question's very first comment, someone commented that 5.6 was not GA. Therefore, the latest 5.5 was stable on January 4, 2013. Today, that would be MySQL 5.6.22. Dec 18, 2014 at 13:17
3

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.

5
  • stackoverflow.com/questions/9202825/mysql-5-5-perfomance This is the link to the post
    – Tush
    Jan 5, 2013 at 15:18
  • 1
    MySQL 5.1 is not EoL'ed as of today. 5.0 is. Jan 6, 2013 at 15:50
  • @LaurynasBiveinis - I stand corrected. Last time I checked last release was Sep 2012; I can see there has been a new release Dec 2012. However, I can verify that a couple bug reports of mine are known "not to be fixed in 5.1", but only in more recent versions (this has led me to the impressions 5.1 is EOL). If (some) 5.1 bug reports are not getting fixed, then I guess 5.1 is as good as EOL. Jan 7, 2013 at 6:13
  • 1
    I believe they-once they get around fixing community bugs-weigh the fix invasiveness for each active branch individually for the fix-no fix decisions. There is a bunch of bugs fixed in 5.6+ only, or even 5.7 only. Jan 7, 2013 at 7:09
  • I think 5.1 will go EOL when 5.6 is GA-released. Jan 7, 2013 at 7:14
1

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.

0

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)

1
  • INSERTs are going to be from different sessions. There can be 1000 Insert at max. That is the worst case. Approx how many INSERTs in 1s can be executed in a satisfactory manner?
    – Tush
    Jan 8, 2013 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.