Re: Why is my entire database getting locked for a slow select query?

[Shawn Taylor]

Given the size of your server I would increase these values:

innodb_buffer_pool_size = 4G
innodb_log_file_size = 100M
innodb_log_buffer_size = 10m

You might want to also consider using innodb_file_per_table in the future.

Based on these results:

-- 1

'Created_tmp_disk_tables', '38310877'
'Created_tmp_tables', '63352021'

-- 2

'Created_tmp_disk_tables', '38312500'
'Created_tmp_tables', '63354947'

-- 3

'Created_tmp_disk_tables', '38313819'
'Created_tmp_tables', '63357353'

You should ensure your tmp space (/tmp) is on a different mount point than your data directory.

Shawn