Solved

MySQL InnoDB Mutex Question / Issue

Posted on 2013-11-25
5
763 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 49

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 49

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 49

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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