Re: "Incorrect key file for table XXXX"
Hi,
I am seeing this problem for medium to large queries. If I simplify the queries by reducing the number of record IDs in the IN() qualifiers, I can get it to work without error.
The system is a 4 proc, 8 core AMD64 with 8 GB memory, Redhat enterprise latest, Mysql 5.0.41 community Redhat RPM AMD64 binary (from 6/19/07), dual RAID 1 drives with 2, ultra2 SCSI 300 GB disks on each. Fresh install, latest everything.
Top shows mysql using 2464M virt, 199M res, 4108 shr, 8GB total, 6GB free so there is no memory problem.
The entire database is 82 MB data and 82 MB index. The data drives are 1% used with hundreds of GB of free space.
The exact error message is:
DBD::mysql::st execute failed: Incorrect key file for table '/local2/mysql-tmp/#sql_3042_0.MYI'; try to repair it at /home/brianp/bin/sql.pl line 1036.
Here's the SQL which just barely breaks it:
$sql = "select distinct a.sweepListId, a.subProjectId, dimmConfig, memLoad,
cpuNode0_Fid, cpuNum, marginType, nominal, lastPassing
from sweepList a, sweepDimm b, dimm c
where a.subProjectId in(11828,11830,11837,11838,11854,11855,11856,11859,
11861,11862,11863,11864,11874,11875,11876)
and c.dimmId in (1056,644,965,282,1177,1006,83,85,598,485,1080,351,520,
1128,815,265,403,406,845,493,930,71,1014,670,779,966,972,946,185,369,
70,326,163,1205,477,478,402,230,1149,678,1103,1104,440,47,766,149,547,
548,680,681,582,1210,118,121,123,124,17,1048,475,476,817,1195,626,139,
1013,63,566,610)
and a.sweepListId = b.sweepListId
and b.dimmId = c.dimmId
order by dimmConfig, memLoad, cpuNode0_Fid, cpuNum, marginType";
Here's the slightly simplified version which works:
$sql = "select distinct a.sweepListId, a.subProjectId, dimmConfig, memLoad,
cpuNode0_Fid, cpuNum, marginType, nominal, lastPassing
from sweepList a, sweepDimm b, dimm c
where a.subProjectId in(11828,11830,11837,11838,11854,11855,11856,11859,
11861,11862,11863,11864,11874,11875,11876)
and c.dimmId in (1056,644,965,282,1177,1006,83,85,598,485,1080,351,520,
670,779,966,972,946,185,369,
70,326,163,1205,477,478,402,230,1149,678,1103,1104,440,47,766,149,547,
548,680,681,582,1210,118,121,123,124,17,1048,475,476,817,1195,626,139,
1013,63,566,610)
and a.sweepListId = b.sweepListId
and b.dimmId = c.dimmId
order by dimmConfig, memLoad, cpuNode0_Fid, cpuNum, marginType";
168 rows selected.
I just removed a few of the dimmIds.
Here's an example record (with margin data scrubbed)
19703, 11876, 11876-800_QIM_DRX8, Heavy, -1, A116158, FREQ_High, 00.00, 00.00
The sweepList table has 19,495 records, 5.7 MB data, 1 MB index.
The sweepDimm table has 56,853 records, 1.5 MB data, 2.7 MB index.
The dimm table has 1126 records, 231 kB data and 87 kB index.
I have the max_heap_table_size set to 2560M. The total delivered data size when put in an output file is 15 kB and 168 records.
I tried setting the max heap size to something ridiculous, 2.56 GB to try to keep it off the disk. The corrupt files are always temp files like #sql_3042_0.MYI.
I have another server with Mysql 5 (over a year old), half as much memory (4 GB) and only 2 cores but otherwise similar and it runs much, much larger queries fine.
Here is the /etc/my.cnf file which I verified it is using by altering the max_heap_table_size, restarting the server and then checking up on it with
mysqld --verbose --help | grep max_h
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
[mysqld]
#Max number of bytes in sorted records.
max_length_for_sort_data=1M
#The size of the buffer that is used for full joins.
join_buffer_size=128M
#The default size of key cache blocks
key_cache_block_size=2M
#Don't allow creation of heap tables bigger than this.
max_heap_table_size=2560M
#Allow big result sets by saving all temporary sets on file (Solves most 'table full' errors).
big-tables
preload_buffer_size=1M
range_alloc_block_size=2M
interactive_timeout = 604800
port = 3306
join_buffer_size = 256M
socket = /var/lib/mysql/mysql.sock
#(Unknown option:)skip-locking
key_buffer_size=1600M
max_allowed_packet = 16M
table_cache = 160
sort_buffer_size = 320M
net_buffer_length = 16M
read_buffer_size = 44M
read_rnd_buffer_size = 160M
myisam_sort_buffer_size = 320M
thread_cache_size = 12
query_cache_size = 1000M
query_cache_limit = 24M
query_prealloc_size = 4M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
#(Unknown option:)slave_compressed_protocol = ON
tmpdir = /local2/mysql-tmp
server-id = 1
skip-innodb
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M
[mysqlhotcopy]
interactive-timeout
I can't give access to the actual tables because they contain company margin data.
Is there anything else I can tell you?
Thank you,
BrianP