1

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
Improve this question
6
  • This is a typical DB-admin question. It will go (be moved) to serverfault.com , where these questions (and the people that know the answers) tend to be. Commented May 30, 2010 at 6:35
  • see serverfault.com/questions/135486/… Commented May 30, 2010 at 6:50
  • quite strange - are you sure it's innodb? can you show table structure - columns, indexes and give idea how many rows do you have? Commented May 30, 2010 at 10:22
  • Can you reproduce the problem on a development system? Can you post a) the table schema, from SHOW CREATE TABLE, b) The output from SHOW FULL PROCESSLIST at the time of the problem (you may need to santitise this if it contains production data) Commented May 30, 2010 at 11:07
  • An InnoDB query cannot hold up MyISAM ones, are you sure that your application isn't doing something like LOCK TABLES? Additionally, how is your RAM tuned? It may be that you have failed to tune it adequately for the hardware. Mixed MyISAM / InnoDB servers are always a compromise, doing neither efficiently. Commented May 30, 2010 at 19:51

1 Answer 1

Reset to default
0

Please try using the following command and see if there are any issues.

SHOW ENGINE INNODB STATUS;

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.

Improve this answer
2
  • That's the weird thing, the other tables aren't innodb, just the images table. I'll post the status is a few minutes, I gotta get the glitch to happen again. Commented May 30, 2010 at 15:14
  • Please try increasing the "innodb_buffer_pool_size" and "innodb_additional_mem_pool_size" values - these seems to be quite low currently. Commented May 31, 2010 at 4:33

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.