Solved

MySQL InnoDB Mutex Question / Issue

Posted on 2013-11-25
5
726 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Read about achieving the basic levels of HRIS security in the workplace.
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

862 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now