Высокая нагрузка на I/O из-за MariaDB.

Я использую nginx с php-fpm и mariadb. Иногда страницы загружаются медленно. Проверив munin, я заметил, что среднее время ожидания I/O очень велико, до 8 секунд. В php-slowlog указаны функции mysql как причина, например, mysql_query(). Я пытаюсь настроить сервер mariadb (например, увеличиваю кэш, перемещаю временный каталог с hdd на ramdisk). Но мое I/O случайным образом увеличивается.

Например, в начале графика. Я думал, что решил проблему, но затем время ожидания снова увеличилось без очевидных причин. Когда я перезапускаю mariadb, оно резко уменьшается (вы можете увидеть, как оно падает в конце, я перезапустил mariadb здесь).

Я не очень много использую I/O, пик составляет около 8 МБ/с:

Замечательно то, что соотношение Table_locks_immediate к Table_locks_waited увеличивается. Когда я перезапускаю mariadb, оно составляло 2.5%. Сервер был активен примерно 30 минут, и оно увеличилось до 3.5%. Я также заметил, что когда сервер “подвисает”, количество работающих потоков (Threads_running) высокое (до 30). Когда это число уменьшается, сервер перестает “подвисать”.

Мой my.cnf:

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

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

[mysqld]
sync_binlog = 3

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
tmpdir      = /var/mysqltmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
log-bin=/var/mysqltmp/mysql-bin.log
#log=/var/mysqltmp/mysql.log
log-error=/var/mysqltmp/mysql-error.log
log-slow-queries=/var/mysqltmp/mysql-slowquery.log

bind-address        = 127.0.0.1

max_tmp_tables      = 100
max_connections     = 150
connect_timeout     = 5
#wait_timeout            = 600
wait_timeout        = 800
max_allowed_packet  = 16M
thread_cache_size       = 128
#sort_buffer_size        = 4M
sort_buffer_size    = 8M
bulk_insert_buffer_size = 8M
max_heap_table_size     = 128M
tmp_table_size      = 128M
#tmp_table_size          = 64M

# * MyISAM
myisam_recover          = BACKUP
key_buffer_size         = 256M
#open-files-limit   = 2000
table_open_cache    = 1000
myisam_sort_buffer_size = 8M

#concurrent_insert  = 2
#read_buffer_size        = 2M
read_buffer_size    = 4M
#read_rnd_buffer_size    = 1M
read_rnd_buffer_size    = 2M

# * Query Cache Configuration
query_cache_limit       = 256M
query_cache_size        = 256M
# for more write intensive setups, set to DEMAND or OFF
query_cache_type        = ON
#table_cache                    = 400
table_open_cache        = 2000
join_buffer_size        = 4M

# * Logging and Replication
log_warnings        = 2
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
#log_slow_rate_limit    = 1000
log_slow_verbosity  = query_plan

log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog        = 1
expire_logs_days    = 10
max_binlog_size         = 128M

# * InnoDB
default_storage_engine  = InnoDB
#innodb_log_file_size   = 50M
#innodb_buffer_pool_size    = 256M
innodb_buffer_pool_size = 256M
innodb_log_buffer_size  = 32M
innodb_file_per_table   = 1
innodb_open_files   = 400
innodb_io_capacity  = 400
#innodb_flush_method    = O_DIRECT
innodb_flush_method     = O_DSYNC

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[isamchk]
#key_buffer     = 16M
key_buffer             = 32M

!includedir /etc/mysql/conf.d/

Если большинство ваших таблиц используют InnoDB, увеличьте innodb_buffer_pool_size как минимум до размера ваших баз данных или установите 2GB. Если сервер используется только для служб базы данных, установите это значение на ~60% или 70% от общего объема памяти, и ваша база данных полностью уместится в памяти.

Если большинство ваших таблиц используют MyISAM, увеличьте key_buffer_size аналогичным образом.

Для MyISAM установите concurrent_insert на 2.

query_cache_size выше 64MB будет пустой тратой памяти.

Регулярно запускайте mysqlcheck -o -A для улучшения статистики индексов для внутреннего планировщика запросов MySQL.

Избегайте запросов, которые выполняют full_scan.

Уменьшите innodb_io_capacity до 100 или 120, если у вас однодисковая система хранения или простая RAID.

Используйте XFS для преимущества одновременной записи в один файл.

Монтируйте файловую систему с опцией noatime.

Избегайте использования подкачки.

Отключите ненужные журналы.

Я столкнулся с этим после недавнего обновления MariaDB на CentOS 7. В то время я не хотел перезагружать сервер, и это не казалось необходимым.

Сначала я думал, что проблема может быть в недостатке места на диске, так как у меня были похожие проблемы после выполнения запланированного резервного копирования. После проверки эта проблема не подтверждалась: было достаточно оперативной памяти и места на диске, но загрузка сервера с использованием top делала сервер неотзывчивым.

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

Я также попытался увеличить размер innodb_buffer_pool_size и innodb_log_file_size, чтобы посмотреть, поможет ли это. Опять же, загрузка процессора на сервере была очень высокой.

Я в итоге перезапустил сервер и любые дополнительные службы, и все вернулось в норму (< 1) с использованием top.