MySQL configuration variables are a set of server system variables used to configure the operation and behavior of the server. In this blog post, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0. We will explain three different ways for setting the configuration variables based on your use-case. […]

The Question Recently, a customer asked us:

What would cause a node switch to fail in a Tungsten Cluster?

For example, we saw the following during a recent session where a switch failed:

cctrl> switch to db3 

SELECTED SLAVE: db3@alpha 
SET POLICY: MAINTENANCE => MAINTENANCE 
PURGE REMAINING ACTIVE SESSIONS ON CURRENT MASTER 'db1@alpha' 
PURGED A TOTAL OF 0 ACTIVE SESSIONS ON MASTER 'db1@alpha' 
FLUSH TRANSACTIONS ON CURRENT MASTER 'db1@alpha' 
Exception encountered during SWITCH. 
Failed while setting the replicator 'db1' role to 'slave' 
ClusterManagerException: Exception while executing command 'replicatorStatus' on manager 'db1' 
Exception=Failed to execute '/alpha/db1/manager/ClusterManagementHelper/replicatorStatus alpha db3' 
Reason= 
CLUSTER_MEMBER(true) 
STATUS(FAIL) 
+----------------------------------------------------------------------------+ 
|alpha | …

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 …

Have you ever spent a lot of time trying to locate where MySQL keeps some file? Here is a quick way to find all this information in one place.

The obvious way is through examining database options in my.cnf or looking at the output of SHOW GLOBAL VARIABLES. But not every path may be explicitly set in the configuration, in such case MySQL may assume some default, while other options may be set using relative paths.

A different approach is listing all files that a running database instance keeps open and searching for the required information there. I find that method by far the fastest whenever I need to learn any of such details.

garfield ~ # lsof -nc mysqld | grep -vE '(\.so(\..*)?$|\.frm|\.MY?|\.ibd|ib_logfile|ibdata|TCP)'
COMMAND   PID  USER   FD   TYPE      DEVICE  SIZE/OFF     NODE NAME
mysqld  30257 mysql  cwd    DIR       253,1      4096 25346049 /data/mysql
mysqld  30257 mysql  rtd    DIR       253,2 …

Do the operating systems kill your MySQL instances from time to time? Are some database servers swapping constantly? These are relatively common problems. Why? How to prevent them?

Memory allocation

When a running program needs some additional memory, it can typically allocate it dynamically with malloc() function. It finds an unused continuous block that is at least as large as the requested size, reserves as much as it needs, and returns a pointer to that space. No initialization of the memory contents is performed at the time. When malloc() returns NULL instead of a valid address, it is an information to the calling program that there wasn’t enough memory available and the call has failed to allocate anything. In such cases applications typically take appropriate actions to notify users about the problem and terminate some of their activity or completely shut down.

In Linux it can be a little bit …

Ronald Bradford’s recent warning to be sure to know your my.cnf sections reminded me of a similar issue that I ran into last summer, where putting the “group” option in both the [mysqld_safe] and [mysqld] directives resulted in a mostly silent problem.

I started noticing this in MySQL 5.1 and it affected both the official MySQL binary and the Percona binary. In trying to be conscientious, I had the following set:

[mysqld_safe]
user=mysql
group=mysql

[mysqld]
user=mysql
group=mysql


However, when the MySQL server started up, the error log showed

[Warning] option 'group_concat_max_len': unsigned value 0 adjusted to 4

This was obviously a problem, but …

Brian Moon suggest that community provided example my.cnf files would be a great thing to have on MySQLforge in this recent post: http://doughboy.wordpress.com/2008/05/06/example-mycnf-files/

I pulled out the "innodb heavy" config sample file and modified it with the standard settings that I typically start with when setting up a new InnoDB master. I've also modified the comments in the file a bit and have added some of my own too. I removed the sample slave configuration parameters (master-host, etc) because you should be using 'CHANGE MASTER TO'.

He suggested tagging such files with a 'mycnf' tag and very kindly tagged mine after I posted it :)

Feel free to share yours too and please feel free to make any comments about my configuration choices.

You can find it here (along with any other mycnf …