1

I have a server where MySQL is periodically taking up 11 of the 12 CPU cores. Once this starts it doesn't end until I restart MySQL. At least, I haven't let it run like this for more than a half hour since it takes down a live website. I haven't been able to notice any patterns for when or why this happens. It's not at times of particularly high traffic and the queries being run are not abnormal to what is normally run.

Here's what I get from PROCESS LIST;

mysql> show processlist;
+---------+------------+----------------------+-----------------+-------------+--------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id      | User       | Host                 | db      | Command     | Time   | State                                                                 | Info                                                                                                 |
+---------+------------+----------------------+-----------------+-------------+--------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 1001328 | replicator | example.linode:59815 | NULL    | Binlog Dump | 316247 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                                                                                 |
| 2356160 | example    | example:33965        | example | Query       |    398 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2356492 | example    | example:34727        | example | Query       |    318 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2361305 | example    | example:46892        | example | Query       |    771 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
[... 198 more lines like the one above, "Copying to tmp table on disk" ...]
| 2362323 | example    | example:49232        | example | Query       |    695 | Copying to tmp table on disk                                          | SELECT COUNT(*) AS expression
FROM 
(SELECT DISTINCT node.title AS node_title, node.nid AS nid, node |
| 2362328 | example    | example:49244        | example | Query       |    694 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
[... 164 more lines like the one above, "Copying to tmp table on disk" ...]
| 2362857 | example    | example:50507        | example | Query       |    428 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362858 | example    | example:50509        | example | Query       |    427 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362859 | example    | example:50511        | example | Query       |    428 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362860 | example    | example:50514        | example | Query       |    427 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362861 | example    | example:50516        | example | Query       |    427 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362862 | example    | example:50519        | example | Query       |    427 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362863 | example    | example:50522        | example | Query       |    427 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362864 | example    | example:50524        | example | Query       |    425 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362865 | example    | example:50526        | example | Query       |    425 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362866 | example    | example:50529        | example | Query       |    424 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362868 | example    | example:50533        | example | Query       |    423 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362871 | example    | example:50537        | example | Query       |    422 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362872 | example    | example:50539        | example | Query       |    422 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362873 | example    | example:50544        | example | Query       |    421 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362874 | example    | example:50546        | example | Query       |    421 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362875 | example    | example:50548        | example | Query       |    421 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362876 | example    | example:50552        | example | Query       |    419 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362877 | example    | example:50556        | example | Query       |    418 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362878 | example    | example:50559        | example | Query       |    417 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362880 | example    | example:50565        | example | Query       |    417 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362881 | example    | example:50567        | example | Query       |    416 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362882 | example    | example:50570        | example | Query       |    413 | Copying to tmp table on disk                                          | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
| 2362883 | example    | example:50572        | example | Query       |    412 | converting HEAP to MyISAM                                             | SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.nid AS |
[... 67 more lines like the one above, "converting HEAP to MyISAM" ...]
| 2362976 | root       | localhost            | NULL            | Query       |      0 | NULL                                                                  | show processlist                                                                                     |
+---------+------------+----------------------+-----------------+-------------+--------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
452 rows in set (0.00 sec)

As you can see, it's about 375 lines of "Copying tmp table on disk" and about 75 of "converting HEAP to MyISAM". My first reaction is to follow the instructions in this StackOverflow answer from @RolandoMySQLDBA and either increase the variables tmp_table_size and/or max_heap_table_size (but how can I know the amount) or set them as small as possible and create a RAM disk for MySQL's temp directory. But I thought I'd ask before doing something I don't completely understand.

What can I do to further understand the problem? What are the likely issues causing this? Are either of those ideas (increase tmp_table_size, max_heap_table_size; or create RAM disk) applicable to this situation?

Some more details:

Server has 32GB of RAM of which I would be comfortable dedicating 10GB to a RAM disk. It's running Ubuntu 14.04.3 and MySQL Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3. The MySQL server is being replicated to a slave at a different host.

The vast majority of the tables are InnoDB.

Customized MySQL variables:

[mysqld]
server-id         = <omitted>
bind-address      = <omitted>
log_bin           = /var/log/mysql/mysql-bin.log
query_cache_limit = 6M
query_cache_size  = 128M
innodb_file_per_table
default-storage-engine = InnoDB
innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1

relay_log_purge       = 1
relay_log_space_limit = 10G

max_connections         = 1024
innodb_buffer_pool_size = 4G
  # Set based on recommendations from http://dba.stackexchange.com/a/39504/34815
innodb_buffer_pool_instances = 4
  # @see https://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-buffer-pools.html

Also:

mysql> SHOW VARIABLES LIKE "%_table_size";
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+
2 rows in set (0.00 sec)

This is out of my area of expertise. Any help is appreciated.

Update Per the request of Raymond Nijland, the SHOW CREATE TABLE main_node output. main_node is the main table being queried above, though it was likely joined with several other tables. I foolishly forgot to to run SHOW FULL PROCESSLIST; before restarting MySQL. Note that there should be nothing special about this table, it is one of the most used tables of Drupal sites.

CREATE TABLE `main_node` (
  `nid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for a node.',
  `vid` int(10) unsigned DEFAULT NULL COMMENT 'The current main_node_revision.vid version identifier.',
  `type` varchar(32) NOT NULL DEFAULT '' COMMENT 'The main_node_type.type of this node.',
  `language` varchar(12) NOT NULL DEFAULT '' COMMENT 'The main_languages.language of this node.',
  `title` varchar(255) NOT NULL DEFAULT '' COMMENT 'The title of this node, always treated as non-markup plain text.',
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT 'The main_users.uid that owns this node; initially, this is the user that created it.',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT 'Boolean indicating whether the node is published (visible to non-administrators).',
  `created` int(11) NOT NULL DEFAULT '0' COMMENT 'The Unix timestamp when the node was created.',
  `changed` int(11) NOT NULL DEFAULT '0' COMMENT 'The Unix timestamp when the node was most recently saved.',
  `comment` int(11) NOT NULL DEFAULT '0' COMMENT 'Whether comments are allowed on this node: 0 = no, 1 = closed (read only), 2 = open (read/write).',
  `promote` int(11) NOT NULL DEFAULT '0' COMMENT 'Boolean indicating whether the node should be displayed on the front page.',
  `sticky` int(11) NOT NULL DEFAULT '0' COMMENT 'Boolean indicating whether the node should be displayed at the top of lists in which it appears.',
  `tnid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The translation set id for this node, which equals the node id of the source post in each set.',
  `translate` int(11) NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this translation page needs to be updated.',
  PRIMARY KEY (`nid`),
  UNIQUE KEY `vid` (`vid`),
  KEY `node_changed` (`changed`),
  KEY `node_created` (`created`),
  KEY `node_frontpage` (`promote`,`status`,`sticky`,`created`),
  KEY `node_status_type` (`status`,`type`,`nid`),
  KEY `node_title_type` (`title`,`type`(4)),
  KEY `node_type` (`type`(4)),
  KEY `uid` (`uid`),
  KEY `tnid` (`tnid`),
  KEY `translate` (`translate`),
  KEY `language` (`language`)
) ENGINE=InnoDB AUTO_INCREMENT=58237 DEFAULT CHARSET=utf8 COMMENT='The base table for nodes.';
  • Did you check htop or something similar? Is it possible that the server may be swapping at that moment? 16MB * 425 tmp tables = some 7GB of memory only for the temp tables and then moved to disk if the reason for those converts is the size limit. Consider optimizing those queries to not use so big tmp tables. – jkavalik Sep 22 '15 at 8:02
  • I checked the monit history and it showed RAM usage only go up to 60%. When the system is running normally, there is only about 20% usage (6.4 of 32GB). Unfortunately, since the system is Drupal and it can be difficult to optimize queries without coding around Drupal. Since this only happens occasionally, I'm guessing it may not be the fault of the queries but something else is stalling them. – donut Sep 22 '15 at 16:34
  • You should also post the output off show create table [table_name] – Raymond Nijland Sep 23 '15 at 21:09
  • @RaymondNijland Added the output to the question. – donut Sep 23 '15 at 21:55
  • 1
    @donut well i dont see your complete query but it looks like your problem is caused by the DISTINCT and the 512 bytes limit read this dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html – Raymond Nijland Sep 23 '15 at 22:18
3

While this isn't quite the answer I was looking for, the problem seems to have been resolved. Raymond Nijland pointed out that I was likely hitting the 512 byte read limit for DISTINCT:

@donut well i dont see your complete query but it looks like your problem is caused by the DISTINCT and the 512 bytes limit read this dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html – Raymond Nijland Sep 23 at 22:18

I reevaluated the queries I most suspected to be causing these problems and determined I was able to remove DISTINCT from most of them. Since removing it I have not encountered this issue again.

  • Note that title is varchar(255) CHARSET utf8 which takes 767 bytes -- more than 512. Decreasing title to about 190 might allow DISTINCT (but might be too short for the application). – Rick James Oct 30 '15 at 14:12
  • Getting rid of the DISTINCT not only avoids switching from MEMORY to MyISAM, but it may also avoid creating the tmp table. (I say may because I can't see the SELECT or its EXPLAIN to be sure.) – Rick James Oct 30 '15 at 14:14

Your Answer

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

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