Description:
The myisam_sort_buffer_size displays a 0 when setting the value equal to or greater than 4G in the my.cnf file. It does not show any warnings or errors in mysql or error logs.
With a value of 0 it causes (perhaps forces) sorting by key-cache.
How to repeat:
1. Stop mysql
2. Edit my.cnf file and set myisam_sort_buffer_size to 4294967295
3. Start mysql
4. View variables:
mysql> show variables like 'myisam_sort_buffer_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| myisam_sort_buffer_size | 4294967295 |
+-------------------------+------------+
5. In my example:
I create an index on a 6G myisam table (3.7 MYD & 2.3 MYI).
CREATE INDEX idx_name ON table1 (column5);
6. View processlist:
mysql> show processlist;
+----+------+------------------------+----------+---------+------+-------------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------------------+----------+---------+------+-------------------+---------------------------------------+
| 1 | root | localhost | Intelius | Query | 580 | Repair by sorting | CREATE INDEX idx_name ON table1 (column5) |
| 34 | root | dev | NULL | Query | 0 | NULL | show processlist |
+----+------+------------------------+----------+---------+------+-------------------+---------------------------------------+
7. I then remove the index from table1
8. Start the process over again (Step 1) but with a value of 4294967296 (4G) for myisam_sort_buffer_size
mysql> show variables like 'myisam_sort_buffer_size';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| myisam_sort_buffer_size | 0 |
+-------------------------+-------+
show processlist;
+----+------+------------------------+----------+---------+------+----------------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------------------+----------+---------+------+----------------------+---------------------------------------+
| 1 | root | localhost | Intelius | Query | 184 | Repair with keycache | CREATE INDEX idx_name ON table1 (column5) |
| 3 | root | dev | NULL | Query | 0 | NULL | show processlist |
+----+------+------------------------+----------+---------+------+----------------------+---------------------------------------+
Error log:
071002 10:18:47 InnoDB: Starting shutdown...
071002 10:18:49 InnoDB: Shutdown completed; log sequence number 2 936578897
071002 10:18:49 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
071002 10:18:50 InnoDB: Started; log sequence number 2 936578897
071002 10:18:50 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.1.20-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)
Suggested fix:
I recommend informing the user through the documentation that a 4G myisam_sort_buffer_size sets the value to 0 and forces key-cache instead of sorting.
