[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

MySQL InnoDB Mutex Question / Issue

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
alon_sabi
Asked:
alon_sabi
  • 3
  • 2
1 Solution
 
PortletPaulCommented:
"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
 
alon_sabiAuthor Commented:
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
 
PortletPaulCommented:
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
 
alon_sabiAuthor Commented:
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
 
PortletPaulCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now