Почему SSD загружается на 100% при INSERT в Mysql?

Question

[Макс]

Доброго дня!
Есть сервер (ram 64GB) с SSD диском (500gb samsung), сервер не нагружен.
Как только начинаю делать INSERT в таблицы mysql через PHP, то диск просто "умирает" - 100% IO загружаются процессом [jbd2/sda3-8], при этом на SELECT все отлично работает с множеством сайтов.
На что обратить внимание и куда смотреть?
Вот как выглядит записал вывод iotop
https://drive.google.com/file/d/13xa80U9DvCjGxAu5z...

Конфиг mysql

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
lc-messages-dir=/usr/share/mysql
log_error=/var/log/mysql/error.log

symbolic-links=0

skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 32M
#table_open_cache = 256
#sort_buffer_size = 1M
#read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
#query_cache_size= 16M
query_cache_size = 0 #tuner
query_cache_type = 0 #tuner
query_cache_limit = 1M #tuner
join_buffer_size = 128M #tuner
sort_buffer_size = 128M #my initiative
read_rnd_buffer_size = 128M #my initiative
tmp_table_size = 4GB #tuner
max_heap_table_size = 4GB #tuner

innodb_buffer_pool_instances = 8 #my initiative  https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances рекомендуют устанавливать не больше чем физических ядер
# Должно высчитываться по формуле ( innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size)
innodb_buffer_pool_size = 48GB # tuner
innodb_log_files_in_group = 2 #tuner
innodb_log_file_size = 6GB #tuner
#innodb_additional_mem_pool_size = 128MB #my initiative - DEPRECATED
innodb_log_buffer_size = 1GB #my initiative можно и до 15% размера журналов  A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. 
#innodb_use_native_aio = 0
innodb_file_per_table

#The open_files_limit should typically be set to at least 2x-3x
#that of table_cache if you have heavy MyISAM usage.
#FORMULA : 10 + max_connections + (table_open_cache * 2)
table_open_cache = 5000 #tuner
open_files_limit = 5000 #tuner

#max_connections=200
max_connections=100
max_user_connections=50
wait_timeout=10
interactive_timeout=28800 # for Scripts and Scrapers! (Was 10)
long_query_time=5

!includedir /etc/mysql/conf.d/

P.S. На локальном компе на SSD все работает значительно быстрее именно на запись.

Comments

[Макс]

Нашел временное решение, похоже дело в Innodb и массовом инсерте. Таблица содержит 2 индекса (autoincrement + unique на int), как я понял идет пересоздание индекса для всей таблицы на каждом инсерте, из-за чего диск умирает от нагрузки.
Решения :
1. Использовать Myisam (не пробовал, может быть не поможет)
2. Transaction перед большим блоком инсерта. Поставил транзакции на каждые 10к запросов, полет нормальный - SSD больше 10% не грузится теперь.

Что можно поменять в конфиге? Пытаюсь найти настройку при которой инсерт происходил бы при достижнии определенного размера кэша инсерта (например), индексы бы ребилдились также вместе с ним. Есть такое?

Answer 1

[Swartalf]

0. Я бы не переходил бы с innodb на myisam, обычно переходят наоборот. В Myisam много проблем решенных в innodb.
1. Делать множественные одиночные инсерты = зло. Транзакции и куски по 1/10/20/etc строк правильней.
2. Какое значение innodb_flush_log_at_trx_commit ? попробуте выставить значение 2, про риски см. линк.
3. Так же есть innodb_doublewrite . Что бы снизить нагрузку на диск, можете попробовать отключить, но тут же опять на ваш страх и риск.

Comments

[Макс]

Спасибо.

1. Да, проблема решилась на 100% после перехода на множественные транзакции.
2. Стоит стандартное, имхо самое оптимальное значение как раз 1, если соблюдать правило больших транзакций.
3. Вот это точно крутая штука, не знал что присуствует, отключу на малопроизводительных HDD!

Answer 2

[German Zvonchuk]

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

Comments

[Анатолий Денисов]

Скорее всего так и есть. wtfowned, а сколько записей в таблицах, при вставке в которые возникает проблема? И таблицы innodb или myisam?

[Макс]

Анатолий Денисов, в данном случае таблица была новая, но выставлял разом 30к записей.

[German Zvonchuk]

wtfowned что значит разом? при batch insert таких проблем не должно.

Запустите анализатор, он подскажет вам что делать надо.
https://github.com/major/MySQLTuner-perl

Выложите сюда результат, проще будет сориентироваться где у вас проблема.

Answer 3

[klepiku]

кэширование на ssd отключено ?

посмотрите мануалы на предмет кэш и рам
https://dev.mysql.com/doc/

Comments

[Макс]

Не понятно честно говоря куда именно смотреть, можно поточнее наводку?
Ничего в стандартной системе не менял.

Файловая система выглядит вот так

root@159:~# df -h
Filesystem      Size  Used Avail Use% Mounted on
udev             32G     0   32G   0% /dev
tmpfs           6.3G  2.1M  6.3G   1% /run
/dev/sda3       437G  346G   69G  84% /
tmpfs            32G   30M   32G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs            32G     0   32G   0% /sys/fs/cgroup
/dev/sda2       488M   80M  384M  18% /boot
tmpfs           6.3G   20K  6.3G   1% /run/user/0
/dev/sdb        469G  295G  151G  67% /mnt/ssd
tmpfs           6.3G   16K  6.3G   1% /run/user/125
/dev/sdc        3.6T  3.3T  186G  95% /mnt/hdd
tmpfs           6.3G     0  6.3G   0% /run/user/1000