Медленный * начальный * запрос MySQL

Запуская Mysql V5.6.22, запуская сложный запрос к правильно проиндексированной базе данных, время отклика изначально очень медленное (10 секунд). Последующие запросы (для разных элементов) очень отзывчивы (50 мсек). поэтому я предполагаю, что кеши запросов выполняют свою работу - но как мне минимизировать начальный медленный ответ?

База данных - это медицинская база данных (SNOMED), поддерживаемая извне, и я использую рекомендуемые представления для текущих снимков - я думаю, что эти представления являются ограничением по скорости.

Странный перезапуск mysql не имеет значения - кажется, что дело времени - если база данных не используется какое-то время, то для запуска требуются целые годы.

Поэтому у меня вопрос: есть ли параметр mysql, который используется для хранения этих кэшей, или я должен использовать другой подход и не использовать представления (данные для SNOMED обновляются 2 раза в год, а в другой аналогичной базе данных для лекарств есть ежемесячные выпуски.

Некоторые из вас хотели бы видеть запрос, так что здесь идет. Предупреждение, это немного сложно, и количество строк для базовых запросов приведено в комментариях...;-)

SELECT DISTINCT concat(c.id, '::', c.effectiveTime) as id, `d1`.`term` as label, `d2`.`term`
FROM (`snomed`.`rf2_ss_refset` as refset)
JOIN `snomed`.`rf2_ss_concepts` as c ON `c`.`id` = `refset`.`referencedCOmponentId`
JOIN `snomed`.`rf2_ss_descriptions` as d1 ON `d1`.`conceptId` = `refset`.`referencedComponentId`
JOIN `snomed`.`rf2_ss_descriptions` as d2 ON `d2`.`conceptId` = `d1`.`conceptId`
JOIN `snomed`.`rf2_ss_language_refset` as lang ON `lang`.`referencedComponentId` = `d1`.`id`
WHERE `refset`.`refSetId` =  32570071000036102
AND `refset`.`active` =  1
AND `d2`.`typeId` =  900000000000013009
AND `d1`.`active` =  1
AND `d2`.`active` =  1
AND `d1`.`moduleId` =  900000000000207008
AND `d2`.`moduleId` =  900000000000207008
AND `lang`.`active` =  1
AND `lang`.`acceptabilityId` =  900000000000548007
AND `d2`.`term` like "hypertension%"
ORDER BY `d1`.`term`, `d2`.`term`

ГДЕ:

CREATE TABLE `rf2_ss_refset` (
  `id` char(36) COLLATE utf8_unicode_ci NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` smallint(1) NOT NULL,
  `moduleId` bigint(20) unsigned NOT NULL,
  `refSetId` bigint(20) unsigned NOT NULL,
  `referencedComponentId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleId_idx` (`moduleId`),
  KEY `refSetId_idx` (`refSetId`),
  KEY `referencedComponentId_idx` (`referencedComponentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE ALGORITHM=UNDEFINED DEFINER=`snomed`@`localhost` SQL SECURITY DEFINER VIEW `rf2_ss_concepts`
AS SELECT
   `t1`.`id` AS `id`,
   `t1`.`effectiveTime` AS `effectiveTime`,
   `t1`.`active` AS `active`,
   `t1`.`moduleId` AS `moduleId`,
   `t1`.`definitionStatusId` AS `definitionStatusId`
FROM `rf2_full_concepts` `t1` where (`t1`.`effectiveTime` = (select max(`t2`.`effectiveTime`) from `rf2_full_concepts` `t2` where (`t1`.`id` = `t2`.`id`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`snomed`@`localhost` SQL SECURITY DEFINER VIEW `rf2_ss_descriptions`
AS SELECT
   `t1`.`id` AS `id`,
   `t1`.`effectiveTime` AS `effectiveTime`,
   `t1`.`active` AS `active`,
   `t1`.`moduleID` AS `moduleID`,
   `t1`.`conceptId` AS `conceptId`,
   `t1`.`languageCode` AS `languageCode`,
   `t1`.`typeID` AS `typeID`,
   `t1`.`term` AS `term`,
   `t1`.`caseSignificanceId` AS `caseSignificanceId`
FROM `rf2_full_descriptions` `t1` where (`t1`.`effectiveTime` = (select max(`t2`.`effectiveTime`) from `rf2_full_descriptions` `t2` where (`t1`.`id` = `t2`.`id`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`snomed`@`localhost` SQL SECURITY DEFINER VIEW `rf2_ss_language_refset`
AS SELECT
   `t1`.`id` AS `id`,
   `t1`.`effectiveTime` AS `effectiveTime`,
   `t1`.`active` AS `active`,
   `t1`.`moduleId` AS `moduleId`,
   `t1`.`refSetId` AS `refSetId`,
   `t1`.`referencedComponentId` AS `referencedComponentId`,
   `t1`.`acceptabilityId` AS `acceptabilityId`
FROM `rf2_full_language_refset` `t1` where (`t1`.`effectiveTime` = (select max(`t2`.`effectiveTime`) from `rf2_full_language_refset` `t2` where (`t1`.`id` = `t2`.`id`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`snomed`@`localhost` SQL SECURITY DEFINER VIEW `rf2_ss_relationships`
AS SELECT
   `t1`.`id` AS `id`,
   `t1`.`effectiveTime` AS `effectiveTime`,
   `t1`.`active` AS `active`,
   `t1`.`moduleId` AS `moduleId`,
   `t1`.`sourceId` AS `sourceId`,
   `t1`.`destinationId` AS `destinationId`,
   `t1`.`relationshipGroup` AS `relationshipGroup`,
   `t1`.`typeId` AS `typeId`,
   `t1`.`characteristicTypeId` AS `characteristicTypeId`,
   `t1`.`modifierId` AS `modifierId`
FROM `rf2_full_relationships` `t1` where (`t1`.`effectiveTime` = (select max(`t2`.`effectiveTime`) from `rf2_full_relationships` `t2` where (`t1`.`id` = `t2`.`id`)));

#select count(*) from rf2_full_concepts # 507046
CREATE TABLE `rf2_full_concepts` (
  `id` bigint(20) unsigned NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` tinyint(4) DEFAULT NULL,
  `moduleId` bigint(20) unsigned NOT NULL,
  `definitionStatusId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleId_idx` (`moduleId`),
  KEY `definitionStatusId_idx` (`definitionStatusId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#select count(*) from rf2_full_descriptions # 1486373
CREATE TABLE `rf2_full_descriptions` (
  `id` bigint(20) unsigned NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` tinyint(4) DEFAULT NULL,
  `moduleID` bigint(20) unsigned NOT NULL,
  `conceptId` bigint(20) unsigned NOT NULL,
  `languageCode` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
  `typeID` bigint(20) unsigned NOT NULL,
  `term` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `caseSignificanceId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleID_idx` (`moduleID`),
  KEY `conceptId_idx` (`conceptId`),
  KEY `typeID_idx` (`typeID`),
  KEY `caseSignificanceId_idx` (`caseSignificanceId`),
  KEY `term_idx` (`term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#select count(*) from rf2_full_relationships = 4582286 
CREATE TABLE `rf2_full_relationships` (
  `id` bigint(20) unsigned NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` tinyint(4) DEFAULT '1',
  `moduleId` bigint(20) unsigned NOT NULL,
  `sourceId` bigint(20) unsigned NOT NULL,
  `destinationId` bigint(20) unsigned NOT NULL,
  `relationshipGroup` bigint(20) unsigned NOT NULL,
  `typeId` bigint(20) unsigned NOT NULL,
  `characteristicTypeId` bigint(20) unsigned NOT NULL,
  `modifierId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleId_idx` (`moduleId`),
  KEY `sourceId_idx` (`sourceId`),
  KEY `destinationId_idx` (`destinationId`),
  KEY `relationshipGroup_idx` (`relationshipGroup`),
  KEY `typeId_idx` (`typeId`),
  KEY `characteristicTypeId_idx` (`characteristicTypeId`),
  KEY `modifierId_idx` (`modifierId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#select count(*) from rf2_full_language_refset # 624467
CREATE TABLE `rf2_full_language_refset` (
  `id` char(36) COLLATE utf8_unicode_ci NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` smallint(1) NOT NULL,
  `moduleId` bigint(20) unsigned NOT NULL,
  `refSetId` bigint(20) unsigned NOT NULL,
  `referencedComponentId` bigint(20) unsigned NOT NULL,
  `acceptabilityId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleId` (`moduleId`),
  KEY `refSetId` (`refSetId`),
  KEY `referencedComponentId` (`referencedComponentId`),
  KEY `acceptabilityId` (`acceptabilityId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Источник

1 ответ

Решение

Диагностировать чужой сервер без всех частей головоломки сложно, поэтому я сделаю следующие предположения (поправьте меня, если я ошибаюсь):

  1. Это не работает на сервере уровня DB
  2. Конфигурация MySQL не была специально настроена для этой задачи

Для примеров, которые вы показали, ваши таблицы выглядят переиндексированными, и из-за большого набора данных я могу представить, что самый большой индексный файл слишком велик, чтобы поместиться в индексный кеш MySQL.

Без дополнительной информации об окружающей среде кажется, что описанное вами поведение относится к настроенным буферам. На этом этапе ОС входит и буферизует БД в памяти.

Когда вы перезапускаете MySQL, он делает хорошие и быстрые запросы, потому что ОС все еще хранит файлы в своих буферах. Когда вы прекратите доступ к БД, ОС в конечном итоге освободит файлы от буфера, и вы вернетесь к медленному начальному запросу.

Я видел такое же поведение на больших индексах, которые превышают настроенный размер буфера, но это гораздо больший набор данных. Конфигурирование БД для увеличения буферов для индексов и табличных данных решило мою конкретную проблему. В моем случае скорость запроса снизилась с 10-15 секунд до миллисекунд.

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

Если у вас есть немного свободной оперативной памяти, попробуйте немного увеличить размер пула буферов. Размер по умолчанию составляет 128 МБ, а ваш самый большой индексный файл будет примерно 279 МБ (64 байта * 4582 286 строк). Для начала попробуйте установить значение в вашей конфигурации 512Mb. Перезапустите, повторите тестирование. Если это все еще не хорошо, добавьте еще 128 МБ и повторите, пока это не работает. Если эта БД находится на выделенном компьютере, установите достаточно высокое значение (50-75% общего ОЗУ) в зависимости от настроек.

Быстрый Google придумал хорошее руководство о том, какие значения конфигурации нужно возиться. http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html

Источник
Другие вопросы по тегам