Alta E / S por causa do MariaDB

Question

Estou usando nginx com php-fpm e mariadb. Às vezes minhas páginas estão carregando devagar. Ao verificar o munin, vi que o tempo médio de espera de E / S é muito alto, até 8 segundos. No php-slowlog estão as funções mysql listadas como razão, por exemplo, mysql_query (). Eu tento ajustar o servidor mariadb (por exemplo, aumentar cache, mover tmp dir do disco rígido para um disco RAM). Mas meu I / O fica aleatoriamente alto.

Porexemplo,nocomeçodográfico.Penseiterresolvidooproblema,masotempodeesperaaumentasemqualquerrazãovisível.Quandoeureinicioomariadbeledescerapidamente(vocêpodevercomoeledescenofinal,eureinicieiomariadbaqui).

NãoestouusandomuitoE/S,opicoédeaproximadamente8MB/s:

É perceptível que a proporção de Table_locks_immediate para Table_locks_waited está aumentando. Ao reiniciar o mariadb, foi de 2,5%. O servidor está ativo há cerca de 30 minutos e aumentou para 3,5%. Eu também notei que quando o servidor está pendurado, o número de threads em execução (Threads_running) é alto (até 30). Quando esse número cai, o servidor pára de ser interrompido.

Meu 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/

    

Answer 1

Se a maioria de suas tabelas forem InnoDB , aumente o innodb_buffer_pool_size para pelo menos a soma dos seus bancos de dados ou coloque 2GB no lugar. Se este servidor for apenas um serviço de banco de dados, coloque esses valores em ~ 60% ou 70% da memória total, seu banco de dados caberá inteiramente na memória.

Se a maioria de suas tabelas forem MyISAM , aumente o key_buffer_size da mesma maneira.

Para o MyISAM, defina concurrent_insert para 2 .

query_cache_size acima de 64MB é perda de memória.

Execute mysqlcheck -o -A periodicamente para melhorar as estatísticas de índices para o planejador interno de consultas do mysql.

Evite consultas que fazem full_scan .

Menor innodb_io_capacity a 100 ou 120 se você tiver um único armazenamento em disco ou RAID simples.

Use XFS para dar a vantagem de escrever simultaneamente no mesmo arquivo .

Monte seu fs com a opção noatime .

Evite trocar.

Desativar registros desnecessários.