i'm using nginx with php-fpm and mariadb. Sometimes my pages are loading slow. By checking munin I saw that my average I/O waiting time is very high, up to 8 seconds. In the php-slowlog are mysql-functions listed as reason, for example mysql_query(). I try to tweak the mariadb server (e.g. increase cache, move tmp dir from hdd to a ramdisk). But my I/O gets randomly high.
For example at the beginning of the graph. I thought I fixed the problem, but then the waiting time increases without any visible reason. When I restart mariadb it goes rapidly down (you can see how it goes down at the end, I restarted mariadb here).
I'm not really using much I/O, the peak is about 8MB/s:
Noticeable is that the ratio of Table_locks_immediate to Table_locks_waited is increasing. As i restart mariadb, it was 2.5%. The server is up for ~ 30 minutes now and it had increased to 3.5%. I also noticed that when the server is hanging the number of running threads (Threads_running) is high (up to 30). When this number goes down, the server stops hanging.
My 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/
If most of your tables are InnoDB, increase the
innodb_buffer_pool_size
to at least the sum of your databases or put2GB
instead. If this server is only run database service, put these values to ~60% or 70% of total memory, your database will fit entirely in memory.If most of your tables are MyISAM, increase the
key_buffer_size
in the same way.For MyISAM, set
concurrent_insert
to2
.query_cache_size
above64MB
is waste of memory.Run
mysqlcheck -o -A
periodically to improve indexes statistics for internal mysql query planner.Avoid queries who do
full_scan
.Lower
innodb_io_capacity
to100
or120
if you have a single disk storage or simple RAID.Use
XFS
to give the advantage of concurrent write in same file.Mount your fs with
noatime
option.Avoid swapping.
Disable unnecessary logs.