MySQL Query Randomly Lags

У меня есть запрос, который выглядит так:

SELECT id FROM user WHERE id='47'

Идентификатор индексируется, и чтение для этого запроса всегда выполняется при использовании профилирующих данных, например.

SET profiling = 1;
SHOW PROFILES;

Запросы всегда выполняются примерно за 0,0002 секунды.

Однако, если я профилирую запрос со стороны PHP, например:

$current = microtime(true);
$data = $conn->query($full_query);
$elapsed = microtime(true) - $current;

Тогда иногда, возможно, 1 из 50 из этих запросов займет примерно 2 сек. Тем не менее, в моем тесте script у меня есть код для проверки этого, который профилирует запрос, используя SET profiling = 1; и даже несмотря на то, что поездка в PHP через PDO может быть .2 секунды, время запроса было еще 0,0002.

Вещи, которые я знаю, или знаю, что это не вызывает проблемы:

Запрос не медленный. Когда я смотрю на тот же запрос, из одного и того же запроса, профилированного на PHP и профилированного с использованием SET PROFILING, запрос всегда быстр и никогда не регистрируется в журнале медленных запросов, даже когда он показывает, что занимает 0,2 секунды со стороны PHP.
Это не связанное с пропуском имя-разрешение - это непоследовательно, и у меня уже есть разрешение на имя пропущенного имени
Это не связанный с кешем запросов, поведение существует как в
Такое поведение происходит даже по запросам, выходящим из кеша.
Запрос фактически не отображает идентификатор, но я использую этот запрос для тестирования, чтобы показать, что он не является проблемой доступа к диску, поскольку это поле определенно индексируется.
Эти таблицы составляют всего 10-20 мегабайт с чем-то вроде индекса 1 мега. Машина показывает очень мало нагрузки, и innodb не использует все свои буферы.
Это проверено на таблицу, которая не имеет других действий против нее, кроме моих тестовых запросов.

Есть ли у кого-нибудь идеи, что еще проверить? Мне кажется, что это сетевая проблема, но мне нужно уметь ее видеть и найти проблему, чтобы исправить ситуацию, и у меня заканчивается ситуация, чтобы проверить ее. Любые идеи?

Ответы

Ответ 1

Я бы прокомментировал машину.

Вы говорите, что это происходит ~ 1 в 50 раз, и каждый запрос имеет 0,2-секундный тест. Вы должны уметь накладывать верхнюю часть на экран, а затем запускать цикл запросов в PHP для загрузки тестов RDBMS и сбора статистики производительности.

Вероятно, вам придется запускать больше 50 * 0.2 = 10 seconds, поскольку ваша статистика "1 из 50", вероятно, основана на ручных индивидуальных запросах - на основе того, что я читал в ваше описание. Попробуйте выполнить 30-секундные и 90-секундные тесты нагрузки.

В течение этого времени просмотрите экран процесса top. Сортируйте его по CPU, нажав P. Каждый раз, когда вы нажимаете "P", он изменяет порядок сортировки для потребления процессора и процессора, поэтому убедитесь, что у вас есть самое большое потребление. (нажатие M сортирует по использованию памяти. man page).

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

Ответ 2

Я заметил одно и то же явление в своих системах. Запросы, которые обычно занимают миллисекунду, внезапно занимают 1-2 секунды. Все мои дела просты, одиночные таблицы INSERT/UPDATE/REPLACE - не на всех SELECT. Никакая нагрузка, блокировка или нить накапливаются.

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

Также исключен

Загрузка сервера - отсутствие корреляции с высоким
load Engine - происходит с запросом InnoDB/MyISAM/Memory MySQL
Кэш - бывает ли он включен или выключен
Логарифмические вращения - отсутствие корреляции в событиях

Ответ 3

Хорошо, что вы уже использовали профилировщик запросов. Если вы используете MySQL 5.6, у вас также есть доступ к множеству новых измерений производительности в PERFORMANCE_SCHEMA. Это позволяет измерять намного больше деталей, чем профилировщик запросов, а также измеряет глобальность вместо одного сеанса. Сообщается, что P_S заменит прокси-сервер запроса.

Чтобы диагностировать вашу проблему, я бы начал с подтверждения или исключения проблемы с TCP/IP. Например, протестируйте PHP script, чтобы узнать, получает ли он такую же прерывистую задержку при подключении через сокет UNIX. Вы можете сделать это, подключившись к localhost, что означает, что PHP скрипт должен запускаться на том же сервере, что и база данных. Если проблема уходит, когда вы обходите TCP/IP, это скажет вам, что основной причиной может быть TCP/IP.

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

Если вы подозреваете, что это проблема TCP/IP, вы можете проверить задержку TCP/IP независимо от PHP или MySQL. Типичные доступные инструменты включают ping или traceroute. Но есть многие другие. Вы также можете проверить скорость сети с помощью netcat. Используйте инструмент, который может измерять многократно с течением времени, потому что это звучит так, как будто у вас хорошая производительность в большинстве случаев со случайными сбоями.

Другая возможность заключается в том, что ошибка заключается в PHP. Вы можете попробовать профилировать PHP с помощью XHProf, чтобы узнать, где он проводит свое время.

Ответ 4

Попробуйте изолировать проблему. Запустите немного script следующим образом:

https://drive.google.com/file/d/0B0P3JM22IdYZYXY3Y0h5QUg2WUk/edit?usp=sharing

... чтобы увидеть, какие шаги в цепочке пикируются. Если у вас установлен ssh2, он также вернет ps axu сразу после самого длинного тестового цикла, чтобы увидеть, что работает.

Запуск против локального хоста в моей домашней коробке разработки, результаты выглядят так:

Результаты ps axu здесь довольно бесполезны, потому что я подключаюсь к localhost. Но из этих результатов видно, что время задержки подключения к базе данных иногда возникает, как и "сетевая" латентность (некоторый буфер TCP/IP?).

Если бы я был вами, я бы ударил число тестовых циклов до 5000 или 50000.

Ответ 5

Я могу просто угадать, но поскольку вы исключили нагрузку на сервер, и я предполагаю, что вы проверили флаги с красными индексами в InnoDb-Stats (phpmyadmin - отличная помощь для этого, хотя есть более профессиональные инструменты), остаётся непоследовательное использование ключей. Может ли быть так, что ваш запрос немного изменился и что существует созвездие, в котором используются субоптимальные индексы?

Добавьте FORCE INDEX PRIMARY или повторите свои тесты.

Ответ 6

Что-то, что я нашел очень полезным при диагностике проблем MySQL в этом ключе, mysqltuner. Это PERL script, который смотрит на ваш экземпляр MySQL и предлагает различные улучшения настройки. Честно говоря, сложно отслеживать всю настройку, которую вы можете сделать, и этот script является потрясающим для того, чтобы дать вам пробой потенциальных точек засоса.

Что-то еще, чтобы рассмотреть, как работает сам Linux, что также может объяснить, почему вы случайно отстаете. Когда вы загружаете top в ящик Linux (любое поле, независимо от загрузки), вы заметите, что ваша память почти полностью используется (если только вы не перезагрузились). Это не проблема или перегрузка вашей коробки. Linux загружает столько, сколько может, в оперативную память, чтобы сэкономить время и обменять нечасто используемые вещи в ваш файл подкачки, как и все современные операционные системы (называемые виртуальными RAM). Обычно это не большая проблема, но вы, вероятно, используете InnoDB как тип таблицы (текущий по умолчанию), который загружает вещи в ОЗУ, чтобы сэкономить время. Что может случиться, ваш запрос загрузился в оперативную память (быстро), но сидел без дела достаточно долго, чтобы поменяться на файл подкачки (гораздо медленнее). Таким образом, вы получите небольшой выигрыш в производительности, в то время как Linux переместил его обратно в ОЗУ (swapfiles более эффективны, чем MySQL будет перемещать его с диска). Ни MySQL, ни InnoDB не могут сказать об этом, потому что, насколько они есть, он все еще находится в ОЗУ. Эта проблема подробно описана в в этом блоге, при этом соответствующая часть

Обычно крошечный бит использования подкачки может быть в порядке (был действительно об активности и свопингах), но во многих случаях "реальные" полезные память заменяется: в основном это часть пула буферов InnoDB. когда его необходимо еще раз, большой удар производительности, чтобы поменять его обратно в, вызывая случайные задержки в случайных запросах. Это может привести к непредсказуемая производительность на производственных системах, и часто один раз система обмена может войти в спираль смерти производительности.

Ответ 7

Мы выяснили, что проблема с основным оборудованием вызвала это. Мы переместили сервер на новое оборудование с помощью VMotion, и проблема исчезла. VMWare не показывал предупреждения или проблемы с оборудованием. Тем не менее, устранение этого аппаратного обеспечения устранило проблему. Очень странно.