Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
How large is "X" as in " X amount of inserts". Also, is there an index involved on this table? –  uSlackr Apr 24 '12 at 23:05
    
@uSlackr 88 inserts when there is a lot of preloaded data. ~1500 when there is no preloaded data. The largest table has about 900k rows, and does have an index with foreign key restraints –  Kovo Apr 24 '12 at 23:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.