当前位置: 动力学知识库 > 问答 > 编程问答 >

locking - 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...

网友答案:

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.

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: