I am having issues with a particular database on my mysql server.
All the tables are InnoDB.
I have a tool (built in PHP) that does a lot of processing (many selects, inserts, updates)
When the select/insert process first begins, things seem fine, data is entered rather quickly.
However, after X amount of inserts, (the exact same amount each time), MySQL hangs for a very long time.
I have tried "show processlist" and I see the query thread sleeping, doing nothing. Oddly enough the sleep time is always 0.
The database has a lot of preloaded data. If I clear all of the data, I still get hangs, but they happen less often (ie. more inserts take place before mysql hangs).
The database is heavily dependent on foreign keys. I have tried disabling foreign key checks, but that does not help.
Do any of you have any ideas as to what can be causing mysql to hang then resume (repeatedly) during read/write intensive processes? I am using indexes on all of my select queries, and there are no slow queries being logged.
Below is a part of my my.cnf file:
skip-external-locking skip-name-resolve open_files_limit=32768 thread_stack = 256K thread_concurrency=4 thread_cache_size = 8 init_connect="SET collation_connection = utf8_general_ci" init_connect="SET NAMES utf8" default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci log_slow_queries = ON long_query_time = 1 log_queries_not_using_indexes =1 max_user_connections=500 max_connections=500 thread_cache_size=128 max_connect_errors=10000 wait_timeout=200 connect_timeout=30 max_allowed_packet=32M key_buffer=384M join_buffer=2M record_buffer=2M sort_buffer_size=2M read_buffer_size=1M read_rnd_buffer_size=1M tmp_table_size=1512M optimizer_search_depth=4 max_seeks_for_key=100 table_cache=8096 concurrent_insert=2 ft_min_word_len=3 max_heap_table_size=768M innodb_additional_mem_pool_size=256M innodb_buffer_pool_size=3096M innodb_max_purge_lag=1000000 innodb_support_xa=0 innodb_log_buffer_size=768M innodb_thread_sleep_delay=2500 innodb_commit_concurrency=0 innodb_flush_log_at_trx_commit=2 innodb_thread_concurrency=8 innodb_flush_method=O_DIRECT binlog_cache_size=4M innodb_log_file_size=64M
Server version: 5.1.62-0ubuntu0.11.10.1-log