I wrote a stored procedure on MySQL that is using a cursor to update a table of about 45,000 rows.
The details what it is doing is are unimportant but the logic is very simple.
The procedure was running extremely slowly (of the order of 1/10 th second per operation) – the steps below describe what I did to make sure that the procedure ran reasonably quickly.
Taking a look in mysql.log I saw the message:
120329 10:34:48 InnoDB: ERROR: the age of the last checkpoint is 9433926,
InnoDB: which exceeds the log group capacity 9433498.
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.
This leads me to changing my.ini to add (or change) the value of innodb_log_file_size
(Should really read the manual….)
innodb_log_file_size = 64M
You then need to shutdown the server and (re)move the existing log files(ib_logfile0 and ib_logfile1) before starting again with the new values
Official docs
Another value that looks to be recommended to change is innodb_buffer_pool_size and while I was there I changed some other values:
innodb_open_files = 512
#innodb_data_file_path=ibdata4:2G:autoextend:max:4G
innodb_buffer_pool_size = 512M
#innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_concurrency_tickets = 500
innodb_lock_wait_timeout=200
#Not windows
#innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode= 2
innodb_commit_concurrency=4
innodb_flush_log_at_trx_commit=2
innodb_support_xa=false
innodb_checksums=0
innodb_doublewrite=0
innodb_max_dirty_pages_pct=15
#innodb_io_capacity = 10000
#innodb_adaptive_checkpoint = 1
#innodb_write_io_threads = 8
#innodb_read_io_threads = 8
Other tips
innotop is useful to see what is happening
If there are (slow) temporary tables being created (EXPLAIN Using where; Using temporary; Using filesort ) then try:
mount a tmpfs system on an empty directory (you should also add this to fstab):
mount tmpfs /tmpfs -t tmpfs
and edit my.cnf to make MySQL use that directory as a temporary directory:
tmpdir = /tmpfs