I've got a innodb table that gets a lot of reads and almost no writes (like, 1 write for every 400,000 reads approx).
I'm running into a pretty big problem though when I do INSERT into the table. MySQL completely locks up. It uses 100% cpu, and every single other table (in other databases even) have their statuses set to "Locked" until the INSERT is done. This is a big problem because MySQL stays locked up for up to 4 minutes.
I'm using version 5.1.47 (rpm from mysql.com).
Any ideas?
EDIT:
CREATE TABLE `images` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`posted_on` datetime NOT NULL,
`hash` binary(20) NOT NULL,
`struct` char(64) NOT NULL,
`category_id` smallint(5) unsigned NOT NULL,
`rating` smallint(6) NOT NULL,
`order_id` mediumint(8) unsigned NOT NULL,
`icon_type` enum('jpg','gif','png') NOT NULL,
`icon_width` smallint(5) unsigned NOT NULL,
`icon_height` smallint(5) unsigned NOT NULL,
`thumb_type` enum('jpg','gif','png') NOT NULL,
`thumb_width` smallint(5) unsigned NOT NULL,
`thumb_height` smallint(5) unsigned NOT NULL,
`medium_type` enum('jpg','gif','png') NOT NULL,
`medium_width` smallint(5) unsigned NOT NULL,
`medium_height` smallint(5) unsigned NOT NULL,
`full_type` enum('jpg','gif','png') NOT NULL,
`full_width` smallint(5) unsigned NOT NULL,
`full_height` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `struct` (`struct`),
UNIQUE KEY `hash` (`hash`),
KEY `category_id` (`category_id`,`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5118 DEFAULT CHARSET=latin1
Nothing special here..
So here's my processlist. Keep in mind, this problem ONLY happens when I do the INSERT into the images table, which is completely unrelated to the system_visitors_ips table.
mysql> show processlist;
+--------+----------------+----------------------+-------------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------------+----------------------+-------------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 2775 | xxxxxxx | xxx.xxx.xx.xxx:44108 | NULL | Binlog Dump | 62866 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 446944 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 22 | update | INSERT INTO images SET struct = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', |
| 446945 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 20 | end | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446946 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 20 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446947 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 17 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446948 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 446949 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 16 | end | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446950 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 14 | update | INSERT INTO data_bkt_r (interval_start, creative_id, zone_id, count) VALUES |
| 446951 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 14 | Locked | INSERT INTO data_bkt_r (interval_start, creative_id, zone_id, count) VALUES |
| 446952 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 13 | update | INSERT INTO data_bkt_m (interval_start, creative_id, zone_id, count) VALUES |
| 446953 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 12 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446954 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 11 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446955 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 9 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446956 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 9 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446957 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 8 | update | INSERT LOW_PRIORITY INTO system_visitors_referrals_hits SET referral_id = ' |
| 446958 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 8 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446959 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 8 | Locked | INSERT LOW_PRIORITY INTO system_visitors_referrals_hitsSET referral_id = ' |
| 446960 | xxxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxx | Query | 6 | end | INSERT INTO `wp_slim_browsers` ( `browser`, `version`, `platform`, `css_version` ) SELECT 'Firef |
| 446961 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 6 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446962 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 6 | Locked | INSERT LOW_PRIORITY INTO system_visitors_referrals_hits SET referral_id = ' |
| 446963 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 4 | Locked | INSERT LOW_PRIORITY INTO system_visitors_referrals_hitsSET referral_id = ' |
| 446964 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 4 | Locked | INSERT LOW_PRIORITY INTO system_visitors_referrals_hitsSET referral_id = ' |
| 446965 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 4 | Locked | INSERT INTO data_bkt_r (interval_start, creative_id, zone_id, count) VALUES |
| 446966 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 4 | Locked | INSERT INTO data_bkt_r (interval_start, creative_id, zone_id, count) VALUES |
| 446967 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 2 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446968 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 0 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446969 | xxxxxxxxxxx | localhost | xxxxxxxxxxx | Query | 0 | update | INSERT INTO xxxxxxxxxx SET cache='xxxxxxxxxxxxxxxxxx', date=NOW(), user_id='', ip |
+--------+----------------+----------------------+-------------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
27 rows in set (0.00 sec)
The INNODB status, which stays locked on the INSERT INTO images SET struct = 'xxxxxxxxxxx', posted_on = NOW(), category_id = 'xxx' query for 20+ seconds:
=====================================
100530 11:17:07 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 131393, signal count 125882
Mutex spin waits 0, rounds 1583362, OS waits 8189
RW-shared spins 225133, OS waits 108608; RW-excl spins 34921, OS waits 10644
------------
TRANSACTIONS
------------
Trx id counter 0 10511023
Purge done for trx's n:o < 0 10414917 undo n:o < 0 0
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1180911936
MySQL thread id 1851720, query id 51638294 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1087560000
MySQL thread id 1851511, query id 51632276 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1169463616
MySQL thread id 1851279, query id 51626132 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1180645696
MySQL thread id 1851202, query id 51624553 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1175054656
MySQL thread id 1851031, query id 51620527 localhost xxxxxxxxxxxxx
---TRANSACTION 0 10510698, not started, process no 22925, OS thread id 1202211136
MySQL thread id 1850961, query id 51618811 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1086970176
MySQL thread id 1849803, query id 51640040 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 10511022, ACTIVE 21 sec, process no 22925, OS thread id 1187301696, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1
MySQL thread id 1851783, query id 51639713 localhost xxxxxxxxxxxx update
INSERT INTO images SET struct = 'xxxxxxxxxxx', posted_on = NOW(), category_id = 'xxx'
--------
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
1278545 OS file reads, 48082 OS file writes, 37866 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 5, seg size 7,
13884 inserts, 13884 merged recs, 682 merges
Hash table size 17393, node heap has 14 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 11 3107465089
Log flushed up to 11 3107465089
Last checkpoint at 11 3107465089
0 pending log writes, 0 pending chkp writes
29690 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20704954; in additional pool allocated 989440
Dictionary memory allocated 430344
Buffer pool size 512
Free buffers 0
Database pages 498
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 2507340, created 36023, written 48970
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 22925, id 1156950336, state: sleeping
Number of rows inserted 676146, updated 41, deleted 14, read 587606968
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Best Answer
Please try using the following command and see if there are any issues.
Also, if every single table in other (innodb) databases are being locked as well then I think that there might be some issue with the innodb tablespace file which is shared by all innodb tables.