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.

How to Relocate the MySQL Binary Logs to New Location


The binary logs are created in the data directory by default in MySQL Server.

To have the binary logs in the data directory is not always the optimal location however. This could be for a range of reasons including:
  • There is not enough space on the drive/partition to manage all the binary logs as well as data.
  • The workload on the server performs better when the logs are on a separate drive to the data.
  • Archiving the data.
  • Administration and management of logs on large setups.
How to Move the Binary Logs
The binary logs are made up of two essential file types:

  1)  The log files - these record all the changes to data on the system
  2)  The index file - this file tracks the active log files on the system

Moving the log files to a new location involves the following steps:

1. Stop the mysqld service so no changes are being recorded.

# service mysqld stop

NOTE: Make sure that the process should be killed.

Eg: # ps -ef | grep -i mysql

2. Copy the log files including the .index file to new location.
# cp hostname-bin.log* /path/to/binlogs
# cp hostname-bin.index /path/to/binlogs

3. Do Changes in the /etc/my.cnf file.

[mysqld]
log-bin=/path/to/newdirecory/server-name-bin
log-bin-index=/path/to/newdirectory/server-name-bin.index

4. The logs will be created in the new directory as specified, but existing logs are still pointing to old path in the .index file. To fix the old logs we will need to edit the .index file and update the paths to new location.

Eg: # cat hostname-bin.index

./hostname-bin.000031
./hostname-bin.000032
./hostname-bin.000033

5. Start the mysql service.

# service mysqld start

6. Show Binary logs

mysql> SHOW BINARY LOGS;
+---------------------+------------+
| Log_name            | File_size  |
+---------------------+------------+
| hostname-bin.000008 |    1252142 |
| hostname-bin.000009 |  481551017 |
| hostname-bin.000010 |        126 |
| hostname-bin.000011 | 1074151674 |
| hostname-bin.000012 |  872672881 |
| hostname-bin.000013 |        126 |
| hostname-bin.000014 |        107 |
+---------------------+------------+

7. Once got confirmed remove the bin log files from data directory.

# cd /data/dir/
# rm hostnamep-bin.*