-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

Windy Wanderer
  • 1
  • 1
  • Hope this help : http://dba.stackexchange.com/a/27341/89077 – Yassine LD Jun 15 '16 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 '16 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 '16 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... – Windy Wanderer Jun 15 '16 at 20:42
  • Found something about the similar problem: https://www.drupal.org/node/1898204 Solution in here: https://groups.drupal.org/node/415883 . Trying to apply their solution. Seems to be working by now. – Windy Wanderer Jun 15 '16 at 21:36
  • Are these queries produced by Drupal or developers? The quoting of literals that should be integers doesn't make sense: WHERE (uid = '1') AND (nid = '40'). Try changing that to: WHERE (uid = 1) AND (nid = 40) – ypercubeᵀᴹ Jun 15 '16 at 22:39
  • These queries are produced by Drupal. For sure, I would not put number into quotes :) – Windy Wanderer Jun 15 '16 at 22:49
  • @WindyWanderer your processlist probably does not contain everything - the status shows one transaction TRANSACTION 3758562, ACTIVE 52 sec currently probably "sleeping" but still holding 4158 row lock(s) so it is quite probable the other statements just wait until it commits or otherwise ends. – jkavalik Jun 16 '16 at 14:30
  • @jkavalik 5 Yes, the holding transactions were connected with table "semaphore", causing deadlocks – Windy Wanderer Jun 16 '16 at 20:29
  • What version are you running? How much RAM? innodb_additional_mem_pool_size should not matter -- it was deprecated in 5.6.3. – Rick James Jun 27 '16 at 21:03
  • uid and nid are INTs; so quoting the numbers is not harmful. (VARCHAR vs non-quoted numbers is another issue.) – Rick James Jun 27 '16 at 21:05
  • What is the value of innodb_lock_wait_timeout? Since those 3 queries have exceeded the 50-second default, have you raised the value? Or is something else going on? – Rick James Jun 27 '16 at 21:07

1 Answers1

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'
Windy Wanderer
  • 1
  • 1