I have an issue with our MySQL server which is used by quite some different users. It is a shared hosting server, so I can’t change much about the queries being issued.
The problem is that sometimes there are a lot of UPDATE and INSERT queries queued up, sometimes for 5-10 seconds, sometimes for more than 1 minute. The queries in queue are mostly random, I don’t see a pattern in it. When one query is blocking everything, the others don’t get processed either.
In attachment a screenshot that illustrates the problem.
The tables are mostly MyISAM, although there are some users using InnoDB.
Does anyone have a pointer as to what variables I could tweak?
The server is running 5.0.87-d10-ourdelta65 and has 8 cores at 2.93Ghz and 32GB RAM. The config is below.
ft_min_word_len=3innodb_file_per_table = 1innodb_thread_concurrency = 16innodb_buffer_pool_size = 2048Minnodb_additional_mem_pool_size = 12Minnodb_flush_log_at_trx_commit = 2myisam_sort_buffer_size = 256Mkey_buffer = 1Gsort_buffer_size = 10Mmax_connections=1000max_user_connections=40max_allowed_packet=16Mquery_cache_size=8192Mquery_cache_limit=2Mtable_cache=4096bind-address = 0.0.0.0wait_timeout=45connect_timeout=10interactive_timeout=45max_heap_table_size=384Mtmp_table_size=256Mread_buffer_size=512Kjoin_buffer_size=8Mthread_cache_size = 20log_slow_queries = /var/log/mysql/mysql-slow.loglog-error = /var/log/mysql/err.loglog_warningsskip-name-resolve