Eu me deparei com uma situação estranha em que uma consulta usa todo o espaço em disco disponível em um servidor (e depois trava, quando não há mais espaço), mas não no outro, e não tenho certeza de como corrigir essa situação .
No servidor OLD, executando o MySQL 5.5.55 , a consulta usa cerca de 8 GB de espaço em disco. Ele retorna os resultados após cerca de 10 minutos.
No servidor NOVO, executando o MySQL 5.7.25 , a consulta usou 15 GB e, em seguida, ficou sem espaço em disco e foi encerrada. Um colega de trabalho disse que quando havia mais espaço em disco disponível há pouco tempo, eles observaram a consulta usando mais de 30 GB antes de ficar sem espaço.
Os dados são os mesmos em ambos os servidores.
A consulta é:
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;
O plano de consulta do NOVO servidor (onde a consulta falha) se parece com isso:
+----+-------------+--------------------------+--- ---------+--------+--------------------+---------- ---+---------+--------------------------------+--- -----+----------+--------------------------------- -------------+ | identificação | select_type | mesa | divisórias | tipo | chaves_possíveis | chave | key_len | ref | linhas | filtrado | Extra | +----+-------------+--------------------------+--- ---------+--------+--------------------+---------- ---+---------+--------------------------------+--- -----+----------+--------------------------------- -------------+ | 1 | PRINCIPAL | ReferenceDatabase_2_name | NULO | TODOS | DB_ID | NULO | NULO | NULO | 292 | 10h00 | Usando onde; Usando temporário; Usando a classificação de arquivos | | 1 | PRINCIPAL | <derivado2> | NULO | ref | <auto_key0> | <auto_key0> | 5 | ReferenceDatabase_2_name.DB_ID | 2526 | 100,00 | NULO | | 2 | DERIVADO | Entidade de Referência | NULO | TODOS | PRINCIPAL | NULO | NULO | NULO | 737849 | 100,00 | Usando temporário; Usando a classificação de arquivos | | 2 | DERIVADO | Objeto de Banco de Dados | NULO | eq_ref | PRINCIPAL | PRINCIPAL | 4 | ReferenceEntity.DB_ID | 1 | 100,00 | Usando condição de índice | | 2 | DERIVADO | Entidade de Referência | NULO | eq_ref | PRIMÁRIO,identificador | PRINCIPAL | 4 | ReferenceEntity.DB_ID | 1 | 100,00 | Usando onde | | 2 | DERIVADO | Sequência de Referência | NULO | eq_ref | PRINCIPAL | PRINCIPAL | 4 | ReferenceEntity.DB_ID | 1 | 100,00 | NULO | | 2 | DERIVADO | Espécies | NULO | eq_ref | PRINCIPAL | PRINCIPAL | 4 | ReferenceSequence.species | 1 | 100,00 | Usando o índice | | 2 | DERIVADO | Taxon_2_name | NULO | ref | DB_ID | DB_ID | 5 | ReferenceSequence.species | 1 | 100,00 | Usando onde | | 2 | DERIVADO | Objeto de Banco de Dados | NULO | eq_ref | PRINCIPAL | PRINCIPAL | 4 | ReferenceEntity.DB_ID | 1 | 100,00 | Usando condição de índice | +----+-------------+--------------------------+--- ---------+--------+--------------------+---------- ---+---------+--------------------------------+--- -----+----------+--------------------------------- -------------+
O plano de consulta do servidor OLD (onde a consulta é bem-sucedida) é diferente e se parece com isso:
+----+-------------+--------------------------+--- -----+--------------------+---------+---------+--- ----------------------------------+--------+--- ------------------------------+ | identificação | select_type | mesa | tipo | chaves_possíveis | chave | key_len | ref | linhas | Extra | +----+-------------+--------------------------+--- -----+--------------------+---------+---------+--- ----------------------------------+--------+--- ------------------------------+ | 1 | PRINCIPAL | <derivado2> | TODOS | NULO | NULO | NULO | NULO | 4169 | Usando temporário; Usando a classificação de arquivos | | 1 | PRINCIPAL | ReferenceDatabase_2_name | ref | DB_ID | DB_ID | 5 | subq1.referenceBanco de dados | 2 | Usando onde | | 2 | DERIVADO | <derivado3> | TODOS | NULO | NULO | NULO | NULO | 737849 | Usando temporário; Usando a classificação de arquivos | | 2 | DERIVADO | Entidade de Referência | eq_ref | PRINCIPAL | PRINCIPAL | 4 | subq.DB_ID | 1 | | | 2 | DERIVADO | Objeto de Banco de Dados | eq_ref | PRINCIPAL | PRINCIPAL | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | Usando onde | | 3 | DERIVADO | Entidade de Referência | TODOS | PRIMÁRIO,identificador | NULO | NULO | NULO | 737849 | Usando onde | | 3 | DERIVADO | Sequência de Referência | eq_ref | PRINCIPAL | PRINCIPAL | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | | | 3 | DERIVADO | <derivado4> | TODOS | NULO | NULO | NULO | NULO | 79 | | | 3 | DERIVADO | Objeto de Banco de Dados | eq_ref | PRINCIPAL | PRINCIPAL | 4 | test_database_XX.ReferenceEntity.DB_ID | 1 | Usando onde | | 4 | DERIVADO | Espécies | índice | PRINCIPAL | PRINCIPAL | 4 | NULO | 79 | Usando o índice | | 4 | DERIVADO | Taxon_2_name | ref | DB_ID | DB_ID | 5 | test_database_XX.Species.DB_ID | 1 | Usando onde | +----+-------------+--------------------------+--- -----+--------------------+---------+---------+--- ----------------------------------+--------+--- ------------------------------+
Meu palpite é que há alguma diferença significativa em como essas duas instâncias do MySQL são configuradas, mas não tenho certeza. o servidor MySQL 5.7.25 foi configurado com uma configuração dividida em vários arquivos e, como não é a mesma versão, não sei como comparar adequadamente.
Alguém tem alguma sugestão para depurar este problema? Talvez em termos de quais variáveis comparar primeiro?
Editar:
Variáveis globais do NOVO servidor: https://pastebin.com/EacpyyAb
Variáveis globais do servidor OLD: https://pastebin.com/uSj7bEbt
Status global do NOVO servidor: https://pastebin.com/CD0g3qSb
Status global do servidor ANTIGO: https://pastebin.com/ETnTbKA1
Índices, servidor ANTIGO:
Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário Index_comment ReferenceDatabase_2_name 1 DB_ID 1 DB_ID A 146 NULL NULL YES BTREE ReferenceDatabase_2_name 1 nome 1 nome A 97 10 NULL YES BTREE Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário 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 identificador 1 identificador A 245949 10 NULL YES BTREE Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário 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 criado 1 criado 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 Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário Index_comment ReferenceSequence 0 PRIMARY 1 DB_ID A 735705 NULL NULL BTREE ReferênciaSequência 1 espécie 1 espécie 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 Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário Index_comment Espécie 0 PRIMARY 1 DB_ID A 79 NULL NULL BTREE Espécie 1 abreviatura 1 abreviatura A 79 10 NULL YES BTREE Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário Index_comment Taxon_2_name 1 DB_ID 1 DB_ID A 551 NULL NULL YES BTREE Taxon_2_name 1 nome 1 nome A 551 NULL NULL YES BTREE
NOVO Servidor:
Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário Index_comment ReferenceDatabase_2_name 1 DB_ID 1 DB_ID A 146 NULL NULL YES BTREE ReferenceDatabase_2_name 1 nome 1 nome A 97 10 NULL YES BTREE Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário 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 identificador 1 identificador A 245950 10 NULL YES BTREE Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário 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 criado 1 criado 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 Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário Index_comment ReferenceSequence 0 PRIMARY 1 DB_ID A 735705 NULL NULL BTREE ReferênciaSequência 1 espécie 1 espécie 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 Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário Index_comment Espécie 0 PRIMARY 1 DB_ID A 79 NULL NULL BTREE Espécie 1 abreviatura 1 abreviatura A 79 10 NULL YES BTREE Tabela Non_unique Key_name Seq_in_index Column_name Agrupamento Cardinalidade Sub_part Packed Null Index_type Comentário Index_comment Taxon_2_name 1 DB_ID 1 DB_ID A 551 NULL NULL YES BTREE Taxon_2_name 1 nome 1 nome A 551 NULL NULL YES BTREE
O NOVO servidor não registra o erro. A única mensagem que ele emite é ERROR 3 (HY000): Error writing file '/tmp/MYIZQlbr' (Errcode: 28 - No space left on device)que não tenho certeza se tenho permissão para alterar as configurações de log de erros...
SHOW CREATE TABLE do servidor ANTIGO:
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 do servidor NOVO é idêntico ao servidor ANTIGO.