I've run into a strange situation where a query uses up all available disc space on one server (and then crashes, when there is no space left), but not on the other, and I'm not sure how to correct this situation.
On the OLD server, running MySQL 5.5.55, the query uses up about 8 GB of disc space. It returns results after about 10 minutes.
On the NEW server, running MySQL 5.7.25, the query used 15 GB and then ran out of disc space and terminated. A co-worker said that when there was more disc space available a little while ago, they observed the query using up closer to 30 GB before running out of space.
The data is the same on both servers.
The query is:
SELECT distinct subq1.duplicate_count, subq1.identifier, subq1.ReferenceEntity_DB_IDs, subq1._class AS object_type, subq1._displayName as display_name, subq1.combined_identifier, ReferenceDatabase_2_name.name as ref_db_name, subq1.species_db_id, subq1.species_name
from (
select count(ReferenceEntity.DB_ID) as duplicate_count, ReferenceEntity.identifier, group_concat(ReferenceEntity.db_id) as ReferenceEntity_DB_IDs, ReferenceEntity.referenceDatabase, DatabaseObject._class, DatabaseObject._displayName, subq.combined_identifier, subq.species_db_id, subq.species_name
from ReferenceEntity
inner join DatabaseObject on DatabaseObject.db_id = ReferenceEntity.db_id
inner join (select ReferenceEntity.*, concat(coalesce(ReferenceEntity.identifier,'NULL'),';', coalesce(DatabaseObject._displayName,'NULL')) as combined_identifier, species_subq.db_id AS species_db_id, species_subq.name AS species_name
from ReferenceEntity
inner join DatabaseObject on ReferenceEntity.db_id = DatabaseObject.db_id
LEFT OUTER JOIN ReferenceSequence ON ReferenceEntity.DB_ID = ReferenceSequence.DB_ID
LEFT OUTER JOIN (SELECT Species.DB_ID, Taxon_2_name.name
FROM Species
INNER JOIN Taxon_2_name ON (Taxon_2_name.DB_ID = Species.DB_ID AND Taxon_2_name.name_rank = 0)) AS species_subq
ON ReferenceSequence.species = species_subq.DB_ID
where ReferenceEntity.identifier is not null) as subq on subq.db_id = ReferenceEntity.db_id
group by subq.combined_identifier, ReferenceEntity.identifier, referenceDatabase, _class, _displayName, subq.species_db_id, subq.species_name
having count(ReferenceEntity.db_id) > 1) as subq1
inner join ReferenceDatabase_2_name on ReferenceDatabase_2_name.DB_ID = subq1.referenceDatabase
where ReferenceDatabase_2_name.name_rank = 0
order by duplicate_count, ReferenceDatabase_2_name.name, identifier;
The query plan from the NEW server (where the query fails) looks like this:
+----+-------------+--------------------------+------------+--------+--------------------+-------------+---------+--------------------------------+--------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------------+------------+--------+--------------------+-------------+---------+--------------------------------+--------+----------+----------------------------------------------+ | 1 | PRIMARY | ReferenceDatabase_2_name | NULL | ALL | DB_ID | NULL | NULL | NULL | 292 | 10.00 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | ReferenceDatabase_2_name.DB_ID | 2526 | 100.00 | NULL | | 2 | DERIVED | ReferenceEntity | NULL | ALL | PRIMARY | NULL | NULL | NULL | 737849 | 100.00 | Using temporary; Using filesort | | 2 | DERIVED | DatabaseObject | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ReferenceEntity.DB_ID | 1 | 100.00 | Using index condition | | 2 | DERIVED | ReferenceEntity | NULL | eq_ref | PRIMARY,identifier | PRIMARY | 4 | ReferenceEntity.DB_ID | 1 | 100.00 | Using where | | 2 | DERIVED | ReferenceSequence | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ReferenceEntity.DB_ID | 1 | 100.00 | NULL | | 2 | DERIVED | Species | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ReferenceSequence.species | 1 | 100.00 | Using index | | 2 | DERIVED | Taxon_2_name | NULL | ref | DB_ID | DB_ID | 5 | ReferenceSequence.species | 1 | 100.00 | Using where | | 2 | DERIVED | DatabaseObject | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ReferenceEntity.DB_ID | 1 | 100.00 | Using index condition | +----+-------------+--------------------------+------------+--------+--------------------+-------------+---------+--------------------------------+--------+----------+----------------------------------------------+
The query plan from the OLD server (where the query succeeds) is different, and it looks like this:
+----+-------------+--------------------------+--------+--------------------+---------+---------+----------------------------------------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------------+--------+--------------------+---------+---------+----------------------------------------+--------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4169 | Using temporary; Using filesort | | 1 | PRIMARY | ReferenceDatabase_2_name | ref | DB_ID | DB_ID | 5 | subq1.referenceDatabase | 2 | Using where | | 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 737849 | Using temporary; Using filesort | | 2 | DERIVED | ReferenceEntity | eq_ref | PRIMARY | PRIMARY | 4 | subq.DB_ID | 1 | | | 2 | DERIVED | DatabaseObject | eq_ref | PRIMARY | PRIMARY | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | Using where | | 3 | DERIVED | ReferenceEntity | ALL | PRIMARY,identifier | NULL | NULL | NULL | 737849 | Using where | | 3 | DERIVED | ReferenceSequence | eq_ref | PRIMARY | PRIMARY | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | | | 3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 79 | | | 3 | DERIVED | DatabaseObject | eq_ref | PRIMARY | PRIMARY | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | Using where | | 4 | DERIVED | Species | index | PRIMARY | PRIMARY | 4 | NULL | 79 | Using index | | 4 | DERIVED | Taxon_2_name | ref | DB_ID | DB_ID | 5 | test_database_XX.Species.DB_ID | 1 | Using where | +----+-------------+--------------------------+--------+--------------------+---------+---------+----------------------------------------+--------+---------------------------------+
My guess is that there is some significant difference in how these two instances of MySQL are configured, but I'm not sure. the MySQL 5.7.25 server was set up with a config split into several files, and since it's not the same version, I'm not sure how to properly compare.
Does anyone have any suggestions for debugging this issue? Maybe in terms of which variables to compare first?
Edit:
Global variables from NEW server:
https://pastebin.com/EacpyyAb
Global variables from OLD server:
https://pastebin.com/uSj7bEbt
Global status from NEW server:
https://pastebin.com/CD0g3qSb
Global status from OLD server:
https://pastebin.com/ETnTbKA1
Indicies, OLD server:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ReferenceDatabase_2_name 1 DB_ID 1 DB_ID A 146 NULL NULL YES BTREE ReferenceDatabase_2_name 1 name 1 name A 97 10 NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ReferenceEntity 0 PRIMARY 1 DB_ID A 737849 NULL NULL BTREE ReferenceEntity 1 referenceDatabase 1 referenceDatabase A 90 NULL NULL YES BTREE ReferenceEntity 1 identifier 1 identifier A 245949 10 NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment DatabaseObject 0 PRIMARY 1 DB_ID A 2439461 NULL NULL BTREE DatabaseObject 1 _class 1 _class A 65 NULL NULL YES BTREE DatabaseObject 1 _timestamp 1 _timestamp A 45175 NULL NULL BTREE DatabaseObject 1 created 1 created A 187650 NULL NULL YES BTREE DatabaseObject 1 _displayName 1 _displayName A 304932 10 NULL YES BTREE DatabaseObject 1 stableIdentifier 1 stableIdentifier A 2439461 NULL NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ReferenceSequence 0 PRIMARY 1 DB_ID A 735705 NULL NULL BTREE ReferenceSequence 1 species 1 species A 52 NULL NULL YES BTREE ReferenceSequence 1 sequenceLength 1 sequenceLength A 735705 NULL NULL YES BTREE ReferenceSequence 1 isSequenceChanged 1 isSequenceChanged A 735705 10 NULL YES BTREE ReferenceSequence 1 checksum 1 checksum A 735705 10 NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Species 0 PRIMARY 1 DB_ID A 79 NULL NULL BTREE Species 1 abbreviation 1 abbreviation A 79 10 NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Taxon_2_name 1 DB_ID 1 DB_ID A 551 NULL NULL YES BTREE Taxon_2_name 1 name 1 name A 551 NULL NULL YES BTREE
NEW Server:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ReferenceDatabase_2_name 1 DB_ID 1 DB_ID A 146 NULL NULL YES BTREE ReferenceDatabase_2_name 1 name 1 name A 97 10 NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ReferenceEntity 0 PRIMARY 1 DB_ID A 737849 NULL NULL BTREE ReferenceEntity 1 referenceDatabase 1 referenceDatabase A 90 NULL NULL YES BTREE ReferenceEntity 1 identifier 1 identifier A 245950 10 NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment DatabaseObject 0 PRIMARY 1 DB_ID A 2439461 NULL NULL BTREE DatabaseObject 1 _class 1 _class A 65 NULL NULL YES BTREE DatabaseObject 1 _timestamp 1 _timestamp A 45175 NULL NULL BTREE DatabaseObject 1 created 1 created A 187651 NULL NULL YES BTREE DatabaseObject 1 _displayName 1 _displayName A 304933 10 NULL YES BTREE DatabaseObject 1 stableIdentifier 1 stableIdentifier A 2439461 NULL NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ReferenceSequence 0 PRIMARY 1 DB_ID A 735705 NULL NULL BTREE ReferenceSequence 1 species 1 species A 52 NULL NULL YES BTREE ReferenceSequence 1 sequenceLength 1 sequenceLength A 735705 NULL NULL YES BTREE ReferenceSequence 1 isSequenceChanged 1 isSequenceChanged A 735705 10 NULL YES BTREE ReferenceSequence 1 checksum 1 checksum A 735705 10 NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Species 0 PRIMARY 1 DB_ID A 79 NULL NULL BTREE Species 1 abbreviation 1 abbreviation A 79 10 NULL YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Taxon_2_name 1 DB_ID 1 DB_ID A 551 NULL NULL YES BTREE Taxon_2_name 1 name 1 name A 551 NULL NULL YES BTREE
The NEW server doesn't log the error. The only message that it emits is ERROR 3 (HY000): Error writing file '/tmp/MYIZQlbr' (Errcode: 28 - No space left on device) I am not sure I have permission to change the error logging settings…
SHOW CREATE TABLE from OLD server:
Table Create Table
ReferenceDatabase_2_name CREATE TABLE `ReferenceDatabase_2_name` (
`DB_ID` int(10) unsigned DEFAULT NULL,
`name_rank` int(10) unsigned DEFAULT NULL,
`name` mediumtext COLLATE utf8_unicode_ci,
KEY `DB_ID` (`DB_ID`),
KEY `name` (`name`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Table Create Table
ReferenceEntity CREATE TABLE `ReferenceEntity` (
`DB_ID` int(10) unsigned NOT NULL DEFAULT '0',
`identifier` mediumtext COLLATE utf8_unicode_ci,
`referenceDatabase` int(10) unsigned DEFAULT NULL,
`referenceDatabase_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`DB_ID`),
KEY `referenceDatabase` (`referenceDatabase`),
KEY `identifier` (`identifier`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Table Create Table
DatabaseObject CREATE TABLE `DatabaseObject` (
`DB_ID` int(10) NOT NULL AUTO_INCREMENT,
`_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`_displayName` mediumtext COLLATE utf8_unicode_ci,
`_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created` int(10) unsigned DEFAULT NULL,
`created_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`stableIdentifier` int(10) unsigned DEFAULT NULL,
`stableIdentifier_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`DB_ID`),
KEY `_class` (`_class`),
KEY `_timestamp` (`_timestamp`),
KEY `created` (`created`),
KEY `_displayName` (`_displayName`(10)),
KEY `stableIdentifier` (`stableIdentifier`)
) ENGINE=MyISAM AUTO_INCREMENT=11631469 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Table Create Table
ReferenceSequence CREATE TABLE `ReferenceSequence` (
`DB_ID` int(10) unsigned NOT NULL DEFAULT '0',
`species` int(10) unsigned DEFAULT NULL,
`species_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`sequenceLength` int(10) DEFAULT NULL,
`isSequenceChanged` mediumtext COLLATE utf8_unicode_ci,
`checksum` mediumtext COLLATE utf8_unicode_ci,
PRIMARY KEY (`DB_ID`),
KEY `species` (`species`),
KEY `sequenceLength` (`sequenceLength`),
KEY `isSequenceChanged` (`isSequenceChanged`(10)),
KEY `checksum` (`checksum`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Table Create Table
Species CREATE TABLE `Species` (
`DB_ID` int(10) unsigned NOT NULL DEFAULT '0',
`abbreviation` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`DB_ID`),
KEY `abbreviation` (`abbreviation`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Table Create Table
Taxon_2_name CREATE TABLE `Taxon_2_name` (
`DB_ID` int(10) unsigned DEFAULT NULL,
`name_rank` int(10) unsigned DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
KEY `DB_ID` (`DB_ID`),\n KEY `name` (`name`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
SHOW CREATE TABLE from NEW server is identical to OLD server.
Best Answer
Ouch!
Set that to about 20% of available RAM.
Note: When (I'm not saying "if") you change to InnoDB, be sure to lower key_buffer_size down again, while raising innodb_buffer_pool_size to 70% of RAM.
(More after I review the VARIABLES and STATUS)
Check the engines -- none of the 5.5 is using InnoDB; some of 5.7 is. Did these tables change when moving to 5.7??
For transitioning from MyISAM to InnoDB on a 32GB MySQL-only server:
There are some nasty queries, both in the old system and new.
and turn on the slowlog. Then get help here: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog