Inspired by Peter Zaitsev’s post for MySQL 5.6, I decided to do a comparison between default configuration of MySQL 5.6 and 5.7. And I gotta tell you, some of the changes I found will have a huge impact on performance so make sure to get yourself familiar with them before you upgrade, even if you’re not otherwise running with a default configuration.

BEGIN

Interestingly, I hit the first roadblock before I could even start. It seems you can no longer extract the same information from the information_schema as in MySQL 5.6, and I had to use performance_schema instead:

mysql> insert into compare.vars57 (variable_name, variable_value) 
   ->  select VARIABLE_NAME, VARIABLE_VALUE from INFORMATION_SCHEMA.GLOBAL_VARIABLES;
ERROR 3167 (HY000): The …

When MySQL becomes too slow (or too unstable), temptation usually is to tweak the MySQL configuration file. Indeed, it’s a good place to start. But if you ever looked at the available configuration options, you know things can get messy – MySQL now has over 450 configuration variables for your consideration, that are not classified in any way, and neither of them are included in the stock my.cnf. It’s hard to know where to start!

I’m hoping that this blog post will help you overcome the anxiety of tuning MySQL, whether you’re setting up a new server, or tuning an already running server for better performance.

Don’t do it the way rookies do it

During the last 9 years I’ve spent at Percona working as a MySQL performance and scalability consultant, I found that customers often use the trial and error approach when tuning MySQL configuration: they change a few things and check if it …

Tweet

If your MySQL server has hundreds of clients (applications) and tens of thousands of queries per second,  MySQL default network settings may NOT be for you.  Network performance is not often a significant factor in the performance of MySQL.  That said, there are things to consider.

If you are building new applications make these changes now.  Developer expectations are hard to change.  My example below will break your application if  developers open a database connections and then spend ten minutes playing with their play doe before making a query.  If you have working applications …