MySQL медленная обработка запросов
у меня есть AWS EC2 экземпляр с DUAL-CORE а также 4 GB Memory, Я настроил мой Apache2 HTTP server Бег PHP "7.0.30" а также MySQL "Ver 14.14 Distrib 5.7.22",
Существуют различные устройства, которые отправляют GET/POST просьба к моему Http server, Каждое сообщение и получить запрос с помощью запросов выбора и обновления.
Щас вокруг 200 устройств которые бьют меня Http server одновременно и ударяя SQL queries выбрать и обновить вместе. Эти хиты содержат данные в JSON форматы.
Проблема в том, что мой сервер MYSQL стал слишком медленным. Сбор данных по отдельным запросам и загрузка страниц занимает много времени.
От phpMyAdminЯ вижу ряд спящих процессов в статусе для запросов. Я также настроил различные параметры моего SQL server но нет результата.
Одним из основных запросов, который требует времени, является запрос на обновление, который обновляет длинные текстовые данные в таблице и поступает с устройства через каждые 60 секунд одновременно, и мы видим его процессы пустыми после длительного периода времени в состоянии сервера MYSQL.
Есть ли способ оптимизировать его с помощью параметров SQL, чтобы сервер MYSQL работал быстро даже при тысячах запросов с несколькими подключениями, которые обновляют столбец таблицы с длинным текстом?
Большинство глобальных переменных имеют значения по умолчанию. Я также попытался изменить значения переменных различных глобальных, но это не дало никакого результата.
Как я могу уменьшить эту медленную обработку запросов?
П.С .: Я считаю, что проблема связана с запросами на обновление. Я настроил запросы Select, и они, кажется, в порядке. Но для запросов UPDATE я вижу до 12 секунд на вкладке «Процессы» в phpMyAdmin.
Я добавил ссылку на изображение, имеющее эту проблему
(Здесь вы можете увидеть спящие даже 13 секунд, все в запросах ОБНОВЛЕНИЯ):
Вот PasteBin для запроса операции UPDATE:
https://pastebin.com/kyUnkJmz
Решение
Это ~ 25 КБ для JSON! (Может быть, 22 КБ, если обратный слеш исчезает.) И 40 вставок в секунду, но чаще каждые 2 минуты.
Я бы хотел увидеть SHOW CREATE TABLE, но я все еще могу сделать некоторые комментарии.
- В InnoDB эта большая строка будет храниться «вне записи». То есть будет дополнительный удар по диску, чтобы написать эту большую строку в другом месте.
- Сжатие JSON должно уменьшить его до 7K, что может привести к хранению этой большой строки в строке, тем самым сокращая немного на ввод / вывод. Выполните сжатие на клиенте, чтобы сократить сетевой трафик. И сделать колонку
BLOBнеTEXT, - Вращающиеся приводы могут обрабатывать около 100 операций ввода-вывода в секунду.
- 200 устройств каждые 5 секунд должны в среднем 40 записей в секунду, чтобы не отставать. Это нормально.
- Каждые 2 минуты добавляются 40 записей. Это может (или не может) увеличивать количество операций ввода-вывода за пределы того, что может обрабатывать диск. Это может быть непосредственной причиной показанного вами «обновления в течение 13 секунд». Этот снимок был сделан вскоре после 2-минутной границы?
- Или устройства не синхронизированы? То есть POST приходят все одновременно или они распределяются по 2 минутам?
- Если каждое обновление является отдельной транзакцией (или вы работаете с
autocommit=ON), то есть дополнительная запись — для целостности транзакции. Это можно отключить (компромисс между скоростью и безопасностью):innodb_flush_log_at_trx_commit = 2, Если вы не возражаете, рискуя данными за 1 секунду, это может быть простым решением. - Что-нибудь еще происходит со столом? Или это только эти обновления?
- Я надеюсь, что вы используете InnoDB (именно на это направлены мои замечания выше), потому что MyISAM будет спотыкаться о себя с фрагментацией.
- Длинные «сны» не проблема; длинные «обновления» являются проблемой.
Больше
- Иметь индекс на
usermacтаким образомUPDATEне нужно пролистывать всю таблицу в поисках нужной строки. Вы могли бы броситьidи добавитьPRIMARY KEY(usermac), - Некоторые из вышеприведенных комментариев отклоняются в 8 раз — кажется, в таблице 8 столбцов JSON, следовательно, 200 КБ / строка.
Другие решения
Других решений пока нет …