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.
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.