Description:
REPAIR TABLE QUICK seems to fail to use values fo myisam_max_sort_file_size if they have been set online using SET command:
| myisam_max_extra_sort_file_size | 100000000000
| myisam_max_sort_file_size | 100000000000
are shown for session as well as global values, set by:
set global myisam_max_extra_sort_file_size=100000000000;
set global myisam_max_sort_file_size=100000000000;
set session myisam_max_extra_sort_file_size=100000000000;
set session myisam_max_sort_file_size=100000000000;
Repairing the table still uses keycache:
pz@abyss:~> mysqladmin processlist;
+----+------+-----------+------+---------+------+----------------------+----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------------------+----------------------------+
| 1 | pz | localhost | test | Query | 6 | Repair with keycache | repair table reptest quick |
| 2 | pz | localhost | | Query | 0 | | show processlist |
+----+------+-----------+------+---------+------+----------------------+----------------------------+
Setting values as startup options make it work:
...
--myisam_max_sort_file_size=100G \
--myisam_max_extra_sort_file_size=100G \
pz@abyss:~> mysqladmin processlist;
+----+------+-----------+------+---------+------+-------------------+----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+----------------------------+
| 1 | pz | localhost | test | Query | 4 | Repair by sorting | repair table reptest quick |
| 2 | pz | localhost | | Query | 0 | | show processlist |
+----+------+-----------+------+---------+------+-------------------+----------------------------+
How to repeat:
Try the above with table which sizes would not allow to use Repair by Sort with default options.
This code can be used for filling the table:
create table reptest (c char(20) not null);
alter table reptest add key(c);
This code generates queries:
#include <stdio.h>
#define MAX_VALUE 10000000
#define VALUES_INSERT 100000
#define VALUES_TO_INSERT (100*1000*1000)
main()
{
int records=0;
int i;
int value;
while (records<VALUES_TO_INSERT)
{
/* Prepare single insert */
printf("INSERT INTO reptest VALUES ");
for(i=0;i<VALUES_INSERT;i++)
{
value=random()%MAX_VALUE;
if (i==VALUES_INSERT-1)
printf("(%d)",value);
else
printf("(%d),",value);
}
printf(";\n");
records+=VALUES_INSERT;
}
}
Description: REPAIR TABLE QUICK seems to fail to use values fo myisam_max_sort_file_size if they have been set online using SET command: | myisam_max_extra_sort_file_size | 100000000000 | myisam_max_sort_file_size | 100000000000 are shown for session as well as global values, set by: set global myisam_max_extra_sort_file_size=100000000000; set global myisam_max_sort_file_size=100000000000; set session myisam_max_extra_sort_file_size=100000000000; set session myisam_max_sort_file_size=100000000000; Repairing the table still uses keycache: pz@abyss:~> mysqladmin processlist; +----+------+-----------+------+---------+------+----------------------+----------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------------------+----------------------------+ | 1 | pz | localhost | test | Query | 6 | Repair with keycache | repair table reptest quick | | 2 | pz | localhost | | Query | 0 | | show processlist | +----+------+-----------+------+---------+------+----------------------+----------------------------+ Setting values as startup options make it work: ... --myisam_max_sort_file_size=100G \ --myisam_max_extra_sort_file_size=100G \ pz@abyss:~> mysqladmin processlist; +----+------+-----------+------+---------+------+-------------------+----------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------------------+----------------------------+ | 1 | pz | localhost | test | Query | 4 | Repair by sorting | repair table reptest quick | | 2 | pz | localhost | | Query | 0 | | show processlist | +----+------+-----------+------+---------+------+-------------------+----------------------------+ How to repeat: Try the above with table which sizes would not allow to use Repair by Sort with default options. This code can be used for filling the table: create table reptest (c char(20) not null); alter table reptest add key(c); This code generates queries: #include <stdio.h> #define MAX_VALUE 10000000 #define VALUES_INSERT 100000 #define VALUES_TO_INSERT (100*1000*1000) main() { int records=0; int i; int value; while (records<VALUES_TO_INSERT) { /* Prepare single insert */ printf("INSERT INTO reptest VALUES "); for(i=0;i<VALUES_INSERT;i++) { value=random()%MAX_VALUE; if (i==VALUES_INSERT-1) printf("(%d)",value); else printf("(%d),",value); } printf(";\n"); records+=VALUES_INSERT; } }