We have a configuration 1 master replicating to 3 slaves, the master has 2 large databases, the first is predominantly InnoDB at around 124GB and the other is MyISAM at around 230GB.
This issue only seems to have appeared since converting the first database to InnoDB from MyISAM - later some tables, specifically logging ones with large text/blob fields have been converted back to MyISAM after a similar performance drop off and evidence continuously being reported in the error log of a possible cause:
100208 18:38:01 InnoDB: ERROR: the age of the last checkpoint is 490028491,
InnoDB: which exceeds the log group capacity 483180135.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
Since this last instance of this issue, 10th Feb, all has run smoothly until yesterday when for a period of just over an hour the performance of the master dropped from an average 2.5 - 3K questions per second to just around .5K questions per second. After which it initially jumped to 4.5K questions per second as everything caught up before re-stabilising back to normal service.
In all cases we have found disk utilisation > 99% on the data disk, the resulting effect is a network wide slow down of all services/events connecting to this database.
At around the same time the issue resolved itself the buffer pool flushed just over 1.5K pages.... possible connection?
Otherwise there is no output in the error logs, no long running selects or in fact any real amount of queries running at all.
[configuration (in brief)]
max_connections = 350
query_cache_size = 16K # thinking I can lower this but don't suspect it to be cause
thread_cache_size = 16
table_cache = 1024
sort_buffer_size = 512M
myisam_sort_buffer_size = 256M
key_buffer_size = 4G
delay_key_write = OFF
join_buffer_size = 128M
net_buffer_length = 64K
innodb_file_per_table
innodb_buffer_pool_size = 40G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend #actual size = 2.3G
innodb_log_group_home_dir = /var/lib/mysql
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1
[System]
Memory : 72GB
CPU : 16 x 2.53GHz
N.B. we have found increasing the innodb_buffer_pool_size > 40G to be unstable on the slaves and results in MySQL server crash.
Cheers in advance - any advice/suggestions welcome as I am scratching my head a bit over something which is possibly to do with a setting which can be tweaked.
And yes separating the DB's onto separate machines would be grand, unfortunately I am still awaiting the hardware ....
Brooksie
Edited 2 time(s). Last edit at 04/15/2010 09:43AM by Stephen Brooks.