Hi, I'm running quite a large database - mostly inserts (4-5 million rows a day) and an occasional select to generate some report based on the inserted data which works very well. Once a month I need to generate a larger number of reports. The SELECT queries are optimized quite well but some of the reports require to group a large number of records together which could take up to 5 minutes, but that is OK for me. The problem is that when more than a certain number (between 4 and 8 - but I can find any stable pattern) of SELECT queries are working concurrently InnoDB starts to lock the threads that insert data and in a very short time MySQL uses all available connections. I couldn't find any reasons why InnoDB is locking INSERT threads which try to insert in tables different then the ones that the reports are SELECT-ing from.
The InnoDB monitor shows a lot of transactions similar to this ---TRANSACTION 1 4271824648, ACTIVE 51 sec, process no 12904, OS thread id 712286614 setting auto-inc lock mysql tables in use 1, locked 1 LOCK WAIT 1 lock struct(s), heap size 320 MySQL thread id 485399224, query id 3713824274 69.59.185.156 websitepulse9 update INSERT INTO slogs8 VALUES (NULL,24801,'OK','0.00','62.40','62.56','62.82','N',Now(),0.479633*1000) ------- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `websitepulse/slogs8` trx id 1 4271824648 lock mode AUTO-INC waiting Normally there are less than 200 running threads (1-2 active) but to avoid hitting the connections limit due to this locking problem I had to increase the maximum connections number to 600 and respectively had to decrease the size of the per-thread memory buffers which slows down the selects additionaly. I'll probably use a second server to replicate the database and run the reports from there but I wanted to see if somebody might had a different suggestion. Here is the servers info MySQL: 4.0.21-standard Official MySQL RPM OS : Red Hat Enterprise Linux ES 3 Memory : 4 GB DELL RAM Processor : Dual 3.06 GHz Intel Xeon RAID Configuration : RAID 1 146 GB SCSI Here is my /etc/my.cnf file [mysqld] port=3306 skip-name-resolve log-bin=/var/lib/mysql/mysql log-slow-queries=/var/lib/mysql/slow.queries.log socket=/var/lib/mysql/mysql.sock myisam-recover=BACKUP,FORCE set-variable = max_connect_errors=100000 innodb_data_home_dir = innodb_data_file_path =/var/lib/mysql/innodbfile:100M:autoextend innodb_log_group_home_dir = /var/log/innologs innodb_log_arch_dir = /var/log/innologs set-variable = innodb_buffer_pool_size=1700M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable = key_buffer_size=500M set-variable = read_buffer_size=500K set-variable = read_rnd_buffer_size=1200K set-variable = sort_buffer_size=1M set-variable = thread_cache=256 set-variable = thread_concurrency=8 set-variable = thread_stack=126976 set-variable = myisam_sort_buffer_size=64M set-variable = max_connections=600 set-variable = table_cache=10000 set-variable = wait_timeout=2000 Any suggestions are welcome. -- Mark J. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
