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.

enter image description here

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:

enter image description here

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:

port        = 3306
socket      = /var/run/mysqld/mysqld.sock

socket      = /var/run/mysqld/mysqld.sock
nice        = 0

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

bind-address        =

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

max_allowed_packet  = 16M

#key_buffer     = 16M
key_buffer             = 32M

!includedir /etc/mysql/conf.d/
  • 1
    How much RAM do you have and how big is the database? – Matthew Ife Feb 12 '14 at 0:48
  • I've 16GB RAM. The main database is about 570MB big and the second one 300MB. The main database is from a CMS hosted on the server. The second database is a non public testing area so only two people can access it. – Lion Feb 12 '14 at 2:19

If most of your tables are InnoDB, increase the innodb_buffer_pool_size to at least the sum of your databases or put 2GB 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 to 2.

query_cache_size above 64MB 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 to 100 or 120 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.

  • +1. Smells right. Databases ARE IO heavy, and caching as much as possible is the only way. Given this size and server all should fill into ram. – TomTom Feb 25 '14 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.