Solved

MySQL InnoDB Mutex Question / Issue

Posted on 2013-11-25
5
735 Views
Last Modified: 2013-12-02
Hello,

I've been using a third party service called NewRelic to monitor a lot of server related metrics, and recently installed their MySQL plugin. I've been on their support for some time to help me understand why the plugin was causing so much RAM usage and we discovered that the plugin was trying to execute SHOW ENGINE INNODB MUTEX to pull some metrics.

The issue here is the table has over a million records in it, and on some of our database servers over a few million.

My question is what exactly is mutex used for, and is records in the low millions healthy?

Here's a my.cnf file on one of my database servers that has over a million mutex records since that seems to be what people ask for first:

[mysqld]
datadir                        = /data/mysql
socket                        = /data/mysql/mysql.sock
user                        = mysql

default_storage_engine            = InnoDB      # MyISAM *SHOULD NOT BE USED* in production
sql_mode                  = NO_AUTO_CREATE_USER

innodb_buffer_pool_size            = 4G
innodb_log_file_size            = 25M
innodb_log_files_in_group      = 4
innodb_flush_log_at_trx_commit      = 2
innodb_file_per_table            = 1            # Will want this in place for 5.6


server_id                  = 202
binlog_format                  = MIXED
log_bin                        = slave02-log-bin
relay_log                  = master02-relay-bin
log_slave_updates            = 1
slave_compressed_protocol      = 1
expire_logs_days            = 7

# **********************************
# removed for EE
# **********************************

max_allowed_packet=256M
skip-name-resolve

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

ssl-ca = ** removed for EE
ssl-cert = ** removed for EE
ssl-key = ** removed for EE

default-character-set = utf8
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

# ***********************************

[mysqld_safe]
log-error                  = /var/log/mysqld.log
pid-file                  = /var/run/mysqld/mysqld.pid

# **********************************
# removed for EE
# **********************************

[client]
ssl-ca= ** removed for EE
ssl-cert = ** removed for EE
ssl-key = ** removed for EE
default-character-set=utf8

[mysql]
default-character-set=utf8
# ***********************************

I have removed some values for security purposes. Some notes, this is a database running in a master-master setup.

Thanks!
0
Comment
Question by:alon_sabi
  • 3
  • 2
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39679630
"Mutex" is short for "mutual exclusion" and this is a term applied to methods of ensuring the 2 critical operations are not performed on a shared resource at the same time. You might prefer to think of it as "locking"

see: http://en.wikipedia.org/wiki/Mutual_exclusion
and: http://www.thinkingparallel.com/2006/09/09/mutual-exclusion-with-locks-an-introduction/

The latter reference isn't specifically about database locks but does cover both locking and "spinlocks". Note the term "spin" is used by  SHOW ENGINE INNODB MUTEX in the columns spin_waits and spin_rounds.

For details on SHOW ENGINE INNODB MUTEX output columns see: http://dev.mysql.com/doc/refman/5.7/en/show-engine.html

so, in brief, the plugin appears to be monitoring mutex to gather metrics regarding locks.

Millions of rows in tables isn't that uncommon
0
 

Author Comment

by:alon_sabi
ID: 39679641
Could this topic be related somehow to the following error we're experiencing?

Error Message: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction


We've been getting an increasing amount of these errors lately.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39679734
I don't know the answer to that I'm afraid.

If you stopped running the plugin does the number of deadlocking incidents reduce?

Sounds like the problem may have existed before using the plugin.
0
 

Author Comment

by:alon_sabi
ID: 39681580
Sorry, I was unclear. The issue isn't the plugin. We merely discovered the fact that we have a huge amount of Mutex results by using the plugin. That SQL error has been happening for a long time to us. The plugin doesn't increase or decrease the amount of deadlocks.

I googled a bit and when searching for InnoDB Mutex, that sql error showed up in one of the top results and I realized we're also getting that error. However I still don't know for sure if they're related or if it's possible to sort of "restart" the mutex table and have it rebuild itself or something.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39682502
Well then, yes, the topic (mutex) is related to that error message but I don't think there's a "restart" unless you re-start the dbms itself. Normally these locking issues stem from competing requirements (e.g. deletes and inserts)  - and solving this isn't just a re-start but involves re-evaluating the competition over the shared resource.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
centos linux 65 137
How to join on ID, with prefix? 15 60
Foxpro errors 4 34
number in printf 13 27
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question