Solved

MySQL InnoDB Mutex Question / Issue

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database maintenance 36 108
Special characters in a TCPDF 4 27
SQL Server Express automatically execute SQL or SP 8 35
php hashing methods 3 16
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
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.:

733 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