Tiago Fael Matos
Reputation: 2077
MySQL updates getting stuck
I have a Mediacore-based website that is getting stuck due to an update query. Those update queries are running all the time, but sometimes (I believe, during high traffic hours), one of those updates just becomes stuck, locking all other queries:
245 mediacore_user localhost mediacore Query 36 Locked UPDATE media SET modified_on='2011-12-21 09:42:58', views=(media.views + 1) WHERE media.id = 1048
246 mediacore_user localhost mediacore Query 36 Locked UPDATE media SET modified_on='2011-12-21 09:42:58', views=(media.views + 1) WHERE media.id = 1048
248 mediacore_user localhost mediacore Query 36 Updating UPDATE media SET modified_on='2011-12-21 09:42:58', views=(media.views + 1) WHERE media.id = 1048
249 mediacore_user localhost mediacore Query 36 Locked UPDATE media SET modified_on='2011-12-21 09:42:58', views=(media.views + 1) WHERE media.id = 1048
253 mediacore_user localhost mediacore Query 36 Locked UPDATE media SET modified_on='2011-12-21 09:42:58', views=(media.views + 1) WHERE media.id = 1048
258 mediacore_user localhost mediacore Query 30 Locked UPDATE media SET modified_on='2011-12-21 09:43:04', views=(media.views + 1) WHERE media.id = 416
268 mediacore_user localhost mediacore Query 3 Locked UPDATE media SET modified_on='2011-12-21 09:43:31', views=(media.views + 1) WHERE media.id = 1048
269 mediacore_user localhost mediacore Query 3 Locked UPDATE media SET modified_on='2011-12-21 09:43:31', views=(media.views + 1) WHERE media.id = 1048
270 mediacore_user localhost mediacore Query 3 Locked UPDATE media SET modified_on='2011-12-21 09:43:31', views=(media.views + 1) WHERE media.id = 1048
271 mediacore_user localhost mediacore Query 3 Locked UPDATE media SET modified_on='2011-12-21 09:43:31', views=(media.views + 1) WHERE media.id = 1048
272 mediacore_user localhost mediacore Query 1 Locked UPDATE media SET modified_on='2011-12-21 09:43:33', views=(media.views + 1) WHERE media.id = 1048
If I kill the "Updating" connection, another one gets stuck, and so on, until there are only about 3 queries left locked.
Any ideas?
Media table:
CREATE TABLE `media` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(8) DEFAULT NULL,
`slug` varchar(50) NOT NULL,
`podcast_id` int(11) DEFAULT NULL,
`reviewed` tinyint(1) NOT NULL,
`encoded` tinyint(1) NOT NULL,
`publishable` tinyint(1) NOT NULL,
`created_on` datetime NOT NULL,
`modified_on` datetime NOT NULL,
`publish_on` datetime DEFAULT NULL,
`publish_until` datetime DEFAULT NULL,
`title` varchar(255) NOT NULL,
`subtitle` varchar(255) DEFAULT NULL,
`description` text,
`description_plain` text,
`notes` text,
`duration` int(11) NOT NULL,
`views` int(11) NOT NULL,
`likes` int(11) NOT NULL,
`popularity_points` int(11) NOT NULL,
`author_name` varchar(50) NOT NULL,
`author_email` varchar(255) NOT NULL,
`dislikes` int(11) NOT NULL,
`popularity_likes` int(11) NOT NULL,
`popularity_dislikes` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `slug` (`slug`),
KEY `podcast_id` (`podcast_id`),
CONSTRAINT `media_ibfk_1` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1049 DEFAULT CHARSET=utf8
INNODB Status after the problem happened:
=====================================
111221 12:12:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 78, signal count 78
Mutex spin waits 0, rounds 453, OS waits 17
RW-shared spins 122, OS waits 61; RW-excl spins 0, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 1115749
Purge done for trx's n:o < 0 1115735 undo n:o < 0 0
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 1115747, not started, process no 15360, OS thread id 140486827099904
MySQL thread id 111, query id 11647 localhost mediacore_user
---TRANSACTION 0 0, not started, process no 15360, OS thread id 140486827898624
MySQL thread id 106, query id 11651 localhost root
show innodb status
---TRANSACTION 0 1115709, not started, process no 15360, OS thread id 140486826567424
MySQL thread id 103, query id 10874 localhost mediacore_user
---TRANSACTION 0 1115731, not started, process no 15360, OS thread id 140486792349440
MySQL thread id 102, query id 11517 localhost mediacore_user
---TRANSACTION 0 1115668, not started, process no 15360, OS thread id 140486792881920
MySQL thread id 101, query id 10067 localhost 127.0.0.1 a7user
---TRANSACTION 0 1115737, not started, process no 15360, OS thread id 140486791816960
MySQL thread id 95, query id 11578 localhost mediacore_user
---TRANSACTION 0 1115738, not started, process no 15360, OS thread id 140486827366144
MySQL thread id 87, query id 11577 localhost mediacore_user
---TRANSACTION 0 1115744, not started, process no 15360, OS thread id 140486827632384
MySQL thread id 61, query id 11606 localhost mediacore_user
---TRANSACTION 0 1115746, not started, process no 15360, OS thread id 140486826301184
MySQL thread id 22, query id 11633 localhost mediacore_user
---TRANSACTION 0 1115748, ACTIVE 1 sec, process no 15360, OS thread id 140486826833664
mysql tables in use 1, locked 1
MySQL thread id 55, query id 11650 localhost mediacore_user Table lock
UPDATE media SET modified_on='2011-12-21 12:12:08', dislikes=10 WHERE media.id = 572
Trx read view will not see trx with id >= 0 1115749, sees < 0 1115740
---TRANSACTION 0 1115743, ACTIVE 14 sec, process no 15360, OS thread id 140486826034944
mysql tables in use 1, locked 1
MySQL thread id 23, query id 11601 localhost mediacore_user Table lock
UPDATE media SET modified_on='2011-12-21 12:11:55', views=(media.views + 1) WHERE media.id = 1048
Trx read view will not see trx with id >= 0 1115744, sees < 0 1115740
---TRANSACTION 0 1115742, ACTIVE 14 sec, process no 15360, OS thread id 140486825502464
mysql tables in use 1, locked 1
MySQL thread id 90, query id 11599 localhost mediacore_user Table lock
UPDATE media SET modified_on='2011-12-21 12:11:55', views=(media.views + 1) WHERE media.id = 1048
Trx read view will not see trx with id >= 0 1115743, sees < 0 1115740
---TRANSACTION 0 1115741, ACTIVE 14 sec, process no 15360, OS thread id 140486825768704 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368, 1 row lock(s)
MySQL thread id 73, query id 11596 localhost mediacore_user Updating
UPDATE media SET modified_on='2011-12-21 12:11:55', views=(media.views + 1) WHERE media.id = 1048
Trx read view will not see trx with id >= 0 1115742, sees < 0 1115740
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1025 n bits 88 index `PRIMARY` of table `mediacore`.`media` trx id 0 1115741 lock_mode X locks rec but not gap waiting
Record lock, heap no 20 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
0: len 4; hex 80000418; asc ;; 1: len 6; hex 00000011065c; asc \;; 2: len 7; hex 00000003c20d5b; asc [;; 3: len 5; hex 766964656f; asc video;; 4: len 30; hex 617273656c696f2d7061746f2d64652d63617276616c686f2d686f6d656e; asc arselio-pato-de-carvalho-homen;...(truncated); 5: len 4; hex 80000013; asc ;; 6: len 1; hex 81; asc ;; 7: len 1; hex 81; asc ;; 8: len 1; hex 81; asc ;; 9: len 8; hex 8000124a82217b0b; asc J !{ ;; 10: len 8; hex 8000124a8230e083; asc J 0 ;; 11: len 8; hex 8000124a82220ef0; asc J " ;; 12: SQL NULL; 13: len 30; hex 417273c3a96c696f205061746f2064652043617276616c686f20686f6d65; asc Ars lio Pato de Carvalho home;...(truncated); 14: SQL NULL; 15: len 30; hex 3c703e43656e74726f206465204e6575726f6369c3aa6e63696173206520; asc <p>Centro de Neuroci ncias e ;...(truncated); 16: len 30; hex 43656e74726f206465204e6575726f6369c3aa6e6369617320652042696f; asc Centro de Neuroci ncias e Bio;...(truncated); 17: SQL NULL; 18: len 4; hex 80000000; asc ;; 19: len 4; hex 80000099; asc ;; 20: len 4; hex 80000000; asc ;; 21: len 4; hex 80000000; asc ;; 22: len 3; hex 554356; asc UCV;; 23: len 9; hex 7563764075632e7074; asc [email protected];; 24: len 4; hex 80000000; asc ;; 25: len 4; hex 80000000; asc ;; 26: len 4; hex 80000000; asc ;;
------------------
---TRANSACTION 0 1115740, ACTIVE 14 sec, process no 15360, OS thread id 140486792083200
2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 97, query id 11597 localhost mediacore_user Table lock
SELECT 1
FROM media_fulltext
LIMIT 1
Trx read view will not see trx with id >= 0 1115741, sees < 0 1115741
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
187 OS file reads, 469 OS file writes, 298 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 2212699, node heap has 9 buffer(s)
0.17 hash searches/s, 0.17 non-hash searches/s
---
LOG
---
Log sequence number 0 148655397
Log flushed up to 0 148655397
Last checkpoint at 0 148655397
0 pending log writes, 0 pending chkp writes
177 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1225697720; in additional pool allocated 11846912
Dictionary memory allocated 203840
Buffer pool size 65536
Free buffers 65299
Database pages 228
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 225, created 3, written 259
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
6 read views open inside InnoDB
Main thread process no. 15360, id 140486801540864, state: sleeping
Number of rows inserted 13, updated 85, deleted 2, read 281971
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.17 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
EDIT: Somehow, some of the updates are not unlocking the table after they commit. I watched the queries for quite some time and saw that after 50 seconds, the lock is freed and one update is executed. I've changed the innodb_lock_wait_timeout to 5 and I saw the same thing, only with intervals of 5 seconds instead of 50. But once it gets only a few updates left locked, they're all executed instantaneously... it's a very strange behavior...
Upvotes: 1
Views: 2876
Answers (2)
Felix Schwarz
Reputation: 3078
A bit late but I'd like to add some more information: From my observation this happens if you have the full-text index enabled. In that case there seems to be a bad interaction in MySQL involving InnoDB (media table) and MyISAM (full-text index) locking.
I never saw the issue if MediaCore's full-text triggers were not installed.
Upvotes: 1
Manigandan Arjunan
Reputation: 2265
I think it is because of the large number of queries at a time. Try using mysql index with your fields. It will speed up the performance.
Upvotes: 0
Related Questions
- How to resolve update lock issue in MySQL
- MySql Update takes very long
- MySQL UPDATE Hangs with No Error
- Innodb update locking
- Mysql updates takes long time and getting stuck
- Mysql SELECT FOR UPDATE - strange issue
- MySql hangs on update and insert
- Update query is locked
- MySQL UPDATE operations on InnoDB occasionally timeout
- MySQL UPDATE statement times out