-1

The situation is the following. Two InnoDB tables, one has 2 records, another one - 281 - small ones, insert and update requests may last up to two (!) minutes. Dropped db, created again. The situation repeats in a couple of hours. The tables are history and semaphore from Drupal Commerce (Drupal 7).

Innodb settings:

innodb_fast_shutdown = 0
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 1280M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 0
innodb_additional_mem_pool_size = 4M
innodb_lock_wait_timeout = 180

Tables:

CREATE TABLE `history` (
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT 'The users.uid that read the node nid.',
  `nid` int(11) NOT NULL DEFAULT '0' COMMENT 'The node.nid that was read.',
  `timestamp` int(11) NOT NULL DEFAULT '0' COMMENT 'The Unix timestamp at which the read occurred.',
  PRIMARY KEY (`uid`,`nid`),
  KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A record of which users have read which...';

CREATE TABLE `semaphore` (
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Primary Key: Unique name.',
  `value` varchar(255) NOT NULL DEFAULT '' COMMENT 'A value for the semaphore.',
  `expire` double NOT NULL COMMENT 'A Unix timestamp with microseconds indicating when the semaphore should expire.',
  PRIMARY KEY (`name`),
  KEY `value` (`value`),
  KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Table for holding semaphores, locks, flags, etc. that...';

Upd.

show processlist;
+------+---------------+---------------------+----------+---------+------+----------+--------------------------------------------------------------------------------------------------+----------+
| Id   | User          | Host                | db       | Command | Time | State    | Info                                                                                             | Progress |
+------+---------------+---------------------+----------+---------+------+----------+--------------------------------------------------------------------------------------------------+----------+                                                                                      |    0.000 |
| 7996 | myuser       | localhost           | mydb | Query   |  129 | updating | DELETE FROM semaphore
WHERE  (value = '584023605761bad5c4a925.89704284')        |    0.000 |
| 8003 | myuser       | localhost           | mydb | Query   |  112 | updating | UPDATE history SET timestamp='1466022629'
WHERE ( (uid = '1') AND (nid = '40') ) |    0.000 |                                                                          |    0.000 |
| 8018 | myuser       | localhost           | mydb | Query   |   53 | updating | DELETE FROM semaphore
WHERE  (value = '6705735415761bb221d33c0.93445495')       |    0.000 |
+------+---------------+---------------------+----------+---------+------+----------+--------------------------------------------------------------------------------------------------+----------+

Upd2.

explain  UPDATE history SET timestamp='1466022629' WHERE ( (uid = '1') AND (nid = '40') );
+------+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table                   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | history | range | PRIMARY,nid   | PRIMARY | 8       | NULL |    1 | Using where |
+------+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

Engine Inndb Status

12
  • Hope this help : dba.stackexchange.com/a/27341/89077
    – Yassine LD
    Jun 15, 2016 at 12:49
  • 4 instances for 1.2GB buffer pool seems excessive, 1 or 2 should be enough. innodb_additional_mem_pool_size is set twice so probably the smaller value is used.
    – jkavalik
    Jun 15, 2016 at 18:54
  • Post the query. EXPLAIN it if your version allows, otherwise EXPLAIN an equivalent SELECT same conditions and select the rows otherwise updated). Get show full processlist; and show engine innodb status\G during one such occurence.
    – jkavalik
    Jun 15, 2016 at 18:56
  • Updated the description. Just removed the innodb_additional_mem_pool_size that was pointing to 4M, nleft only for 20M, haven't seen that, thanks. innodb_buffer_pool_instance set to 2. Checking the results... Jun 15, 2016 at 20:42
  • Found something about the similar problem: drupal.org/node/1898204 Solution in here: groups.drupal.org/node/415883 . Trying to apply their solution. Seems to be working by now. Jun 15, 2016 at 21:36

1 Answer 1

0

The solution appears to be the following.

Table 'semaphore' (innodb) was locking everything. Moving it from innodb to memory fixed the situation.

ALTER TABLE semaphore ENGINE = MEMORY;
ALTER TABLE semaphore DROP PRIMARY KEY;
ALTER TABLE semaphore ADD PRIMARY KEY (name, value) USING BTREE;
ALTER TABLE semaphore ADD UNIQUE name (name) USING BTREE;
ALTER TABLE semaphore DROP INDEX value;
ALTER TABLE semaphore ADD INDEX value (value) USING BTREE;
ALTER TABLE semaphore DROP INDEX expire;
ALTER TABLE semaphore ADD INDEX expire (expire) USING BTREE;

Also there were some additional changes made:

SET SESSION tx_isolation='READ-COMMITTED'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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