MySQL вставка вызывает большой диск ввода / вывода ждать?
Я получаю то, что появляется много времени ожидания при выполнении больших вставок в Mysql. Правильно ли я считаю, что у меня проблема с дисковым вводом-выводом? Да, я использую Mysql на виртуальной машине.
Innodb_buffer_pool hit rate is %100
innodb_flush_method = ''
innodb_log_file_size = 250M
innodb_flush_log_at_trx_commit=1
$iostat -dx 10
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
xvda 0.00 2514.09 0.10 1483.22 0.80 31958.44 21.55 39.04 26.00 0.64 94.91
xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
xvda2 0.00 2514.09 0.10 1483.22 0.80 31958.44 21.55 39.04 26.00 0.64 94.91
dm-0 0.00 0.00 0.10 3983.82 0.80 31870.53 8.00 163.34 39.84 0.24 94.91
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
xvda 0.00 2206.21 0.00 1336.24 0.00 28342.74 21.21 41.85 31.43 0.72 96.02
xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
xvda2 0.00 2206.21 0.00 1336.24 0.00 28342.74 21.21 41.85 31.43 0.72 96.02
dm-0 0.00 0.00 0.00 3542.44 0.00 28339.54 8.00 165.48 47.09 0.27 96.02
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
$ top
top - 11:57:19 up 88 days, 11:38, 3 users, load average: 1.44, 1.54, 1.60
Tasks: 80 total, 2 running, 78 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.7%us, 1.0%sy, 0.0%ni, 44.5%id, 53.2%wa, 0.0%hi, 0.0%si, 0.7%st
Mem: 16777216k total, 13791264k used, 2985952k free, 166988k buffers
Swap: 2129912k total, 44k used, 2129868k free, 7157368k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3910 mysql 15 0 13.2g 5.6g 6760 S 1.7 34.8 4:50.09 mysqld
1 root 15 0 10364 740 620 S 0.0 0.0 0:00.29 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:02.77 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root 10 -5 0 0 0 S 0.0 0.0 0:00.08 events/0
6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
7 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
9 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 xenwatch
10 root 10 -5 0 0 0 S 0.0 0.0 0:01.95 xenbus
15 root 10 -5 0 0 0 S 0.0 0.0 0:00.04 kblockd/0
16 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0
20 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 khubd
22 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod
84 root 15 0 0 0 0 S 0.0 0.0 0:00.01 khungtaskd
87 root 10 -5 0 0 0 S 0.0 0.0 1:37.10 kswapd0
88 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 aio/0
218 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kpsmoused
239 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 kstriped
248 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 ksnapd
259 root 10 -5 0 0 0 S 0.0 0.0 1:56.45 kjournald
281 root 10 -5 0 0 0 S 0.0 0.0 0:01.04 kauditd
309 root 16 -4 12632 744 408 S 0.0 0.0 0:00.37 udevd
636 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 kmpathd/0
637 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 kmpath_handlerd
656 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kjournald
3 ответа
Чтобы ответить на это просто, да.
Основным показателем является 53.2% время ожидания на процессоре. Если ваш процент ожидания ввода / вывода больше (1/# ядер ЦП), то ваши ЦП ожидают значительное количество времени, чтобы дисковая подсистема наверстала упущенное.
Вставки создают дисковый ввод-вывод, и это, как правило, наихудший вариант для виртуальных дисков с подсистемой жесткого диска. Жесткие диски обычно медленнее пишут, чем читают. Этот разрыв уменьшается с SSD, но время записи все еще медленнее, чем время чтения. Таким образом, любые операции записи будут оказывать более негативное влияние, чем операции чтения.
Кроме того, когда вы смешиваете большую базу данных и / или базу данных, которая находится под большой нагрузкой, с дисковой сибсистемой, которая не предназначена для интенсивного ввода-вывода и уже испытывает большое количество операций ввода-вывода (например, дисковая подсистема виртуальной машины).), это умножает проблему и создает серьезные проблемы с дисковым вводом / выводом.
Если не считать перемещения сервера в выделенную коробку с дисковой подсистемой, оптимизированной для SQL (что было бы вашим долгосрочным решением), лучшим вариантом будет снять любую ненужную нагрузку с виртуальной машины (например, веб-сервера), и просто запустить голые кости и SQL на нем. Кроме того, убедитесь, что вы настраиваете свои индексы и запросы, и попробуйте выполнить большинство запросов на вставку в непиковые часы, если это возможно.
Да, вы правы, у вас есть проблема с дисковым вводом / выводом. Кстати, почему вы используете innodb_flush_log_at_trx_commit=1? Он не работает хорошо в условиях высокой нагрузки, используя innodb_flush_log_at_trx_commit=2 Рекомендовано. Но я не думаю, что это поможет вам, потому что у вас большие вставки, поэтому я предполагаю, что скорость транзакций не слишком высока (не сотни в секунду). Поэтому вам следует подумать об улучшении дисковой подсистемы, добавив несколько шпинделей.
Просто чтобы быть противоположностью, я должен не согласиться с некоторым анализом. Прежде всего, я думаю, что время айовитов может быть крайне обманчивым. Все, что он действительно говорит, это то, что больше ничего не происходит, пока процессор свободен. Другими словами, низкий iowait не означает, что диск не занят или система не ждет, это просто означает, что у него есть дела поважнее, чем торчать пальцами в ожидании завершения ввода-вывода.
Лично я нахожу, что время ожидания и время обслуживания в выходных данных iostat намного более информативно, хотя я не являюсь поклонником выходного формата, поскольку считаю, что его намного сложнее читать, чем выходные данные collectl. В любом случае время ожидания в этом выводе составляет порядка 20-40 мсек, что на самом деле не так уж и плохо.
-отметка