主页 / dba / 问题 / 235097
Accepted
FrustratedWithFormsDesigner
Asked: 2019-04-18 13:02:25 +0800 CST

查询用尽所有临时磁盘空间 - 但仅在一台服务器上

  • 772

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

在运行 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

4 个回答

  1. 哎哟!

    key_buffer_size 16777216

    将其设置为可用RAM 的 20% 左右。

    join_buffer_size = 1M

tmp_table_size and max_heap_table_size  -- about 1% of RAM

    注意：当（我不是说“如果”）您更改为 InnoDB 时，请务必再次降低 key_buffer_size，同时将 innodb_buffer_pool_size 提高到 RAM 的 70%。

    （查看变量和状态后的更多内容）

    检查引擎——5.5 中没有一个使用 InnoDB；一些5.7是。这些表在迁移到 5.7 时是否发生了变化？

    在 32GB MySQL 服务器上从 MyISAM 过渡到 InnoDB：

    key_buffer_size = 3G
innodb_buffer_pool_size = 10G

    在旧系统和新系统中都有一些令人讨厌的查询。

    long_query_time = 1

    并打开慢日志。然后在这里获得帮助：http: //mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

    • 1

  2. 为您的 my.cnf [mysqld] 部分考虑的建议

    max_heap_table_size=320M  # from 16M for ~ 1% RAM
tmp_table_size=320M  # from 16M for size matched to MHTSize
thread_cache_size=64  # from 8 to reduce threads_created count
sort_buffer_size=2M  # from 32M to conserve RAM requested per connection
slow_query_log=ON  # from OFF to allow daily monitoring
query_cache_size=0  # from 16M since Query_Cache_Type is OFF
lock_wait_timeout=300  # from 1 year of seconds, when REPLICATION not used
key_buffer_size=10G  # from 16M - all ISAM data tables
key_age_threshold=7200  # from 300 seconds to reduce key_reads
key_cache_block_size=16382  # from 1024 to reduce CPU cycles used for mgmt
key_cache_division_limit=50  # from 100 for HOT/WARM split to reduce key_reads
innodb_lru_scan_depth=100  # from 1024 to conserve CPU cycles every second for function

    免责声明：我是我的个人资料中列出的网站的内容作者，带有联系信息的网络个人资料。

    • 1

  3. 一种使 5.7 像 5.5 一样优化的可能方法对这个语句做了...

    先来看看

    SHOW VARIABLES LIKE 'optimizer_switch';

    您将设置很多设置。我认为其中一个控制了一项对您的查询效果不佳的新功能。我不知道它是否是唯一相关的。执行此操作时查看是否EXPLAIN恢复：

    SET SESSION optimizer_switch='materialization=off';
EXPLAIN SELECT ...
SET SESSION optimizer_switch='materialization=on';

    如果是这样，那么SETs在您的SELECT.

    • 0
  4. Best Answer

    首先，感谢 Rick James 和 Wilson Hauck 试图提供帮助。非常感谢，这表明我在微调 MySQL 数据库方面还有很多东西要学。

    我尝试了上面的大多数建议，单独和组合，到目前为止，它们都没有奏效。:(

    我做了更多的研究，并偶然发现了一个网页（丢失了链接，抱歉），该网页描述了我遇到的类似问题，与该group_concat功能有关。我尝试将内部查询group_concat作为独立查询运行，发现当我删除 时group_concat，我没有任何问题！另一个页面上的一个建议是更改ORDER BY使用group_concat. 我注意到我没有排序子句。我加了一个，查询成功了！！查询现在看起来更像这样：

    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
    -- The ORDER BY below is *NEW*
    order by ReferenceEntity.identifier desc ) 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;

    所以我猜对于 MySQL 5.7.26（最近好像有人升级了），结果需要对 group_concat 进行排序。

    对我来说奇怪的是它似乎适用于任何排序。我什至尝试过ORDER BY rand() desc，查询的运行与我指定实际列名时一样好。

    现在我想知道MySQL中是否存在错误group_concat......

    • 0

相关问题