Basic Details
The myisam_sort_buffer_size variable sets the amount of memory for a buffer that is allocated when indexes are created using CREATE INDEX or ALTER TABLE or during a REPAIR TABLE operation on a MyISAM table.
Releem automatically tunes myisam_sort_buffer_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
Releem automatically tunes myisam_sort_buffer_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
myisam_sort_buffer_size Usage
MyISAM was the original storage engine for MySQL servers. It was replaced in MySQL version 5.5 in December 2009. MyISAM has been replaced by InnoDB. MyISAM was originally vieweed as superior to InnoDB but InnoDB quickly caught up.
InnoDB is ACID compliant by default and highly resistant to table corruption, MyISAM can't say the same. ACID compliant means if a transaction is interrupted, during a server crash or other interruption, the information is safe. There are numerous reasons that InnoDB is preferred over MyISAM.
The myisam_sort_buffer_size variable is the kind of the legacy version of sort_buffer_size. It is used in the same way, to adjust the default buffer size if a server's workload has a higher than normal number of sort queries. It is also defined on a per-session level.
InnoDB is ACID compliant by default and highly resistant to table corruption, MyISAM can't say the same. ACID compliant means if a transaction is interrupted, during a server crash or other interruption, the information is safe. There are numerous reasons that InnoDB is preferred over MyISAM.
The myisam_sort_buffer_size variable is the kind of the legacy version of sort_buffer_size. It is used in the same way, to adjust the default buffer size if a server's workload has a higher than normal number of sort queries. It is also defined on a per-session level.
myisam_sort_buffer_size Configuration
The myisam_sort_buffer_size system variable can be configured using the command line or configuration file:
Command Line:
mysqld> set global myisam_sort_buffer_size = XX
Replace XX with a value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server, and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
myisam_sort_buffer_size = XX
Replace XX with value to suit your database needs. Must restart the server to see the changes applied.
Command Line:
mysqld> set global myisam_sort_buffer_size = XX
Replace XX with a value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server, and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
myisam_sort_buffer_size = XX
Replace XX with value to suit your database needs. Must restart the server to see the changes applied.
myisam_sort_buffer_size
Considerations
Considerations
The myisam_sort_buffer_size is only relevant to servers using the MyISAM storage engine thare are creating or rebuilding indexes using the ALTER TABLE, REPAIR TABLE, or CREATE TABLE operations. This variable can be set for a session, without affecting other users.
Many DBAs have a limited understanding of the MyISAM variables, especially if they've only been in the field for a few years. And while this variable is less common today, some servers still myse MyISAM or a combination of MyISAM and InnoDB. With Releem you don't have to worry about any of this because Releem handles the configuration of myisam_sort_buffer_size and other variables. Releem Agent collects metrics and other system information while Releem Cloud Platform uses the information collected by Releem Agent to calculate MySQL Performance Score and then recommends a new and improved server configuration. Releem can automatically apply these settings, including any recommendations for myisam_sort_buffer_size.