主页 / user-6687

FrustratedWithFormsDesigner's questions

Martin Hope
FrustratedWithFormsDesigner
Asked: 2019-04-18 13:02:25 +0800 CST
  • 0

我遇到了一种奇怪的情况，查询用尽了一台服务器上的所有可用磁盘空间（然后在没有空间时崩溃），但在另一台服务器上却没有，我不知道如何纠正这种情况.

在运行 MySQL 5.5.55的 OLD 服务器上，查询占用了大约 8 GB 的磁盘空间。它会在大约 10 分钟后返回结果。

在运行 MySQL 5.7.25的 NEW 服务器上，查询使用了 15 GB，然后磁盘空间不足并终止。一位同事说，不久前有更多可用磁盘空间时，他们观察到查询使用了接近 30 GB 的空间，然后空间就用完了。

两台服务器上的数据相同。

查询是：

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;

来自 NEW 服务器（查询失败）的查询计划如下所示：

+----+-------------+--------------+--- ---------+--------+--------+--------- ---+----------+--------------------------------+--- -----+----------+--------------------------------- -------------+
| 编号 | 选择类型 | 表| 隔断 | 类型 | 可能的键 | 关键 | key_len | 参考 | 行 | 过滤 | 额外 |
+----+-------------+--------------+--- ---------+--------+--------+--------- ---+----------+--------------------------------+--- -----+----------+--------------------------------- -------------+
| 1 | 初级 | 参考数据库_2_名称 | 空 | 全部 | DB_ID | 空 | 空 | 空 | 292 | 10.00 | 使用哪里；使用临时的；使用文件排序 |
| 1 | 初级 | <派生2> | 空 | 参考 | <auto_key0> | <auto_key0> | 5 | 参考数据库_2_name.DB_ID | 2526 | 100.00 | 空 |
| 2 | 派生 | 参考实体 | 空 | 全部 | 初级 | 空 | 空 | 空 | 737849 | 100.00 | 使用临时的；使用文件排序 |
| 2 | 派生 | 数据库对象 | 空 | eq_ref | 初级 | 初级 | 4 | 参考实体.DB_ID | 1 | 100.00 | 使用索引条件 |
| 2 | 派生 | 参考实体 | 空 | eq_ref | PRIMARY,标识符 | 初级 | 4 | 参考实体.DB_ID | 1 | 100.00 | 使用位置 |
| 2 | 派生 | 参考序列 | 空 | eq_ref | 初级 | 初级 | 4 | 参考实体.DB_ID | 1 | 100.00 | 空 |
| 2 | 派生 | 物种 | 空 | eq_ref | 初级 | 初级 | 4 | 参考序列.species | 1 | 100.00 | 使用索引 |
| 2 | 派生 | Taxon_2_name | 空 | 参考 | DB_ID | DB_ID | 5 | 参考序列.species | 1 | 100.00 | 使用位置 |
| 2 | 派生 | 数据库对象 | 空 | eq_ref | 初级 | 初级 | 4 | 参考实体.DB_ID | 1 | 100.00 | 使用索引条件 |
+----+-------------+--------------+--- ---------+--------+--------+--------- ---+----------+--------------------------------+--- -----+----------+--------------------------------- -------------+

来自 OLD 服务器（查询成功的地方）的查询计划是不同的，它看起来像这样：

+----+-------------+--------------+--- -----+--------+---------+---------+--- -------------------------------------+--------+--- ------------------------------+
| 编号 | 选择类型 | 表| 类型 | 可能的键 | 关键 | key_len | 参考 | 行 | 额外 |
+----+-------------+--------------+--- -----+--------+---------+---------+--- -------------------------------------+--------+--- ------------------------------+
| 1 | 初级 | <派生2> | 全部 | 空 | 空 | 空 | 空 | 4169 | 使用临时的；使用文件排序 |
| 1 | 初级 | 参考数据库_2_名称 | 参考 | DB_ID | DB_ID | 5 | subq1.reference 数据库 | 2 | 使用位置 |
| 2 | 派生 | <派生3> | 全部 | 空 | 空 | 空 | 空 | 737849 | 使用临时的；使用文件排序 |
| 2 | 派生 | 参考实体 | eq_ref | 初级 | 初级 | 4 | subq.DB_ID | 1 | |
| 2 | 派生 | 数据库对象 | eq_ref | 初级 | 初级 | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | 使用位置 |
| 3 | 派生 | 参考实体 | 全部 | PRIMARY,标识符 | 空 | 空 | 空 | 737849 | 使用位置 |
| 3 | 派生 | 参考序列 | eq_ref | 初级 | 初级 | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | |
| 3 | 派生 | <派生4> | 全部 | 空 | 空 | 空 | 空 | 79 | |
| 3 | 派生 | 数据库对象 | eq_ref | 初级 | 初级 | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | 使用位置 |
| 4 | 派生 | 物种 | 索引 | 初级 | 初级 | 4 | 空 | 79 | 使用索引 |
| 4 | 派生 | Taxon_2_name | 参考 | DB_ID | DB_ID | 5 | test_database_XX.Species.DB_ID | 1 | 使用位置 |
+----+-------------+--------------+--- -----+--------+---------+---------+--- -------------------------------------+--------+--- ------------------------------+

我的猜测是这两个 MySQL 实例的配置方式存在一些显着差异，但我不确定。MySQL 5.7.25 服务器设置为将配置拆分为多个文件，由于版本不同，我不确定如何正确比较。

有人对调试此问题有任何建议吗？也许首先要比较哪些变量？

编辑：

来自新服务器的全局变量： https ://pastebin.com/EacpyyAb

旧服务器的全局变量： https ://pastebin.com/uSj7bEbt

来自新服务器的全局状态： https ://pastebin.com/CD0g3qSb

旧服务器的全局状态： https ://pastebin.com/ETnTbKA1

指数，旧服务器：

Table Non_unique Key_name Seq_in_index Column_name Collat​​ion 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 Collat​​ion 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 标识符 1 标识符 A 245949 10 NULL YES BTREE       

Table Non_unique Key_name Seq_in_index Column_name Collat​​ion 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 创建 1 创建 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 Collat​​ion Cardinality Sub_part Packed Null Index_type Comment Index_comment
参考序列 0 PRIMARY 1 DB_ID A 735705 NULL NULL BTREE       
参考序列1种1种A 52 NULL NULL YES BTREE       
参考Sequence 1 sequenceLength 1 sequenceLength A 735705 NULL NULL YES BTREE       
参考Sequence 1 isSequenceChanged 1 isSequenceChanged A 735705 10 NULL YES BTREE       
参考序列 1 校验和 1 校验和 A 735705 10 NULL YES BTREE       

Table Non_unique Key_name Seq_in_index Column_name Collat​​ion Cardinality Sub_part Packed Null Index_type Comment Index_comment
种类 0 PRIMARY 1 DB_ID A 79 NULL NULL BTREE       
种类 1 缩写 1 缩写 A 79 10 NULL YES BTREE       

Table Non_unique Key_name Seq_in_index Column_name Collat​​ion 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 名称 1 名称 A 551 NULL NULL YES BTREE

新服务器：

Table Non_unique Key_name Seq_in_index Column_name Collat​​ion 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 Collat​​ion 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 标识符 1 标识符 A 245950 10 NULL YES BTREE       

Table Non_unique Key_name Seq_in_index Column_name Collat​​ion 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 创建 1 创建 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 Collat​​ion Cardinality Sub_part Packed Null Index_type Comment Index_comment
参考序列 0 PRIMARY 1 DB_ID A 735705 NULL NULL BTREE       
参考序列1种1种A 52 NULL NULL YES BTREE       
参考Sequence 1 sequenceLength 1 sequenceLength A 735705 NULL NULL YES BTREE       
参考Sequence 1 isSequenceChanged 1 isSequenceChanged A 735705 10 NULL YES BTREE       
参考序列 1 校验和 1 校验和 A 735705 10 NULL YES BTREE       

Table Non_unique Key_name Seq_in_index Column_name Collat​​ion Cardinality Sub_part Packed Null Index_type Comment Index_comment
种类 0 PRIMARY 1 DB_ID A 79 NULL NULL BTREE       
种类 1 缩写 1 缩写 A 79 10 NULL YES BTREE       

Table Non_unique Key_name Seq_in_index Column_name Collat​​ion 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 名称 1 名称 A 551 NULL NULL YES BTREE

新服务器不会记录错误。它发出的唯一消息是ERROR 3 (HY000): Error writing file '/tmp/MYIZQlbr' (Errcode: 28 - No space left on device)我不确定我是否有权更改错误日志记录设置...

从旧服务器显示创建表：

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 与 OLD server 相同。

mysql