2

When executing a SELECT query that uses filesort on a huge table (2 billion rows) the execution stops after ~30mins with the following error message:

ERROR 9 (HY000) at line 1: Unexpected EOF found when reading file '/tmp/MYIcoG9f'
(Errcode: 175 - File to short; Expected more data in file)

I use "5.6.21 MySQL Community Server (GPL)" on Debian 7.0 on a 64-Bit CPU. The system has 64 GB of physical RAM and about 340 GB of free disk space. The table is stored as MyISAM table.

The query I run looks like:

SELECT id, UNIX_TIMESTAMP(time) AS unixtime, somecolumn, othercolumn FROM
       bigtable WHERE somecolumn=b'0' ORDER BY time ASC;

First I assumed that the drive on which /tmp resides ran out of free disk space. I ran df every second in parallel to executing the query. In the df output I can see that MySQL's temporary file consumed only about 70 GB; the disk had still 270 GB free space. Also, I ran the same query successfully on the same machine on three other tables with the same structure, but less rows (1 billion rows and below).

Maybe I need to adjust a server variable? Below you can find some vars which may be relevant. Please let me know in the comments if other variables are relevant as well and I'll add them here.

mysql> SHOW VARIABLES WHERE `Variable_name` LIKE '%buf%' OR `Variable_name` LIKE '%tmp%';
+-------------------------------------+-------------------+
| Variable_name                       | Value             |
+-------------------------------------+-------------------+
| bulk_insert_buffer_size             | 8388608           |
| default_tmp_storage_engine          | InnoDB            |
| innodb_buffer_pool_dump_at_shutdown | OFF               |
| innodb_buffer_pool_dump_now         | OFF               |
| innodb_buffer_pool_filename         | ib_buffer_pool    |
| innodb_buffer_pool_instances        | 8                 |
| innodb_buffer_pool_load_abort       | OFF               |
| innodb_buffer_pool_load_at_startup  | OFF               |
| innodb_buffer_pool_load_now         | OFF               |
| innodb_buffer_pool_size             | 134217728         |
| innodb_change_buffer_max_size       | 25                |
| innodb_change_buffering             | all               |
| innodb_log_buffer_size              | 8388608           |
| innodb_sort_buffer_size             | 1048576           |
| join_buffer_size                    | 262144            |
| key_buffer_size                     | 34359738368       |
| max_tmp_tables                      | 32                |
| myisam_sort_buffer_size             | 8388608           |
| net_buffer_length                   | 16384             |
| preload_buffer_size                 | 32768             |
| read_buffer_size                    | 131072            |
| read_rnd_buffer_size                | 262144            |
| slave_load_tmpdir                   | /tmp              |
| sort_buffer_size                    | 34359738368       |
| sql_buffer_result                   | OFF               |
| tmp_table_size                      | 16777216          |
| tmpdir                              | /tmp              |
+-------------------------------------+-------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.