show slave status nolock blocks

[Gil Bahat]

Hi,

'show slave status nolock' locks on my system.

root@back006 [(none)]>show slave status nolock;
(...)

(... other thread)
root@back006 [(none)]>show full processlist;
+-----+-------------+-----------+----------+---------+-------+----------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+-----+-------------+-----------+----------+---------+-------+----------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
| 1 | system user | | NULL | Connect | 2620 | Queueing master event to the relay log | NULL | 0 | 0 | 0 |
| 2 | system user | | sightera | Connect | 15131 | Waiting for table level lock | INSERT INTO `session_assets` (`file_name`, `file_size`, `duration`, `content_type`, `create_ts`, `internal_uri`, `video_session_id`, `deleted`, `archived`, `chunks`, `chunk_info`, `asset_type`, `index`) VALUES ('rendererMetaData.data', 74472, 0, '', '2013-06-16 03:50:31', 's3://asset.sightera.com/9/b/ed8dacf86a354b5566f0d3ea9e71833d', 15915794, 0, 0, NULL, NULL, 'rendererMetaData', 1) | 0 | 0 | 0 |
| 147 | sightera | localhost | sightera | Query | 281 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `session_assets` | 70886 | 0 | 0 |
| 181 | root | localhost | NULL | Query | 313 | NULL | SHOW SLAVE STATUS NOLOCK | 0 | 0 | 0 |
| 182 | root | localhost | NULL | Query | 197 | NULL | show slave status nolock | 0 | 0 | 0 |
| 184 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0 | 0 | 0 |
+-----+-------------+-----------+----------+---------+-------+----------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
6 rows in set (0.00 sec)

this is halting the SNMP agent when the script is blocking, so it's a real mess.

please let me know if there's any additional info needed.

Server version: 5.5.31-30.3-log Percona Server (GPL), Release 30.3

Gil

[Valerii Kravchuk (valerii-kravchuk)]

I see this statement that is executed but slave's SQL thread is blocked:

| 2 | system user | | sightera | Connect | 15131 | Waiting for table level lock | INSERT INTO `session_assets` ...

Is it possible that some other session of those in your PROCESSLIST had executed FLUSH TABLES WITH READ LOCK and had NOT yet executed UNLOCK TABLES? Maybe this happens during a backup of some kind that you run, probably mysqldump? I ask because this statement:

| 147 | sightera | localhost | sightera | Query | 281 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `session_assets` | 70886 | 0 | 0 |

looks like generated by mysqldump in the process.

The output of:

ps aux | grep mysql

may help to find out if this is the case.

[Gil Bahat (gil-x)]

this indeed happened during mysqldump backup. the insert lock on the replication thread is expected. I am not sure what update does show slave status generate in the background though if any.

[Valerii Kravchuk (valerii-kravchuk)]

According to the manual, http://www.percona.com/doc/percona-server/5.5/reliability/show_slave_status_nolock.html, SHOW SLAVE STATUS NOLOCK should NOT lock on STOP SLAVE that is waiting for something. I do not see STOP SLAVE in the processl;ist above and I am not sure what is the behavior by design in NOLOCK in this case. I'll try to find out.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2974