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.
[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/