mysql

By steve, 26 May, 2011

I was trying to tune an applications performance, but was having issues where it was taking a long time (~5 minutes) to delete 10,000 rows from an innodb table using an indexed field to select the rows to delete. It did not matter whether these rows were deleted in individual queries, or as a single query. I added the following options to /etc/mysql/my.cnf and the query dropped down to 0.3s (a reduction of 1000 times the original time).

innodb_buffer_pool=3G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_log_group_home_dir=/var/lib/mysql2

Tags