Re: Index causing lock up to 5 minutes
Hi Peter,
Thanks for your prompt feedback.
Please see below:
(Let me know know if I missed anything)
Total RAM is 64gb with 36gb allocated to the buffer pool.
> explain UPDATE OutQueue SET ConfigID = 434 WHERE BindID = 327 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,434) OR ConfigID IS NULL) LIMIT 100;
+------+-------------+----------+-------+------------------------------------+---------------------+---------+------+------+---------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+------------------------------------+---------------------+---------+------+------+---------------------------+
| 1 | SIMPLE | OutQueue | range | idx_OutQueue_2,idxBindAllocateWork | idxBindAllocateWork | 9 | NULL | 666 | Using where; Using buffer |
+------+-------------+----------+-------+------------------------------------+---------------------+---------+------+------+---------------------------+
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 35282485248
Dictionary memory allocated 303552
Buffer pool size 2097056
Free buffers 16392
Database pages 2080664
Old database pages 767732
Modified db pages 11385
Percent of dirty pages(LRU & free pages): 0.543
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 308466, not young 2051582
1.00 youngs/s, 0.25 non-youngs/s
Pages read 2171701, created 15743, written 989583
0.25 reads/s, 0.17 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2080664, unzip_LRU len: 0
I/O sum[192]:cur[0], unzip sum[0]:cur[0]
> show indexes from OutQueue;
+----------+------------+---------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| OutQueue | 0 | PRIMARY | 1 | ID | A | 21132 | NULL | NULL | | BTREE | | |
| OutQueue | 1 | idx_OutQueue_2 | 1 | StatusID | A | 11 | NULL | NULL | | BTREE | | |
| OutQueue | 1 | idx_OutQueue_2 | 2 | GatewayReference | A | 21132 | NULL | NULL | YES | BTREE | | |
| OutQueue | 1 | idx_OutQueue_3 | 1 | GatewayReference | A | 21132 | NULL | NULL | YES | BTREE | | |
| OutQueue | 1 | idx_OutQueue_3 | 2 | DestinationAddr | A | 21132 | NULL | NULL | | BTREE | | |
| OutQueue | 1 | idx_OutQueue_3 | 3 | SourceAddr | A | 21132 | NULL | NULL | YES | BTREE | | |
| OutQueue | 1 | idxBindAllocateWork | 1 | BindID | A | 29 | NULL | NULL | | BTREE | | |
| OutQueue | 1 | idxBindAllocateWork | 2 | ScheduledTime | A | 21132 | NULL | NULL | | BTREE | | |
| OutQueue | 1 | idxBindAllocateWork | 3 | StatusID | A | 21132 | NULL | NULL | | BTREE | | |
| OutQueue | 1 | idxBindAllocateWork | 4 | ConfigID | A | 21132 | NULL | NULL | YES | BTREE | | |
| OutQueue | 1 | idxBindAllocateWork | 5 | Priority | A | 21132 | NULL | NULL | | BTREE | | |
+----------+------------+---------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
11 rows in set (0.002 sec)
[root@smsserver paul]# ls -hl /var/lib/mysql/sms/OutQueue*
-rw-rw----. 1 mysql mysql 5.3K Oct 8 14:16 /var/lib/mysql/sms/OutQueue.frm
-rw-rw----. 1 mysql mysql 112M Oct 8 17:38 /var/lib/mysql/sms/OutQueue.ibd
SELECT database_name, table_name, index_name,
round(stat_value*@@innodb_page_size/1024/1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND index_name != 'PRIMARY'
ORDER BY 4 DESC;
+---------------+-----------------------------+--------------------------------------------+------------+
| database_name | table_name | index_name | size_in_mb |
+---------------+-----------------------------+--------------------------------------------+------------+
| sms | OutQueue | idx_OutQueue_3 | 5.02 |
| sms | OutQueue | idx_OutQueue_2 | 3.02 |
| sms | OutQueue | idxBindAllocateWork | 1.38 |
We currently have a few of these long-running queries at the moment:
581212 sms_user localhost sms 276 Query UPDATE OutQueue SET ConfigID = 422 WHERE BindID = 311 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,422) OR ConfigID IS NULL) LIMIT 1000
621127 sms_user localhost sms 276 Query UPDATE OutQueue SET StatusID = 2,SubmitTime = now(),ExpireTime = '2019-10-09 17:51:09',ConfigID = 377,SourceAddr = '+2782000000176310',GatewayReference = '935aca5659a54f3f83bb18eb0e0fd5ac' WHERE ID = 116579056
626259 sms_user localhost sms 270 Query UPDATE OutQueue SET ConfigID = 376 WHERE BindID = 47 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,376) OR ConfigID IS NULL) LIMIT 1000
626260 sms_user localhost sms 270 Query UPDATE OutQueue SET ConfigID = 49 WHERE BindID = 47 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,49) OR ConfigID IS NULL) LIMIT 1000
574633 sms_user localhost sms 236 Query UPDATE OutQueue SET ConfigID = 423 WHERE BindID = 311 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,423) OR ConfigID IS NULL) LIMIT 1000
621130 sms_user localhost sms 236 Query UPDATE OutQueue SET ConfigID = 420 WHERE BindID = 311 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,420) OR ConfigID IS NULL) LIMIT 1000
1 system us 0 Daemon InnoDB purge coordinator
2 system us 0 Daemon InnoDB purge worker
3 system us 0 Daemon InnoDB purge worker
4 system us 0 Daemon InnoDB purge worker
5 system us 0 Daemon InnoDB shutdown handler
629942 root localhost sms 0 Query show full processlist
630067 sms_user localhost sms 0 Query UPDATE Session SET StatusID = 4,Details = '<?xml version="1.0"?><details step="0" userentryid="0"><variable name="SessionVariable" value="277818874711570550145"/></details>',Comment = '',Tariff = '',Cost = '',
630068 sms_user localhost sms 0 Query UPDATE Session SET StatusID = 4,Details = '<?xml version="1.0"?><details step="0" userentryid="0"><variable name="SessionVariable" value="277308470721570550145"/></details>',Comment = '',Tariff = '',Cost = '',
630069 sms_user localhost sms 0 Query UPDATE Session SET StatusID = 4,Details = '<?xml version="1.0"?><details step="0" userentryid="0"><variable name="SessionVariable" value="277331920851570550145"/></details>',Comment = '',Tariff = '',Cost = '',
630079 sms_user localhost sms 0 Query UPDATE Session SET StatusID = 1,Details = '<?xml version="1.0"?><details step="0" userentryid="0"><variable name="SessionVariable" value="response"/></details>',Comment = '',Tariff = '',Cost = '',Reference =
The full query of the update on the session table is below. It takes milliseconds to complete a batch of 10-15. This table has a few million rows, understanding that only one where condition needs to be met.
| 633138 | sms_user | localhost | sms | Query | 0 | Commit | UPDATE Session SET StatusID = 1,Details = '<?xml version="1.0"?>
<details step="0" userentryid="0"><variable name="SessionVariable" value="response"/></details>
',Comment = '',Tariff = '',Cost = '',Reference = '',StepIndex = 0,Duration = 88,BytesSent = 973,BytesReceived = 18,Requests = 9 WHERE ID = 45085339