Может ли задержка ввода / вывода привести к тому, что простое ОБНОВЛЕНИЕ займет несколько секунд в MySQL?

Question

Может ли задержка ввода / вывода привести к тому, что простое ОБНОВЛЕНИЕ займет несколько секунд в MySQL?

My MySQL приложение испытывает низкую производительность при запуске некоторых UPDATE, INSERT а также DELETE запросы. В этом вопросе я буду обсуждать только один конкретный UPDATEпотому что достаточно продемонстрировать проблему:

UPDATE projects SET ring = 5 WHERE id = 1

это UPDATE обычно достаточно быстрый, около 0,2 мс, но время от времени (достаточно, чтобы быть проблемой) это занимает несколько секунд. Вот выдержка из журнала (посмотрите на 4-ю строку):

 ~ (0.000282) UPDATE `projects` SET `ring` = 5 WHERE `id` = 1
 ~ (0.000214) UPDATE `projects` SET `ring` = 6 WHERE `id` = 1
 ~ (0.000238) UPDATE `projects` SET `ring` = 7 WHERE `id` = 1
 ~ (3.986502) UPDATE `projects` SET `ring` = 8 WHERE `id` = 1
 ~ (0.000186) UPDATE `projects` SET `ring` = 9 WHERE `id` = 1
 ~ (0.000217) UPDATE `projects` SET `ring` = 0 WHERE `id` = 1
 ~ (0.000162) UPDATE `projects` SET `ring` = 1 WHERE `id` = 1

projects таблица InnoDB с 6 столбцами типов INT а также VARCHAR17 строк и указатель на id, Это происходит и с другими таблицами, но здесь я сосредоточусь на этом. Пытаясь решить проблему, я удостоверился, что все запросы были последовательными, так что это не проблема блокировки. UPDATE выше, выполняется в контексте транзакции. Другая информация на сервере:

VPS с 4 ГБ ОЗУ (было 1 ГБ), 12 ГБ свободного дискового пространства
CentoOS 5,8 (было 5,7)
MySQL 5.5.10 (был 5.0.x)

"Было" немного выше означает, что он не работал до или после обновления.

То, что я пробовал до сих пор, безрезультатно:

настройка innodb_flush_log_at_trx_commit до 0, 1 или 2
настройка innodb_locks_unsafe_for_binlog вкл или выкл
настройка timed_mutexes вкл или выкл
изменения innodb_flush_method по умолчанию O_DSYNC или же O_DIRECT
Увеличение innodb_buffer_pool_size по умолчанию до 600M, а затем до 3000M
Увеличение innodb_log_file_size по умолчанию до 128M
Компиляция MySQL из исходного кода
Бег SHOW PROCESSLIST, который сообщает мне, что состояние "обновляется"
Бег SHOW PROFILE ALLЭто говорит о том, что почти все время было потрачено на "обновление", и что на этом этапе не так много времени было потрачено на циклы ЦП и было много произвольных переключений контекста (например, 30).
мониторинг SHOW STATUS для изменений в Innodb_buffer_pool_pages_dirty, Может быть некоторая связь между удаляемыми грязными страницами и медленными запросами, но корреляция не ясна.

Затем я решил проверить задержку ввода / вывода системы с ioping, Это мой первый VPS, поэтому я был удивлен, увидев такой результат:

4096 bytes from . (vzfs /dev/vzfs): request=1 time=249.2 ms
4096 bytes from . (vzfs /dev/vzfs): request=2 time=12.3 ms
4096 bytes from . (vzfs /dev/vzfs): request=3 time=110.5 ms
4096 bytes from . (vzfs /dev/vzfs): request=4 time=232.8 ms
4096 bytes from . (vzfs /dev/vzfs): request=5 time=294.4 ms
4096 bytes from . (vzfs /dev/vzfs): request=6 time=704.7 ms
4096 bytes from . (vzfs /dev/vzfs): request=7 time=1115.0 ms
4096 bytes from . (vzfs /dev/vzfs): request=8 time=209.7 ms
4096 bytes from . (vzfs /dev/vzfs): request=9 time=64.2 ms
4096 bytes from . (vzfs /dev/vzfs): request=10 time=396.2 ms

Я бы сказал, довольно странно.

Сказав все это, я спрашиваю:

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

8

mysql vps disk-io

Источник

user1155219 11 мар '12 в 14:12

4 ответа

Решение

У вас проблема с вводом-выводом, связанная с VPS. Это не вина MySQL.

Вы случайно не используете Elastic Block Store с Amazon или, возможно, RDS? Оба используют удаленное хранилище и уровень протокола IP для связи с хранилищем; они могут иногда иметь неприятное отставание.

1

Источник

user850473 13 мар '12 в 17:53

Поскольку вы размещаете свой VPS в облаке, вы можете столкнуться с проблемами, которые находятся вне вашего контроля.

VPS подчиняются прихотям хост-серверов, на которых они работают. Например, приоритет цикла ЦП в Rackspace Cloud взвешивается в зависимости от размера VPS. Чем больше ваш VPS, тем выше вероятность того, что ваше приложение будет работать без сбоев. Если на хосте, который вы используете, установлен больший VPS, вполне возможно, что виноват взорванный вес. Сложно сказать.

Вы пытались запустить это локально на своей машине? Если он отлично работает в вашей собственной системе, и вам нужна гарантированная производительность, тогда вам лучше всего перейти на выделенный сервер.

1

Источник

user302939 13 мар '12 в 06:38

Вопрос 1) Да.

Чтобы проверить это, напишите 2 приложения:

Тестовый пример 1: будет делать это каждую минуту в течение нескольких часов

UPDATE `projects` SET `ring` = 5 WHERE `id` = 1
UPDATE `projects` SET `ring` = 6 WHERE `id` = 1

Контрольный пример 2: будет делать это каждую минуту в течение нескольких часов

UPDATE `projects` SET `ring` = 7 WHERE `id` = 1
UPDATE `projects` SET `ring` = 8 WHERE `id` = 2

Тестовый пример 1 должен иметь задержку, а тестовый пример 2 - нет.

Вопрос 2) Используйте базу данных noSQL.

0

Источник

user726086 12 мар '12 в 21:15

Другие вопросы по тегам mysql vps disk-io

user1155219 14 мар '12 в 03:44 2012-03-14 03:44 · Accepted Answer · 2012-03-14 03:44

Я отвечаю на свой вопрос с дополнительными данными, которые я собрал на основе ваших ответов.

Я использовал два ноутбука, соединенных с помощью беспроводной сети. На ноутбуке A я смонтировал каталог ноутбука B, используя sshfs, Затем на ноутбуке A I запустил MySQL, указав этот смонтированный каталог в качестве своего каталога данных. Это должно обеспечить MySQL очень медленным устройством ввода-вывода. MySQL был запущен сinnodb_flush_log_at_trx_commit = 0,

Я определил 3 набора запросов, каждый из которых состоит из обновления и запроса выбора, повторенного 10000 раз, без явных транзакций. Эксперименты были:

US1SID: обновить и выбрать определенную строку в той же таблице. Одна и та же строка использовалась во всех итерациях.
US1MID: обновить и выбрать определенную строку в той же таблице. Строка была разной в каждой итерации.
US2MID: обновить и выбрать строки разных таблиц. В этом случае таблица, читаемая селектором, не изменилась вообще во время эксперимента.

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

tc qdisc replace dev wlan0 root handle 1:0 netem delay 200ms

Приведенная выше команда добавляет среднюю задержку в 200 мс при передаче пакетов через wlan0.

Во-первых, вот среднее время самых высоких 99% самых быстрых обновлений и выборов, а нижние 1% обновляются и выбираются.

          |        Delay: 0ms        |       Delay: 200ms       |
          | US1SID | US1MID | US2MID | US1SID | US1MID | US2MID |
| top99%u | 0.0064 | 0.0064 | 0.0064 | 0.0063 | 0.0063 | 0.0063 |
| top99%s | 0.0062 | 0.0063 | 0.0063 | 0.0062 | 0.0062 | 0.0062 |
| bot01%u | 1.1834 | 1.2239 | 0.9561 | 1.9461 | 1.7492 | 1.9731 |
| bot01%s | 0.4600 | 0.5391 | 0.3417 | 1.4424 | 1.1557 | 1.6426 |

Ясно, что даже при очень низкой производительности ввода / вывода MySQL удается выполнять большинство запросов действительно быстро. Но больше всего меня беспокоит наихудший случай, так что вот еще одна таблица, показывающая 10 самых медленных запросов. "U" означает, что это было обновление, "s" - выбор.

|          Delay: 0ms         |          Delay: 200ms          |
| US1SID  | US1MID  | US2MID  | US1SID   | US1MID   | US2MID   |
| 5.443 u | 5.946 u | 5.315 u | 11.500 u | 10.860 u | 11.424 s |
| 5.581 u | 5.954 s | 5.466 u | 11.649 s | 10.995 u | 11.496 s |
| 5.863 s | 6.291 u | 5.658 u | 12.551 s | 11.020 u | 12.221 s |
| 6.192 u | 6.513 u | 5.685 u | 12.893 s | 11.370 s | 12.599 u |
| 6.560 u | 6.521 u | 5.736 u | 13.526 u | 11.387 u | 12.803 u |
| 6.562 u | 6.555 u | 5.743 u | 13.997 s | 11.497 u | 12.920 u |
| 6.872 u | 6.575 u | 5.869 u | 14.662 u | 12.825 u | 13.625 u |
| 6.887 u | 7.908 u | 5.996 u | 19.953 u | 12.860 u | 13.828 s |
| 6.937 u | 8.100 u | 6.330 u | 20.623 u | 14.015 u | 16.292 u |
| 8.665 u | 8.298 u | 6.893 u | 27.102 u | 22.042 s | 17.131 u |

Выводы:

Низкая производительность ввода-вывода действительно может замедлить сканирование MySQL. Непонятно почему или когда именно, но это случается.
Замедление применяется как к выборкам, так и к обновлениям, причем обновления страдают больше.
По какой-то причине даже выборки в таблице, которые не были вовлечены в какие-либо изменения и которые недавно были заполнены, также были замедлены, как видно из US2MID выше.
Что касается тестовых случаев, предложенных mentatkgs, кажется, что обновление разных строк вместо одних помогает немного, но не решает проблему.

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

Спасибо всем за комментарии.