MySQL – Which Version to Use

MySQLmysql-5.1upgrade

For ages I have been using MySQL 5.1.37.

Recently I noticed that this version is no longer supported in repository, so I need to upgrade mysql.

I assume that changing the version should be complicated process with changes in my.cnf, and may be with the changes in the code.

Questions

Which version is the most stable and preferred?
Which version should I use?

Best Answer

I tend to follow the following rule for existing code-base:

If it's working fine, there is no need to upgrade unless there's a specific feature you are missing.

InnoDB does get some nice performance boosts in 5.5, but if your current use-case is not seeing any issues with your install, why upgrade?

If you ran some performance tests on a development server that indicates you are getting near your limit, you could try 5.5 to see if that helps any.

For future projects, I would definitely recommend 5.5, however.

STEP 01

Add this to my.cnf under the [mysqld] group header

[mysqld]
sql_mode = ''

STEP 02

mysql> SET GLOBAL sql_mode = '';

Restarting mysql is not required.

STEP 03 (Optional)

STEP 02 only affects incoming connections after the change. You may have to kill all current DB Connections. Let the app reconnect and it will begin with sql_mode as blank. While restarting mysql is not needed for incoming connections, you could just run service mysql restart instead of custom scripting the killing of DB Connections.

If you want to custom script the killing of your connections, please see some of my past posts for exmaples on how to kill many connections:

May 10, 2011 : kill all queries - MySQL
Jun 05, 2013 : Too Many database connections on Amazon RDS
Dec 25, 2013 : Mysql. Block/disconnect slow queries

STEP 04 (Optional, Last Resort)

If you would like to make code changes, the only change would be to run

SET sql_mode = '';

as the first command for the connection. Then, you could run your regular queries thereafter.

GIVE IT A TRY !!!

NOTE #1 : The first two steps are more than enough for your app if you close your connection right after running your query. If your connections are persistent, then you will need STEP 03.

NOTE #2 : If you are not allowed to change the configuration, you could skip STEP 01 and STEP 02 and just run STEP 03 and STEP 04.

Mysql – Which service to use for unoptimized, slow thesql database

Has been a while since I used MySQL, am I reading your graphic correctly? More than 30 million select statements per hour of uptime? Seems a bit excessive, especially if you are seeing around 150 users concurrently.

If that's correct then I'd be looking at your client's modifications as the probable cause before either trying config tweaks or throwing hardware at the problem.

Do you have a dev box which you can clone and a testing suite that can mimic many sessions? If so I would try removing the mods to diagnose.

I'm not familiar with CS Cart but I've seen SQL server performance killed by recursive views, cursor queries and/or bad joins. Have you any data on which queries are consuming all your processing?