MySQL load data command performance test

The following environments are:

Software:

HOST: winxp sp3

Virtual Machine Software: wmware ws 7.1

Virtual Machine System: rhel5.1 + mysql5.157

Host hardware:

Cpu: Core 2 T8100

Memory: 3 GB

1. Mysql> desc t;
2. + ----------------- + ------------------- + ------ + ----- + --------- + ------- +
3. | Field | Type | Null | Key | Default | Extra |
4. + ----------------- + ------------------- + ------ + ----- + --------- + ------- +
5. | TABLE_CATALOG | varchar (512) | YES | NULL |
6. | TABLE_SCHEMA | varchar (64) | NO | NULL |
7. | TABLE_NAME | varchar (64) | NO | NULL |
8. | TABLE_TYPE | varchar (64) | NO | NULL |
9. | ENGINE | varchar (64) | YES | NULL |
10. | VERSION | bigint (21) unsigned | YES | NULL |
11. | ROW_FORMAT | varchar (10) | YES | NULL |
12. | TABLE_ROWS | bigint (21) unsigned | YES | NULL |
13. | AVG_ROW_LENGTH | bigint (21) unsigned | YES | NULL |
14. | DATA_LENGTH | bigint (21) unsigned | YES | NULL |
15. | MAX_DATA_LENGTH | bigint (21) unsigned | YES | NULL |
16. | INDEX_LENGTH | bigint (21) unsigned | YES | NULL |
17. | DATA_FREE | bigint (21) unsigned | YES | NULL |
18. | AUTO_INCREMENT | bigint (21) unsigned | YES | NULL |
19. | CREATE_TIME | datetime | YES | NULL |
20. | UPDATE_TIME | datetime | YES | NULL |
21. | CHECK_TIME | datetime | YES | NULL |
22. | TABLE_COLLATION | varchar (32) | YES | NULL |
23. | CHECKSUM | bigint (21) unsigned | YES | NULL |
24. | CREATE_OPTIONS | varchar (255) | YES | NULL |
25. | TABLE_COMMENT | varchar (80) | NO | NULL |
26. + ----------------- + ------------------- + ------ + ----- + --------- + ------- +
27. 21 rows in set (0.04 sec)
28. Mysql> select count (*) from t;
29. + ---------- +
30. | Count (*) |
31. + ---------- +
32. | 1, 502564 |
33. + ---------- +
34. 1 row in set (0.00 sec)
35. Mysql> select * from t into outfile'/Tmp/t.txt'Fields terminated','Enclosed'"';
36. Query OK, 502564 rows affected (5.09 sec)
37. Mysql> create table t1 select * from t where 1 <> 1;
38. Query OK, 0 rows affected (0.02 sec)
39. Records: 0 Duplicates: 0 Warnings: 0
40. Mysql> select * from t1;
41. Empty set (0.01 sec)
42. Mysql> load data infile'/Tmp/t.txt'Into table t1 fields terminated','Enclosed'"';
43. Query OK, 502564 rows affected (8.73 sec)
44. Records: 502564 Deleted: 0 Skipped: 0 Warnings: 0
45. Mysql> select count (*) from t;
46. + ---------- +
47. | Count (*) |
48. + ---------- +
49. | 1, 502564 |
50. + ---------- +
51. 1 row in set (0.00 sec)
52. Mysql> select count (*) from t1;
53. + ---------- +
54. | Count (*) |
55. + ---------- +
56. | 1, 502564 |
57. + ---------- +
58. 1 row in set (0.00 sec)
59. Mysql>

Figure 1 use information_schema.tables to generate the test table t.

1. [Root @ rhel5 ~ 05:04:56] # ll-h/tmp
2. Total 88 M
3. -Rw-1 mysql 80 Sep 21 16:16 a.txt
4. Drwx ------ 2 root 4.0 K Feb 18 2015 keyring-mViyDu
5. Srwxr-xr-x 1 Oracle oinstall 0 Aug 3 2010 mapping-oracle
6. Srwxr-xr-x 1 root 0 Feb 18 2015 mapping-root
7. -Rw ---- 1 mysql 2.8 K Sep 21 16:56 mysqlslow. log
8. Srwxrwxrwx 1 mysql 0 Sep 21 :17 mysql. sock
9. Srw ------- 1 oracle oinstall 0 Aug 3 2010 scim-panel-socket: 0-oracle
10. Srw ------- 1 root 0 Feb 18 2015 scim-panel-socket: 0-root
11. Drwx ------ 2 root 4.0 K Sep 21 ssh-kahSRu6355
12. Drwx ------ 2 root 4.0 K Sep 21 ssh-XCGOuG6217
13. -Rw-1 mysql 88 M Sep 21 :01 t.txt

Figure 2 Tsung data file of the test table is t.txt

The test procedure is as follows:

USE insert into t select * from t; insert data until the number of records is 0.5 million.

Then, import t data to a text file.

Use the loaddata command to import data from a text file to table t2.

The operation time has been shown in the figure, and the speed is much faster than I expected!