Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com May 30 '10 at 9:22

This question came from our site for professional and enthusiast programmers.

    
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. –  lexu May 30 '10 at 6:35
    
see serverfault.com/questions/135486/… –  lexu May 30 '10 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? –  pQd May 30 '10 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) –  MarkR May 30 '10 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. –  MarkR May 30 '10 at 19:51
show 1 more comment

1 Answer

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.

share|improve this answer
    
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. –  Ian May 30 '10 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. –  Ramesh Tabarna May 31 '10 at 4:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.