Tuesday, April 7, 2015

What to consider to set innodb_sort_buffer_size variable ?

what to consider when choosing the value to use for the innodb_sort_buffer_size configuration option.

The innodb_sort_buffer_size option specifies how much memory InnoDB is allowed to used for sort buffers during various ALTER TABLE and CREATE TABLE operations.
Before MySQL 5.6.4 the value was hard coded to 1MB which is also the default.
The main considerations to take into account when choosing the best value are:
  • The size of the InnoDB sort buffer applies per thread requiring the buffer. As the size of the sort buffer is global and cannot be changed except through a restart of MySQL, be careful setting this value too large if you have several concurrent schema changes.
  • The sorting algorithm does not scale well with large sort buffers, so with respect to sorting efficiency a relatively small buffer should be chosen.
  • A smaller InnoDB sort buffer causes a a larger amount of disk I/O. So the slower disk I/O and the more concurrent I/O from other connections, the larger InnoDB sort buffer.
  • innodb_sort_buffer_size also specifies how much the size of the online ALTER log increases in each increment.
As can be seen from the above, the optimal size of innodb_sort_buffer_size depends on the system and the workload, so you should test with your hardware and workload which value is the optimal for you. However the following rules of thumb can be applied:
  • Fast I/O (like SSD or 8 SAS disks in RAID 10): innodb_sort_buffer_size should be around the default value of 1MB.
  • Slow I/O (like in a virtual machine or using NFS) with fast CPUs: Increase innodb_sort_buffer_size to 8MB or more.

No comments: