Мы используем таблицы InnoDB в качестве серверной части веб-приложения, и все было хорошо около двух лет, пока нам не пришлось перезапускать MySQL несколько недель назад. (Мы не отключили обратный поиск DNS, хотя на самом деле мы их не использовали, но наша хостинговая система внезапно перестала отвечать на эти запросы. Теперь они отключены.) К сожалению, файл конфигурации был изменен, и мы этого не делаем. У меня есть копия исходного состояния для сравнения.
После устранения наиболее серьезных проблем возникает настоящая загадка: при высокой нагрузке запросы к базе данных начинают занимать гораздо больше времени, чем обычно. В это время у нас есть несколько сотен открытых подключений с наших семи серверов Apache. Запуск SHOW PROCESSLIST показывает, что половина или более этих подключений находятся в состоянии «Отправка данных», часто с временами в несколько сотен секунд. Почти все их запросы - это SELECT, при этом похожие запросы имеют тенденцию группироваться. Фактически, самый нижний кластер в списке, как правило, был точно таким же запросом (я ожидал, что он будет в кеше запросов), возвращая 1104 строки по два целых числа в каждой. Другими частыми нарушителями являются списки из нескольких сотен строк с одним целым числом, нескольких строк с одним целым числом или даже с одним результатом COUNT (*).
Мы пытались выключить веб-серверы в течение одного из этих периодов, но проблема вернулась через минуту после их перезапуска. Однако полный перезапуск mysqld решил проблему до следующего дня. В чем может быть проблема, и как мы можем ее проверить и / или исправить?
Что ж, обратите внимание, что, если я хорошо помню (прошло много времени с тех пор, как я работал с БД), запросы COUNT (*) без предложения WHERE в таблицах innodb, как известно, медленнее, чем в таблицах MyISAM и Memory.
Кроме того, это случайно не Xen DomU?
Какой язык интерфейса? Если PHP, он использует MySQL или MySQLi? Они используют постоянные соединения?
Вы не упомянули базовую операционную систему, но в случае Linux я бы начал с просмотра вывода free -m, обращая особое внимание на последние две строки, чтобы увидеть, не хватает ли памяти в целом.
[0:504] callisto:cyanotype $ free -m
total used free shared buffers cached
Mem: 3961 3816 144 0 184 1454
-/+ buffers/cache: 2177 1784
Swap: 2898 0 2898
Здесь у нас есть исправная система (это моя рабочая станция). Второй столбец исключает буферы и кеш, поэтому я фактически использую 2177 МБ памяти, а 1784 мегабайта легко доступны.
Последняя строка показывает, что я пока вообще не использую своп.
Затем давая vmstat(8), тоже было бы полезно увидеть, не работает ли ваша система как сумасшедшая.
[0:505] callisto:cyanotype $ vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 134116 189828 1499948 0 0 11 3 44 49 1 1 98 0
0 0 0 143112 189836 1489688 0 0 0 6 526 2177 1 1 98 0
0 0 0 139268 190504 1491864 0 0 512 4 663 4704 2 1 96 1
2 0 0 136688 191084 1493484 0 0 473 5 641 3039 1 1 97 1
0 0 0 52636 191712 1518620 0 0 5066 4 1321 6600 8 2 86 4
5 0 0 72992 193264 1377324 0 0 10742 31 1602 7441 12 3 80 5
2 1 0 84036 193896 1202012 0 0 10126 43 2621 4305 31 2 57 10
3 0 0 42456 195812 1060904 0 0 3970 75 55327 9806 43 5 41 10
8 1 0 34620 197040 942940 0 0 3554 64 50892 12531 43 6 44 6
^C
[0:506] callisto:cyanotype $
(Мой рабочий стол здесь действительно не так уж и много, извините. Какая трата 8 совершенно хороших ядер)
Если в столбце «b» вы видите, что многие процессы проводят время, это означает, что они заблокированы и чего-то ждут. Часто это IO. Важные столбцы здесь: si и so. Проверьте, не заполнены ли они высокими значениями. Если да, то это может быть вашей проблемой - что-то потребляет много памяти, больше, чем вы можете себе позволить. С помощью top(4) и упорядочивание столбцов по% памяти (shift + m наверху) может показать виновника (-ов).
Не исключено, что ваша система переключает свопинг и обратно, а также насыщает диски, вызывая блокировку потоков и процессов. iostat(8)(часть пакета sysstat, обычно) следует проверить, есть ли у вас процессы, которые заблокированы, застряли на IO_WAIT. Переполненный диск может означать плохие новости для всей системы при высокой нагрузке, особенно если система часто меняет местами.
Вы можете запускать iostat с расширенной статистикой каждые пять секунд, например:
[0:508] callisto:cyanotype $ iostat -x 5
Linux 2.6.35-23-generic (callisto) 2010-11-30 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
16,55 0,12 2,70 2,60 0,00 78,02
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 0,00 2,00 1,00 0,80 27,20 22,40 27,56 0,01 3,33 3,33 0,60
sdd 0,00 12,60 67,60 4,80 4222,40 139,20 60,24 0,62 8,62 3,29 23,80
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
32,02 0,10 1,83 0,44 0,00 65,61
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 0,60 3,20 11,00 0,80 265,60 32,00 25,22 0,05 3,90 2,88 3,40
sdd 0,00 8,20 0,00 3,00 0,00 89,60 29,87 0,02 8,00 7,33 2,20
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
49,26 0,22 3,12 0,12 0,00 47,28
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 6,20 3,00 7,40 3,80 208,00 54,40 23,43 0,09 7,86 2,50 2,80
sdd 0,00 15,20 0,20 4,00 1,60 152,00 36,57 0,03 6,67 6,19 2,60
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
16,00 0,54 1,05 1,07 0,00 81,35
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 4,20 0,00 31,40 0,00 3204,80 0,00 102,06 0,17 4,90 2,68 8,40
sdd 0,00 28,20 0,20 2,60 1,60 246,40 88,57 0,02 7,14 7,14 2,00
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
^C
Это должно позволить вам легко увидеть, насыщаются ли ваши тома. Например, здесь вы можете видеть, что мои диски ужасно недоиспользуются, что система тратит большую часть своих циклов процессора в режиме ожидания и т. Д. И т. Д. Если этот процент в основном находится в столбце% IOWAIT, значит, у вас здесь узкое место ввода-вывода. Вы, наверное, уже все это знаете, но для уверенности достаточно охватить все основы.
Идея состоит в том, что ваш файл конфигурации изменился, и у вас нет его истории (помещение ваших файлов конфигурации под контроль версий - отличная идея именно по этой причине) - и не исключено, что размер буфера внезапно изменился, что сделало дорого запросы вроде COUNT (*) без SELECT внезапно начинают поглощать ресурсы.
Основываясь на том, что вы узнали из предыдущего использования инструментов, вам, вероятно, следует проверить файл конфигурации (это единственное, что изменилось, очень вероятно, что это виновник), чтобы увидеть, подходят ли значения буфера для вашей средней нагрузки .
Насколько велики буферы, такие как query_cache_size ценность, и особенно sort_buffer размеры? (Если это не умещается в памяти, это будет выполняться на диске с огромными затратами, как я уверен, вы можете себе представить).
Насколько велик innodb_buffer_pool_size?
Насколько велик table_cacheи, что наиболее важно, соответствует ли это значение системным ограничениям для дескрипторов файлов? (как open-files-limit в [mysqld], так и на уровне ОС).
Кроме того, я не помню, верно ли это, но я вполне уверен, что innodb фактически блокирует всю таблицу всякий раз, когда ему нужно зафиксировать поля с автоинкрементом. Я googledd, и я не мог найти, правда ли это до сих пор или нет.
Вы также можете использовать innotop(1) чтобы узнать, что происходит, более подробно.
Надеюсь, это как-то поможет или даст вам отправную точку :)
Это оказалось недостатком в сочетании innodb_file_per_table, default-storage-engine = innodbи часто просматриваемая страница, на которой создана временная таблица. Каждый раз, когда соединение закрывается, таблица удаляется, отбрасывание страниц из буферного пула LRU. Это приведет к тому, что сервер немного остановится, но никогда не будет обрабатывать запрос, который на самом деле вызывал проблему.
Хуже того, innodb_file_per_table обстановка томилась в наших my.cnf file в течение нескольких месяцев, прежде чем пришлось перезапускать сервер по совершенно не связанной с этим причине, и в течение этого времени мы без проблем использовали эти временные таблицы. (NOC внезапно отключил DNS-сервер, в результате чего каждое новое соединение зависало, потому что мы не включили skip-name-resolveи часами не признавал, что что-то изменилось.)
К счастью, мы смогли переписать проблемную страницу, чтобы использовать еще более быстрый набор запросов, которые загружали большую часть работы на интерфейсные веб-серверы и с тех пор не видели никаких проблем.