Last night we pushed a software update which put a large strain on our database and we had moderate load on our server beforehand (which is configured well for innodb only setup). We ended up having an issue with deadlocks and timeouts for queries in a new bug tracking system we attempted to implement where the inserts were frequent but moderate considering the hardware and resources available for use. I’ve dealt with high traffic issues before, but this is the first time I’ve had the displeasure of watching an innodb based back end freak.
mysql> show innodb status;±---------------------------------+| Status |±---------------------------------+| =====================================080221 12:13:57 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 37 seconds----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 1040196, signal count 916812Mutex spin waits 768277988, rounds 747137145, OS waits 510422RW-shared spins 282239, OS waits 138614; RW-excl spins 17408, OS waits 6992------------------------LATEST DETECTED DEADLOCK------------------------*** WE ROLL BACK TRANSACTION (2)*** WE ROLL BACK TRANSACTION (2)*** WE ROLL BACK TRANSACTION (2)*** WE ROLL BACK TRANSACTION (2)*** WE ROLL BACK TRANSACTION (2)… lots more of these …*** WE ROLL BACK TRANSACTION (2)*** WE ROLL BACK TRANSA | ±---------------------------------+1 row in set, 1 warning (0.07 sec)
Should I disable transactions? We have master / slave replication set up and I don’t want to mess up the binlogging if possible. The errors encountered were:
Lock wait timeout exceeded; try restarting transaction
The hardware involved is an 8 core machine with 3 15k drives and 16gb of ram
The settings involved are:
tmp_table_size = 256Mmax_heap_table_size = 256Msort_buffer_size = 256Mkey_buffer_size = 2Gmax_connections = 1000wait_timeout=3600interactive_timeout=120# Cache Settings# – table cache is not used for innodb tablestable_cache = 20000 # default is 64, max is subject to OS. 1024 is recommended min. max open files limit is found by “cat /proc/sys/fs/file-max” which outputs 412870 thread_cache_size = 256 # I’m going to set this = to the number of tables in the table cache but I don’t know what it should bethread_cache = 256 # 32-64 is recommended# Binlog Settingslog-bin = mysql-binmax_binlog_size = 100M sync_binlog=1skip-external-locking # same as belowskip-locking # only useful if you have things accessing table files outside of mysql (never) skip-bdb# InnoDB Settings#innodb_data_home_dir = /var/lib/mysql/ibdata/innodb_data_file_path = ibdata1:1000M:autoextendinnodb_buffer_pool_size = 8G # this can / should be 70% of the available ram for innodb only systems (2G totals for 32bit chips) so 1-2G would be recommended. This can be tuned.innodb_additional_mem_pool_size = 20M innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 2M # do not set over 2-8M, is flushed once a second anywayinnodb_lock_wait_timeout = 50innodb_log_file_size = 256M # if you change this size, you must stop mysql, delete the log files for innodb, then start it to see a difference#innodb_support_xa = OFF # when off, reduces overhead. may cause out of sync binlogs innodb_thread_concurrency = 22 # (8 processors + 3 disks) * 2 = 22 concurrent threads. lower is generally better. default is infinite and may result in “thrashing” and "bumping"innodb_flush_method = O_DIRECTinnodb_open_files = 20000 innodb_file_per_table
An example query which was logged as failed is:
INSERT INTO wib_logging.phpErrors set personID = ‘’, occured = NOW(), num = ‘8’, type = ‘Notice’, message = ‘Use of undefined constant DEV_NAME - assumed 'DEV_NAME'’, varTrace = ‘a:0:{}’, server = ‘’, ssl = 0, script = ‘/updateProfileQueue.php’, network = ‘wib’, file = ‘/var/www/whereivebeen/macdaddy/includes/functions.php’, line = ‘36’, host = ‘’ ;
The table schema for it is:
CREATE TABLE IF NOT EXISTS phpErrors ( phpErrorsID int(10) unsigned NOT NULL auto_increment, personID int(10) unsigned NOT NULL, occured datetime NOT NULL, num int(10) unsigned NOT NULL, type varchar(50) collate utf8_unicode_ci NOT NULL, message varchar(500) collate utf8_unicode_ci NOT NULL, varTrace text collate utf8_unicode_ci NOT NULL, server char(10) collate utf8_unicode_ci NOT NULL, ssl tinyint(1) NOT NULL, script varchar(255) collate utf8_unicode_ci NOT NULL, network char(15) collate utf8_unicode_ci NOT NULL, file varchar(100) collate utf8_unicode_ci NOT NULL, line int(10) unsigned NOT NULL, host char(15) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (phpErrorsID), KEY personID (personID)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Thanks in advance for any reply and or input )