What are steps to reproduce this, and where?

To reproduce:
CREATE TABLE enwiki_neu ( name_id bigint( 20 ) NOT NULL , name varchar( 180 ) NOT NULL , tp_nr bigint( 20 ) NOT NULL , tp_name varchar( 100 ) NOT NULL , entry_nr bigint( 20 ) NOT NULL , entry_name varchar( 200 ) NOT NULL , Value varchar( 900 ) NOT NULL ,
KEY tp_name (tp_name(30)), KEY name ( name (15)) , KEY name_id ( name_id) , KEY entry_name ( entry_name(15) ) , KEY Value ( Value (15) ) ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

set autocommit = 0;LOAD /* SLOW_OK */ DATA LOCAL INFILE '/data/project/templatetiger/public_html/dumps/sort/enwiki-2014-04-02-lPqO.txt' IGNORE INTO TABLE enwiki_neu FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\n' ;
commit; set autocommit = 1;

I believe there is a nonlinear performance drop, if the index-size becomes larger than the RAM.

To stop import I use "kill query 12345678;" and jstop.

Where?

Is this about some Wikimedia Labs instance? Some tool on Tool Labs?

Toollabs: http://tools.wmflabs.org/templatetiger/

Thats why I put this report in "Product:Tools".

https://bugzilla.wikimedia.org/describecomponents.cgi :)

Process of enwiki is still there:
MariaDB [(none)]> show processlist;
+----------+--------+-------------------+-------------------------+---------+---------+-----------+------------------------------------------------------------------------------------------------------+----------+

+----------+--------+-------------------+-------------------------+---------+---------+-----------+------------------------------------------------------------------------------------------------------+----------+

After 11 days still running:
tools.templatetiger@tools-login:
MariaDB [(none)]> show processlist;
+----------+--------+-------------------+-------------------------+---------+---------+-----------+------------------------------------------------------------------------------------------------------+----------+

+----------+--------+-------------------+-------------------------+---------+---------+-----------+------------------------------------------------------------------------------------------------------+----------+

+----------+--------+-------------------+-------------------------+---------+---------+-----------+------------------------------------------------------------------------------------------------------+----------+
2 rows in set (0.00 sec)

Tim, just to be clear: On what DB host is this query running/hanging? Looking at /data/project/templatetiger/public_html/einspielen.sql, this seems to be tools-db?

Replication for enwiki stopped on 2014-04-18 (twelve days ago) and was restarted on 2014-04-21 (nine days ago; cf. bug #64154), so this query in limbo might be related to that, thus assigning to Sean (even though I assume tools-db resides on a different DB server).

It's running on tools-db. I started it with jsub.
I never restarted the query and it has no contact to the official enwiki-db because I import an extract of templates as a text-file into my project-db. Template data are not included the official enwiki-db. I got the data from checkwiki-project.

tools-db is labsdb1005. It isn't a production replicant, so this is unrelated to the replication outage.

LOAD DATA INFILE importing a lot of data to a transactional storage engine like InnoDB is asking for trouble because the statement is a also a transaction -- an 18GB transaction :-)

That means a whole lot of undo log entries are generated in case of rollback, much purge thread lag occurs, other traffic is slowed, and if the undo log exceeds buffer pool capacity it will start thrashing the tablespace on disk.

If the transaction gets killed and has to rollback the whole process will be even slower than the original load.

All that can be done now is:

a) Wait it out.

b) Restart mysqld and hope crash recovery is faster (may not be).

c) Not nice stuff that will need downtime for everyone.

In the future, better to do bulk data loads into ARIA tables then ALTER them to be InnoDB if you need it specifically. Your plan to batch the inserts in 2GB chunks is also good.

I assume then that this (killed queries taking a long time to wind down) is not really a bug, but ... the way it is :-).