3

I have a MariaDB (5.5.41) cluster made of 2 nodes configured as master-slave. All reads and writes are sent to the same node.

I have been investigating some deadlock issues for a few weeks.

On a regular basis, my PHP application returns Message: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction.

I used to be able to run SHOW ENGINE INNODB STATUS; and would see that last deadlock, but for some reason, after a small irrelevant configuration change (changing innodb_buffer_pool_instances from 1 to 19), and a reboot of both nodes, doing a SHOW ENGINE INNODB STATUS; will not show any deadlock.

However, if I connect with my mysql client and manually create transactions resulting in a deadlock, the status command does show the deadlock.

I tried playing innodb_print_all_deadlocks ON and OFF. Nothing shows in the mysql-error.log, except for my manually triggered deadlock.

Why are the deadlocks created by my PHP application not showing anymore?

liquidity
  • 408
  • 1
  • 7
  • 22

3 Answers3

1

I don't think I can answer your question directly, given my lack of access to your systems and the information given. However, here are some AWESOME tools I've used to get a better handle on all varieties of MySQL-derived databases I've been charged with administering.

InnoTop: https://github.com/innotop/innotop

Check out the "D" command in the innotop man page:

       D: InnoDB Deadlocks
           This mode shows the transactions involved in the last InnoDB
           deadlock.  A second table shows the locks each transaction held and
           waited for.  A deadlock is caused by a cycle in the waits-for
           graph, so there should be two locks held and one waited for unless
           the deadlock information is truncated. [...]

The "K" and "L" commands are also potentially relevant to you.

NOTE: innotop, to be fully useful, may need to change schema information and settings, and add a 'test' database to gather information. READ THE ENTIRE MAN PAGE to know what you are getting into before blindly changing your database. (Personally, I love the extra information the changes innotop unveils...)

Less directly relevant to your lock problem, but very useful, nonetheless:

The Percona Toolkit (formerly MAATKIT): https://www.percona.com/software/database-tools/percona-toolkit

Good luck!

Jesse Adelman
  • 968
  • 5
  • 15
  • Thanks for the tools, Jesse. I can't however accept the answer as it doesn't solve my exact issue. The "D" command uses "SHOW STATUS..." and as such won't work, the "L" command might be useful though. – liquidity Jun 05 '17 at 19:02
  • @liquidity The changes to the MySQL environment that InnoTop makes should do what is needed to get SHOW STATUS working for you. Are the deadlock tables created? – Jesse Adelman Jun 06 '17 at 02:50
  • if you read my question carefully, you'll notice that I do see some deadlocks: the ones I create manually. What innotop does is read the result of the "SHOW STATUS..." command every x seconds and print out the deadlock section. This assumes that "SHOW STATUS" works, which, in my case, doesn't. – liquidity Jun 06 '17 at 17:41
  • @liquidity I encourage you to read the man page for innotop. You are making assertions about something which you need more information before making conclusions. Better yet, just install it and try it. :) – Jesse Adelman Jun 06 '17 at 22:01
  • I also encourage you to read the manpage as it specifically indicates that innotop bases its output on "SHOW INNODB STATUS ...". Re-reading my original question carefully or ask for clarification if something's unclear might help too. – liquidity Jun 06 '17 at 22:27
  • Yes, it does rely on the output of that command. However, that command output is dependent on the configuration of your database and it's environment. That is what the man page shows innotop handles... – Jesse Adelman Jun 06 '17 at 22:39
  • I am trying to help you solve your own problem, since you have not given enough information here to allow us to. You cannot easily solve your problems if you refuse to use the tools made available for you to do so. – Jesse Adelman Jun 06 '17 at 22:42
  • My question indicates that I am able to see *some* deadlocks with "SHOW ENGINE INNODB STATUS", which would imply that my configuration is valid. But even then, "SHOW ENGINE INNODB STATUS" isn't normally bound to any specific configuration and should work out of the box. Whatever innotop does, it will not change the fact that the status command won't output the deadlocks generated by my php application. What information do you need that I am not providing? I'm happy to provide. – liquidity Jun 06 '17 at 22:57
0

It is quite puzzling that show engine innodb status is not giving you the required deadlock information. You can however check for the deadlocks by running mysqladmin debug, which logs all locks and also the LOCK TABLE locks which are not shown by show engine innodb status in this case.

These issues sometimes present in at wrong times and it wastes a lot of time. I personally use Monyog to monitor which also does this. You can try using its trial if nothing works.

Veer Abheek Singh Manhas
  • 113
  • 4
-1

Things to do in [mysqld] section of my.cnf/ini 

innodb_print_all_deadlocks=ON  # for error log documentation & be proactive in correcting.
log_error=(a valid filename)  # to write to  RTM
innodb_buffer_pool_instances=8  # from 19 would be adequate RAM overhead
Wilson Hauck
  • 426
  • 4
  • 10
  • Like my question says: I tried playing innodb_print_all_deadlocks ON and OFF. Nothing shows in the mysql-error.log, except for my manually triggered deadlock. As for innodb_buffer_pool_instances, this parameter is tuned according to my needs and has no bearings as far as deadlocks are concerned – liquidity Feb 13 '18 at 21:58
  • OK, to have predictability, in your my.cfg/ini [mysqld] section, have a single line addressing innodb_print_all_deadlocks=ON and SHOW STATUS needs to be SHOW GLOBAL STATUS; for any reasonably current version. SHOW ENGINE INNODB STATUS; will list the LAST deadlock. – Wilson Hauck Feb 13 '18 at 22:11
  • Your error.log will have each deadlock listed. – Wilson Hauck Feb 14 '18 at 00:57
  • Have you considered that using 19 innodb_buffer_pool_Instances may be the reason you can not SHOW ENGINE INNODB STATUS: at this time? – Wilson Hauck Feb 14 '18 at 01:16
  • I do not believe innodb_buffer_pool_instances set to 19 to be the issue as I am not aware of this setting having any impact on the "SHOW ENGINE..." command. As for innodb_print_all_deadlocks, like stated in my question, it had no effect as far as showing deadlocks is concerned. As for "SHOW ENGINE INNODB STATUS; will list the LAST deadlock", like stated in my question: it does not. Why? That's what I'd like to figure out. – liquidity Feb 14 '18 at 19:36
  • Would you consider posting complete text results of SHOW GLOBAL VARIABLES LIKE 'version%'; and SHOW GLOBAL VARIABLES LIKE 'innodb_print_all%'; ? Thanks – Wilson Hauck Feb 21 '18 at 19:27