| Bug #29300 | Falcon LOAD_DATA performance issue | ||
|---|---|---|---|
| Submitted: | 22 Jun 2007 10:48 | Modified: | 29 Aug 2008 20:53 |
| Reporter: | Yoshinori Matsunobu | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Falcon storage engine | Severity: | S2 (Serious) |
| Version: | 5.2.4-falcon-alpha | OS: | Any |
| Assigned to: | Hakan Küçükyılmaz | CPU Architecture: | Any |
[22 Jun 2007 10:48]
Yoshinori Matsunobu
[25 Jun 2007 22:13]
MySQL Verification Team
Thank you for the bug report.
Microsoft Windows [versão 6.0.6000]
Copyright (c) 2006 Microsoft Corporation. Todos os direitos reservados.
c:\dev>cd 6.0
c:\dev\6.0>bin\mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.1-alpha-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1 (c1 BIGINT PRIMARY KEY, c2 INT, c3 VARCHAR(100)) engine=falcon;
Query OK, 0 rows affected (0.95 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` bigint(20) NOT NULL,
`c2` int(11) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=Falcon DEFAULT CHARSET=latin1
1 row in set (0.06 sec)
mysql> load data infile 'c:/temp/datafile.sql' into table t1 fields terminated by ',' lines terminated by '\n';
Query OK, 10000000 rows affected (12 min 47.07 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
c:\dev\6.0>bin\mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.1-alpha-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1 (c1 BIGINT PRIMARY KEY, c2 INT, c3 VARCHAR(100)) engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` bigint(20) NOT NULL,
`c2` int(11) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> load data infile 'c:/temp/datafile.sql' into table t1 fields terminated by ',' lines terminated by '\n';
Query OK, 10000000 rows affected (6 min 15.04 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0
small program to create the datafile:
#include <iostream>
#include <fstream>
using namespace std;
void main()
{
ofstream out("c:\\temp\\datafile.sql");
for (int n = 1; n <= 10000000; n++)
{
out << n << ',' << n+1 << ',' << "ABCDEFGHIJabcdefghij\n";
}
}
[18 Oct 2007 22:30]
Kevin Lewis
Miguel, can you retest this both with mysql-6.0-falcon and with mysql-6.0-falcon -team?
[26 Oct 2007 9:20]
Kevin Lewis
Please retest only with mysql-6.0-falcon. I now has newer performance improvements.
[19 Nov 2007 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[11 Feb 2008 19:29]
Philip Stoev
I am setting this bug to verifed because I have the following statistics using the latest mysql-6.0 tree: Falcon: mysql> load data local infile '/build/tests-6.0/mysql-test//suite/systems/data/tb1.txt' into table tb1_eng1 (f1,f2,f3,f4 ); Query OK, 5000000 rows affected (8 min 9.23 sec) Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0 Innob: mysql> load data local infile '/build/tests-6.0/mysql-test//suite/systems/data/tb1.txt' into table tb1_eng1 (f1,f2,f3,f4 ); Query OK, 5000000 rows affected (5 min 1.73 sec) Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0 Table is: create table tb1_eng1 ( i1 int NOT NULL auto_increment, primary key (i1), f1 int, f2 char (15), f3 decimal (10,3), f4 datetime );
[27 Feb 2008 19:41]
Kevin Lewis
Hakan or Yoshinori, Can you verify if this LOAD DATA performance problem still exists. I think that we identified a similar problem as a serialization in starting new transactions. That is fixed in the current mysql-6.0-falcon and mysql-6.0-release 6.0.4 trees. Please retest.
[29 Aug 2008 20:03]
Kevin Lewis
Hakan, Now that we can do an Online Add/Drop Index, and this bug was opened over a year ago, can you or John do another test and re-verify this?
[29 Aug 2008 20:53]
Hakan Küçükyılmaz
Can't repeat with latest Falcon code anymore: [22:47] root@(none)>use test; Database changed [22:48] root@test>CREATE TABLE t1 (c1 BIGINT PRIMARY KEY, c2 INT, c3 VARCHAR(100)) ENGINE InnoDB; Query OK, 0 rows affected (0.01 sec) [22:48] root@test>load data local infile '/home/hakan/src/c/foo.txt' into table t1 fields terminated by ','; Query OK, 10000000 rows affected (2 min 35.87 sec) Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0 [22:51] root@test>CREATE TABLE t2 (c1 BIGINT PRIMARY KEY, c2 INT, c3 VARCHAR(100)) Engine Falcon; Query OK, 0 rows affected (0.09 sec) [22:52] root@test>load data local infile '/home/hakan/src/c/foo.txt' into table t2 fields terminated by ','; Query OK, 10000000 rows affected (2 min 6.03 sec) Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0
