Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 - http://stackoverflow.com/questions/9202825/mysql-5-5-perfomance

share|improve this question
    
5.6 is not officially released ("GA" - general availability) yet –  a_horse_with_no_name Jan 4 '13 at 11:04
1  
Ok. edited my question. I think 5.5 should be the preferred choice. –  Tush Jan 4 '13 at 12:07

3 Answers 3

up vote 3 down vote accepted

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:

share|improve this answer
    
Rolando, the linked post compares configured MySQL. The unconfigured MySQL tests would not provide any useful information, not even a single anecdotal data point –  Laurynas Biveinis Jan 6 '13 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. –  RolandoMySQLDBA Jan 6 '13 at 20:47

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.

share|improve this answer
    
stackoverflow.com/questions/9202825/mysql-5-5-perfomance This is the link to the post –  Tush Jan 5 '13 at 15:18
1  
MySQL 5.1 is not EoL'ed as of today. 5.0 is. –  Laurynas Biveinis Jan 6 '13 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. –  Shlomi Noach Jan 7 '13 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. –  Laurynas Biveinis Jan 7 '13 at 7:09
    
I think 5.1 will go EOL when 5.6 is GA-released. –  ypercube Jan 7 '13 at 7:14

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)

share|improve this answer
    
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 '13 at 5:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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