0

I've been frustrated by a problem with MySQL going CPU bound and effectively locking up. I'm unsure whether it's a bug, quirk, or I'm hitting some resource limit. I believe I have narrowed down the trigger to the following:

  • Multiple clients can concurrently insert their data into the same table without issue. For example, when monitoring 4 clients inserting their own individual sets of data, top shows 4 active MySQL threads at 20% to 60% CPU each.

  • If one client is restarted, and it tries to insert data that already exists in the table, the server falls to its knees, and all activity is confined to a single thread at 100%. The console is fully responsive, but table operations (including those involving other tables) get stuck in an "update" state, seemingly for an indefinite period.

  • Although this is a large table (1.3TB), this behaviour happens even when the table is empty, so it does not appear to be related to size.

Here's an example showing the oldest insert has been updating for 2529 seconds, with all subsequent table operations by other clients also frozen:

-----+---------+------+----------+---------------------------
     | Command | Time | State    | Info
-----+---------+------+----------+---------------------------
ata  | Query   | 2528 | update   | INSERT IGNORE INTO rawd...
hain | Query   | 2505 | updating | DELETE FROM pre_..........
hain | Query   |    0 | starting | show processlist
ata  | Query   | 2529 | update   | INSERT IGNORE INTO rawd...
ata  | Query   | 1537 | update   | INSERT IGNORE INTO rawd...
hain | Query   |  859 | update   | INSERT IGNORE INTO pre_...
hain | Query   |  534 | update   | INSERT IGNORE INTO pre_...
hain | Query   |  232 | update   | INSERT IGNORE INTO pre_...
-----+---------+------+----------+---------------------------

With some experimenting I've determined that inserting fewer records per query "solves" the problem, but that does not explain why the initial insert works at higher record counts, or why MySQL enters a death spiral around (but not consistently at) a certain number of records per "duplicated insert".

To further demonstrate, here is the 'top' output after table dropped and recreated, with 4 clients inserting unique data:

   THR USERNAME    PRI NICE   SIZE    RES STATE    C   TIME    WCPU COMMAND
101527 mysql        52    0    13G  9872M select   7   5:28  45.70% mysqld{mysqld}
101528 mysql        49    0    13G  9872M select  11   6:31  22.25% mysqld{mysqld}
101529 mysql        52    0    13G  9872M select   1   2:31  60.06% mysqld{mysqld}
101530 mysql        86    0    13G  9872M CPU3     3  11:26  42.46% mysqld{mysqld}

Now one client is stopped then restarted, causing it to insert duplicate data. Note the same threadids; 3 threads fall to 0.00% CPU, 1 goes to 100.00%:

   THR USERNAME    PRI NICE   SIZE    RES STATE    C   TIME    WCPU COMMAND
101527 mysql        20    0    13G    11G uwait    2   6:06   0.00% mysqld{mysqld}
101528 mysql        20    0    13G    11G uwait    3   6:59   0.00% mysqld{mysqld}
101529 mysql        20    0    13G    11G uwait    1   3:09   0.00% mysqld{mysqld}
101530 mysql       102    0    13G    11G CPU4     4  12:30 100.00% mysqld{mysqld}

Table format (note that this may also happen at some level with other tables, but this one DEFINITELY causes the problem)

 CREATE TABLE `rawdata` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` bigint(20) DEFAULT NULL,
  `exchange` tinyint(4) DEFAULT NULL,
  `currency1` smallint(6) DEFAULT NULL,
  `currency2` smallint(6) DEFAULT NULL,
  `dtype` tinyint(4) DEFAULT NULL,
  `dsubtype` tinyint(4) DEFAULT NULL,
  `dindex` smallint(6) DEFAULT NULL,
  `price` decimal(18,8) DEFAULT NULL,
  `volume` decimal(18,8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `timestamp_2` (`timestamp`,`exchange`,`currency1`,`currency2`,`dtype`,`dsubtype`,`dindex`),
  KEY `timestamp` (`timestamp`),
  KEY `currency1` (`currency1`,`currency2`)
) ENGINE=InnoDB AUTO_INCREMENT=9589145681 DEFAULT CHARSET=latin1

Sample data insert showing one set of records. I'd previously had clients inserting 1000 records per query, and also tested it at 10000 per query, but to avoid the CPU stall that number needs to be below about 400.

INSERT IGNORE INTO rawdata (timestamp, exchange, currency1, currency2, dtype, dsubtype, dindex, price, volume) VALUES (1571495278000,1,1,6,1,1,1,0.00003774,12.38935500), [......]

System config:

  • E5-2620 CPU (6 cores/12 threads)
  • 32GB ECC RAM
  • 2 x 3TB HDDs in ZFS mirror, 16GB NVMe SSD ZFS SLOG, 512GB NVMe SSD ZFS L2ARC
  • MySQL 5.7.27
  • FreeBSD 12.0

Thank you in advance for any insight or suggestions into this behaviour.

  • 1
    Can you post your my.cnf configuration? – Jesus Uzcanga Oct 23 '19 at 14:21
  • Additional information request. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Dec 19 '19 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.