Почему запросы MySQL накапливают в “Передающих данных” состояние?
Оказалось, что это недостаток в комбинации innodb_file_per_table , default-storage-engine = innodb и часто просматриваемая страница, на которой создана временная таблица. Каждый раз при закрытии соединения таблица удалялась , а страницы из пула буферов LRU отбрасывались. Это заставит сервер ненадолго остановиться, но никогда не будет обрабатывать запрос, который действительно вызвал проблему.
Хуже того, параметр innodb_file_per_table томился в нашем файле my.cnf в течение нескольких месяцев, прежде чем сервер пришлось перезапустить по совершенно не связанной причине, в течение которых мы использовали эти временные таблицы без проблема. (NOC внезапно отключил DNS-сервер, в результате чего каждое новое соединение зависало, потому что мы не включили skip-name-resolve и часами не признавали, что что-то изменилось.)
К счастью, мы смогли переписать проблемную страницу, чтобы использовать еще более быстрый набор запросов, которые загружали большую часть работы на интерфейсные веб-серверы и с тех пор не видели никаких проблем.
cnf за несколько месяцев до перезапуска сервера по совершенно не связанной причине, в течение которых мы без проблем использовали эти временные таблицы. (NOC внезапно отключил DNS-сервер, в результате чего каждое новое соединение зависало, потому что мы не включили skip-name-resolve и часами не признавали, что что-то изменилось.)
К счастью, мы смогли переписать страницу с нарушением, чтобы использовать еще более быстрый набор запросов, которые загружали большую часть работы на интерфейсные веб-серверы и с тех пор не обнаружили никаких проблем.
cnf за несколько месяцев до перезапуска сервера по совершенно не связанной причине, в течение которых мы без проблем использовали эти временные таблицы. (NOC внезапно отключил DNS-сервер, в результате чего каждое новое соединение зависало, потому что мы не включили skip-name-resolve и часами не признавали, что что-то изменилось.)
К счастью, мы смогли переписать страницу с нарушением, чтобы использовать еще более быстрый набор запросов, которые загружали большую часть работы на интерфейсные веб-серверы и с тех пор не обнаружили никаких проблем.
Ну, действительно обратите внимание, что, если я вспоминаю хорошо (это было некоторое время, так как я сделал работу DB) КОЛИЧЕСТВО (*) запросы без оператора Where на innodb таблицах известно медленнее, чем на таблицах MyISAM и Memory.
Кроме того, это - случайно Xen DomU?
Каков frontend язык? Если 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 МБ памяти и имею легко доступные 1 784 мегабайта.
Последняя строка показывает, что я не использую подкачку вообще до сих пор.
Затем предоставление 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
Это должно позволить Вам легко видеть, насыщаются ли Ваши объемы. Например, здесь, Вы видите, что мои диски ужасно недостаточно используются, что система тратит большую часть своего бездействия циклов CPU и т.д. и т.д. Если тот процент находится главным образом в столбце IOWAIT %, хорошо у Вас есть узкое место IO здесь. Вы, вероятно, уже знаете, что все это, но просто покрывающий все основания удостоверяется.
Идея состоит в том, что Ваш файл конфигурации изменился, и у Вас нет истории его (подвергание Ваших файлов конфигурации при управлении версиями является прекрасной идеей по той самой причине) - и это не невозможно размер буфера suddendly измененный таким образом, делающие дорогие запросы как КОЛИЧЕСТВО (*) без ВЫБОРА suddendly начинают проглатывать ресурсы.
На основе того, что Вы узнали из предыдущего использования инструментов abive - необходимо, вероятно, осмотреть конфигурационный файл (являющийся единственной вещью, которая изменилась, это вероятно преступник) видеть, нормальны ли буферности для средней загрузки.
Насколько большой буферы, как query_cache_size значение, и особенно sort_buffer размеры? (Если это не уместится в памяти, то это будет выполненный на диске по крупной стоимости, поскольку я уверен, что можно вообразить).
Насколько большой innodb_buffer_pool_size?
Насколько большой table_cacheи самое главное, который оценивает соответствия в системных пределах для дескрипторов файлов? (и открытый предел файлов в [mysqld] и на уровне ОС).
Кроме того, я не помню первое, что пришло на ум, если это все еще верно, но я вполне уверен, что innodb на самом деле блокирует всю таблицу каждый раз, когда это должно фиксировать автоинкрементные поля. Я погуглил, и я не мог найти, было ли это все еще верно или нет.
Вы могли также использовать innotop(1) видеть, что продолжается больше подробно, также.
Я надеюсь, что это помогает так или иначе или дает Вам начальную точку :)
Вероятно, этому есть много причин. В нашем конкретном случае это происходило, когда количество наблюдаемых запросов резко увеличивалось в течение короткого промежутка времени, что вызывало перегрузку ЦП, поскольку количество потоков превышало в 4 раза количество ядер на сервере. Наша проблема заключается в том, что скачок количества запросов на самом деле является нормальным для нашего приложения, и реализация POSIX работала приемлемо «большую часть» времени, но периодически останавливалась. После долгих исследований мы наткнулись на корпоративный плагин Oracle mySQL под названием thread-pool, который предлагает альтернативную реализацию для обработки потоков. Еще лучше - сервер Percona реализовал это изначально (плагин не нужен), и изменение было однострочным для тестирования в нашем файле cnf. В результате производительность при тяжелых нагрузках значительно улучшилась. Хотя это вряд ли будет проблемой для многих реализаций, я надеюсь, что это может быть для некоторых, и это простое изменение стоит протестировать.
а вот еще один пример использования: