My advise:
http://forums.mysql.com/read.php?24,563878,563891#msg-563891
Have you made any progress on this?
Let's see how to do it:
I have got file /tmp/alertdata.txt (size 12G) to import:
From terminal#1
./mk-fifo-split /tmp/alertdata.txt --fifo /tmp/my-fifo --lines 1000000
From terminal#2:
I have created a script to import file i.e.
]$ cat import.bash
while [ -e /tmp/my-fifo ]; do
time mysql -uroot -pxxxx -e "load data local infile '/tmp/my-fifo' into table test.alertdata_2;"
sleep 1;
done
now run the above script:
./import.bash
From terminal#3
connect to mysql and observe how quickly it's importing chunks of a large file:
mysql> show processlist;
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
| 4163 | root | localhost | NULL | Query | 6 | NULL | load data local infile '/tmp/my-fifo' into table test.alertdata_2 |
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+------+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+-------+------------------+
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
+------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4165 | root | localhost | NULL | Query | 0 | NULL | load data local infile '/tmp/my-fifo' into table test.alertdata_2 |
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4165 | root | localhost | NULL | Query | 2 | NULL | load data local infile '/tmp/my-fifo' into table test.alertdata_2 |
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4165 | root | localhost | NULL | Query | 3 | NULL | load data local infile '/tmp/my-fifo' into table test.alertdata_2 |
+------+------+-----------+------+---------+------+-------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4165 | root | localhost | NULL | Query | 4 | Reading from net | load data local infile '/tmp/my-fifo' into table test.alertdata_2 |
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4165 | root | localhost | NULL | Query | 5 | Reading from net | load data local infile '/tmp/my-fifo' into table test.alertdata_2 |
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4165 | root | localhost | NULL | Query | 6 | Reading from net | load data local infile '/tmp/my-fifo' into table test.alertdata_2 |
+------+------+-----------+------+---------+------+------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+------+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+-------+------------------+
| 4164 | root | localhost | test | Query | 0 | NULL | show processlist |
+------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
So average its taking 6 seconds to load chunks of a large file:
Let's look at .MYD/.MYI (data and index) file size as the script is importing data:
[akhan@xxxx ~]$ sudo ls -lh /var/lib/mysql/test/ | grep alertdata_2
-rw-rw---- 1 mysql mysql 4.1G Aug 9 10:11 alertdata_2.MYD
-rw-rw---- 1 mysql mysql 246M Aug 9 10:11 alertdata_2.MYI
-rw-rw---- 1 mysql mysql 8.7K Aug 9 09:55 alertdata_2.frm
[akhan@xxxx ~]$ sudo ls -lh /var/lib/mysql/test/ | grep alertdata_2
-rw-rw---- 1 mysql mysql 4.2G Aug 9 10:12 alertdata_2.MYD
-rw-rw---- 1 mysql mysql 251M Aug 9 10:12 alertdata_2.MYI
-rw-rw---- 1 mysql mysql 8.7K Aug 9 09:55 alertdata_2.fr
Edited 2 time(s). Last edit at 08/09/2012 07:19AM by Aftab Khan.