how can I detect deadlocks in MySQL? What could cause my application to hang when making a connection?

Question

I have an application that is having some issues with the database: suddenly it freezes when it tries to open a connection to the database (or executing a query, this is not clear). There is no error message. I am suspecting that there is some query blocking others and I am trying to figure out what is that. I used

SET profiling=1;

but when I execute:

show profiles;

I get only the queries I executed myself, not the application queries (the application and I are using the same user).

Calling

 SHOW FULL PROCESSLIST;

Returns a table with all process.

+-----+----------+---------------------+--------+---------+------+-------+-----------------------+
| Id  | User     | Host                | db     | Command | Time | State | Info                  |
+-----+----------+---------------------+--------+---------+------+-------+-----------------------+
|   8 | user     | <HOST>              | DBs    | Sleep   |    3 |       | NULL                  |
| 722 | user     | <HOST>              | DBs    | Sleep   | 8205 |       | NULL                  |
| 726 | user     | <HOST>              | DBs    | Sleep   | 8212 |       | NULL                  |
| 727 | user     | <HOST>              | DBs    | Sleep   | 8205 |       | NULL                  |
| 728 | user     | <HOST>              | DBs    | Sleep   | 8205 |       | NULL                  |
| 730 | user     | <HOST>              | DBs    | Sleep   | 7172 |       | NULL                  |
| 732 | user     | <HOST>              | DBs    | Sleep   | 8095 |       | NULL                  |
| 733 | user     | <HOST>              | DBs    | Sleep   | 8055 |       | NULL                  |
| 735 | user     | <HOST>              | DBs    | Sleep   | 8075 |       | NULL                  |
| 736 | user     | <HOST>              | DBs    | Sleep   | 8075 |       | NULL                  |
| 737 | user     | <HOST>              | DBs    | Sleep   | 8035 |       | NULL                  |
| 738 | user     | <HOST>              | DBs    | Sleep   | 8015 |       | NULL                  |
| 740 | user     | <HOST>              | DBs    | Sleep   | 7995 |       | NULL                  |
| 741 | user     | <HOST>              | DBs    | Sleep   | 7975 |       | NULL                  |
| 742 | user     | <HOST>              | DBs    | Sleep   | 7955 |       | NULL                  |
| 774 | user     | <HOST>              | DBs    | Sleep   | 5772 |       | NULL                  |
| 779 | user     | <HOST>              | DBs    | Sleep   | 6068 |       | NULL                  |
| 806 | user     | <HOST>              | DBs    | Query   |    0 | init  | SHOW FULL PROCESSLIST |
+-----+----------+---------------------+--------+---------+------+-------+-----------------------+

Calling

show engine innodb status

Returns a lot of transactions, some active, some not started. But no info about locked queries.

this query, that supposedly would give me information about blocked queries return an empty set:

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id,  b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM       information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b  ON   b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r  ON   r.trx_id = w.requesting_trx_id;

With all that information, can I have some guarantee that there is NO deadlock?

Would you have some guess about what could be happening so I can research about it?

Is there any way I can get more information about the processes?

I am new to DB administration and MySQL.

Thanks

Answer 1

Delays for Lock Waits

A lock wait is probably what you mean. You can monitor for lock waits by enabling the slow-query log, collecting a bunch of logs, and then reviewing it. Here's an example:

# Time: 140605 15:00:06
# User@Host: appuser[appuser] @  [127.0.0.1]  Id:    29
# Schema:   Last_errno: 0  Killed: 0
# Query_time: 0.011732  Lock_time: 0.000161  Rows_sent: 214  Rows_examined: 214  Rows_affected: 0
SET timestamp=1402005606;
SELECT ...blah blah blah...

You can see the field Lock_time above, which shows that the query was waiting for locks for 161 microseconds before it could begin executing. Then it took less than 12 milliseconds to execute (shown by Query_time).

It's ordinary for Lock_time to be really small, often it's even off the scale, so it just shows as 0.000000. If it's getting into hundreds of milliseconds or more, that's unusual. If it's into whole seconds, you're in trouble.

Note that the slow-query log entry won't be written to the log unless Query_time exceeds your config variable long_query_time -- even if the Lock_time is large. For some more discussion on this, see http://www.mysqlperformanceblog.com/2012/11/22/get-me-some-query-logs/

Delays for Connections

You also mentioned it could be a delay caused by acquiring the connection, before you have run any query. You need to track down whether this is the case. It should be easy in any application language to read the time before and after the connection to the database, and compare them to see how long it takes. Some frameworks even provide this type of application-level profiling per query (or you can do it yourself).

One common reason for delays on connection, for example, is that the MySQL Server is doing a reverse-DNS lookup to convert the incoming socket's IP address into a hostname. It does this so it can look up the hostname in the grant tables to figure out what privileges the user@host has. But if your DNS server is slow or overloaded, this can be slow. It's surprising that it would be more than a fraction of a second, but it's possible.

You can speed this up by setting the config variable skip_name_resolve. This means you cannot grant privileges to users based on hostname, you have to identify users by IP address only. Most production MySQL instances in the real world set skip_name_resolve.

There may also be other causes for slow connections, but first do some application profiling to determine conclusively whether it's the connection that is slow or a query.

---

P.S.: Lots of people say "deadlock" when they mean "lock wait." A deadlock is when two transactions are stuck waiting for each other's locks, and they cannot proceed. Deadlocks don't cause delays because InnoDB notices the cyclical dependency immediately and kills one of the transactions. You can see if you have had a deadlock in the SHOW ENGINE INNODB STATUS, in a section titled "LATEST DEADLOCK."