| Bug #14709 | LOAD DATA INFILE: Indexes get disabled by LOAD DATA | ||
|---|---|---|---|
| Submitted: | 7 Nov 2005 15:30 | Modified: | 4 Feb 2010 11:56 |
| Reporter: | martin koegler | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.16-BK, 4.1.14 (4.1.15),5.0.16 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[7 Nov 2005 15:30]
martin koegler
[10 Nov 2005 15:40]
Valeriy Kravchuk
Thank you for a problem report. I was able to repeat the "disabling" using much less data (about 30 Mb) on 4.0.16-BK build on x86 (32bit), but then, after loading finished, select ... where xx=222 was performed using index, while select ... zz=222 - not (according to explain). So, I can't say that all indexes are disabled really... I need much smaller and exact test case to be able to verify this. Do you have any ideas on how to reduce it? You my.cnf content may be of some use too.
[11 Nov 2005 8:45]
martin koegler
>I was able to repeat the "disabling" using much less data (about 30 Mb) on >4.0.16-BK build on x86 (32bit), but then, after loading finished, select ... >where xx=222 was performed using index, while select ... zz=222 - not (according > to explain). So, I can't say that all indexes are disabled really... I hope, you mean 4.1.16-BK (on 4.0.X I do similar things and never notice such problems). I tried to narrow it down. Using only one table seems to be enough. The size of the load file must be large enough, so that you can issue the show index command, while the first phase of the LOAD DATA statement is executed. If you are too late, no index gets disables (or only some). My minimal size was 15 MB, but then some times I was too slow. So I use bigger files to have more time. I use before each attempt delete from t1; flush tables; The I run (same setup as last time) $mysqlimport -h 127.0.0.1 -u root test /tmp/mysql/t1 and as fast a I can, I issue show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+ | t1 | 1 | xx | 1 | xx | A | 0 | NULL | NULL | YES | BTREE | disabled | | t1 | 1 | xx | 2 | yy | A | 0 | NULL | NULL | YES | BTREE | disabled | | t1 | 1 | yy | 1 | yy | A | 0 | NULL | NULL | YES | BTREE | disabled | | t1 | 1 | yy | 2 | xx | A | 0 | NULL | NULL | YES | BTREE | disabled | | t1 | 1 | zz | 1 | zz | A | 0 | NULL | NULL | YES | BTREE | disabled | | t1 | 1 | zz | 2 | xx | A | 0 | NULL | NULL | YES | BTREE | disabled | | t1 | 1 | yy_2 | 1 | yy | A | 0 | NULL | NULL | YES | BTREE | disabled | | t1 | 1 | yy_2 | 2 | zz | A | 0 | NULL | NULL | YES | BTREE | disabled | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+ 8 rows in set (0.00 sec) After mysqlimport finishes, all indexes are disabled (if you were fast enough, so that show index returned disabled for each line): mysql> explain select * from t1 where zz=222; explain select * from t1 where yy=222; explain select * from t1 where xx=222; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1048576 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1048576 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1048576 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from t1 where zz=222; explain select * from t1 where yy=222; explain select * from t1 where xx=222; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | t1 | ALL | zz | NULL | NULL | NULL | 1048576 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | t1 | ref | yy,yy_2 | yy | 3 | const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | t1 | ref | xx | xx | 14 | const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) So before the flush tables, no index is used, after that some indexes are considered (even if for zz=222 key is NULL). I fear, that the test case can not made smaller than this, because one LOAD DATA statement must be executed simultanly to a SHOW INDEX command with certain timing requirements. Now, only debugging can show futher details. My my.ini is empty (except changing the datadir, socket and port to be able to run a test instance). I will a show variables in the files tab. Do you need more information?
[11 Nov 2005 8:46]
martin koegler
SHOW VARIABLES
Attachment: variables.txt (text/plain), 14.40 KiB.
[11 Nov 2005 9:58]
Valeriy Kravchuk
Yes, I meant 4.1.16-BK. Thank you for the additional information. Now everything seems clear. Let me try to repeat on a newer build.
[13 Nov 2005 13:54]
Valeriy Kravchuk
Verified just as described in last comment on 4.1.16-BK build (ChangeSet@1.2465.1.1, 2005-11-10 15:12:22+01:00, ...) on Linux (with much smaller table t1 on my old Fedora Core 1 box):
[openxs@Fedora 4.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1 (xx decimal(12,0) default NULL,yy smallint(6) default NULL,zz
-> char(60) default NULL, KEY (xx,yy), KEY (yy,xx), KEY (zz,xx), KEY (yy,zz))
-> ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[openxs@Fedora 4.1]$ bin/mysqlimport -h 127.0.0.1 -u root test /tmp/t1 &
[2] 3090
[openxs@Fedora 4.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.16
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| t1 | 1 | xx | 1 | xx | A | NULL | NULL | NULL | YES | BTREE | disabled |
...
| t1 | 1 | yy_2 | 2 | zz | A | NULL | NULL | NULL | YES | BTREE | disabled |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
8 rows in set (0.00 sec)
mysql> select count(*) from t1; -- this statement will be locked until LOAD completed
test.t1: Records: 163840 Deleted: 0 Skipped: 0 Warnings: 491520
+----------+
| count(*) |
+----------+
| 163840 |
+----------+
1 row in set (4.38 sec)
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| t1 | 1 | xx | 1 | xx | A | NULL | NULL | NULL | YES | BTREE | disabled |
...
| t1 | 1 | yy_2 | 2 | zz | A | NULL | NULL | NULL | YES | BTREE | disabled |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
8 rows in set (0.00 sec)
So, yes, indexes remain disabled after the LOAD finished:
mysql> explain select * from t1 where zz=222; explain select * from t1 where
yy=222; explain select * from t1 where xx=222;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 163840 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.06 sec)
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 163840 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 163840 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)
mysql> explain select * from t1 where zz=222; explain select * from t1 where
yy=222; explain select * from t1 where xx=222;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t1 | ALL | zz | NULL | NULL | NULL | 163840 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | t1 | ref | yy,yy_2 | yy | 3 | const |1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.01 sec)
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | t1 | ref | xx | xx | 14 | const |1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
Now some indexes are used finally.
[27 Nov 2005 13:22]
Valeriy Kravchuk
Shell script to repeat the problematic behaviour
Attachment: 14709.sh (application/octet-stream, text), 1.18 KiB.
[27 Nov 2005 13:23]
Valeriy Kravchuk
The results of the shell script
Attachment: 14709.txt (text/plain), 2.09 KiB.
[27 Nov 2005 13:29]
Valeriy Kravchuk
I had uploaded a shell script and the results it produce to simplify working on this problem. Please note that some changes may be needed to pathnames and number of cycles to create large enough file.
[18 Dec 2005 3:18]
Alexander Pachev
LOAD DATA INFILE uses the bulk insert algorithm which temporarily disables all non-unique keys for the duration of the load. If it did not, the load time would increase. In some cases, though, it may be preferred to the performance decrease that comes from disabling the keys. In the future versions we should add an option to LOAD DATA INFILE to not use the bulk insert.
[18 Dec 2005 17:20]
Martin Kögler
Is INSERT into xxx select * from yyy also temporary disabling the indexes, like LOAD DATA? I see a similar behaviour for tables, where data is added by this mean, so this statement may also need such an option. I think, that such an option an impraticable solution: * If the disable index options is activ, a user with SELECT access to a table can accidentaly disable its indices, if he does not verify, that there is no concurrent bulk insert. * If the disable index option is not active, all bulk inserts will take longer. So users, which need fast bulk inserts, must switch to MySQL 4.0 (A workload, which causes these problems under 4.1, runs on a MySQL 4.0.x Windows without any problems) or maybe 5.0 (not tested, if affected)? As far as I know, no read/write access to a table is possible, while a LOAD DATA is in progress. So would it be a better solution to deny/block all open attempts during the LOAD DATA, so that no wrong information about the indexes can be cached?
[19 Dec 2005 11:16]
martin koegler
MySQL 5.0.16 has the same problem. So bulk inserts only work on 4.0.X without any problems. The following output was created by the script in this bug report and MySQL 5.0.16-max (precompiled by MySQL.com). Beside some changes to the path of the script, I need to sleep some seconds after the start of mysqlimport. Everything es list unchanged. Variable_name Value version 5.0.16-max Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 xx 1 xx A NULL NULL NULL YES BTREE disabled t1 1 xx 2 yy A NULL NULL NULL YES BTREE disabled t1 1 yy 1 yy A NULL NULL NULL YES BTREE disabled t1 1 yy 2 xx A NULL NULL NULL YES BTREE disabled t1 1 zz 1 zz A NULL NULL NULL YES BTREE disabled t1 1 zz 2 xx A NULL NULL NULL YES BTREE disabled t1 1 yy_2 1 yy A NULL NULL NULL YES BTREE disabled t1 1 yy_2 2 zz A NULL NULL NULL YES BTREE disabled test.t1: Records: 4194304 Deleted: 0 Skipped: 0 Warnings: 8388608 count(*) 4194304 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 xx 1 xx A NULL NULL NULL YES BTREE disabled t1 1 xx 2 yy A NULL NULL NULL YES BTREE disabled t1 1 yy 1 yy A NULL NULL NULL YES BTREE disabled t1 1 yy 2 xx A NULL NULL NULL YES BTREE disabled t1 1 zz 1 zz A NULL NULL NULL YES BTREE disabled t1 1 zz 2 xx A NULL NULL NULL YES BTREE disabled t1 1 yy_2 1 yy A NULL NULL NULL YES BTREE disabled t1 1 yy_2 2 zz A NULL NULL NULL YES BTREE disabled Execution plans after loading Execution plans after loading id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4194304 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4194304 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4194304 Using where Execution plans after flushing Execution plans after flushing id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL zz NULL NULL NULL 4194304 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref yy,yy_2 yy 3 const 1 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref xx xx 7 const 1 Using where + echo 'So, indexes are not even considered until fuls tables performed. Is it OK?' So, indexes are not even considered until fuls tables performed. Is it OK?
[21 Dec 2005 7:42]
martin koegler
The problem is, that apart from the TABLE instance used by the LOAD DATA statement, the other statements use a second TABLE instance.
When the indexes are enabled, info(HA_STATUS_CONST); is called, which only correct the index information for one TABLE instance. To invalidate the other copies, they are removed from the table cache.
--- mysql-4.1.15/sql/ha_myisam.cc.orig 2005-12-20 10:19:19.098422576 +0100
+++ mysql-4.1.15/sql/ha_myisam.cc 2005-12-20 10:44:21.375041816 +0100
@@ -918,6 +918,7 @@
}
else if (mode == HA_KEY_SWITCH_NONUNIQ_SAVE)
{
+ TABLE_LIST flush;
THD *thd=current_thd;
MI_CHECK param;
const char *save_proc_info=thd->proc_info;
@@ -939,6 +940,15 @@
}
info(HA_STATUS_CONST);
thd->proc_info=save_proc_info;
+
+ /* flush table to invalidate all copies */
+ bzero (&flush, sizeof(TABLE_LIST));
+ if (table)
+ {
+ flush.real_name = table->real_name;
+ flush.db = table->table_cache_key;
+ close_cached_tables (thd, 0, &flush);
+ }
}
else
{
