MySQL performance problems

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_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
#Not windows
#innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode= 2
#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