Performance Comparison of massive MySQL Data warehouse receiving

Performance Comparison of massive MySQL Data warehouse receiving

Enterprise IM revised
User chat content should be stored in the database.

Generally, JAVA Insert MySQL supports the following methods:
1. automatically submit Insert
2. transaction commit Insert
3. Batch submission
4. Use the Load File Interface

The structure of the simulated table is as follows:

Create table chat_message (
 
Id bigint primary key auto_increment,

Src_userid bigint not null,

Target_userid bigint not null,

Message varchar (200 ),

Ts timestamp not null default current_timestamp,

S1 int,

S2 int,

S3 int,

S4 int

);

The following code uses four methods: Insert 2 W records and record the execution time.

Dependency
Commons-lang3-3.3.2.jar
Mysql-connector-java-5.1.31-bin.jar (performance impact on earlier versions of drivers)

1. import java. io. ByteArrayInputStream;
2. import java. io. InputStream;
3. import java. io. UnsupportedEncodingException;
4. import java. SQL. Connection;
5. import java. SQL. DriverManager;
6. import java. SQL. PreparedStatement;
7. import java. SQL. SQLException;
8.
9. import org. apache. commons. lang3.RandomStringUtils;
10.
11. public class Main {
12. private static String URL = "jdbc: mysql: // 127.0.0.1: 3306/mvbox ";
13. private static String USERNAME = "xx ";
14. private static String PWD = "xx ";
15. private static int MAX = 20000;
16. private static String SQL = "insert into chat_message (src_userid, target_userid, message, s1, s2, s3, s4) values (?,?,?,?,?,?,?) ";
17.
18. public static void main (String [] args) throws ClassNotFoundException, SQLException, UnsupportedEncodingException {
19. long start = System. currentTimeMillis ();
20. testLoadFile (100 );
21. long end = System. currentTimeMillis ();
22. System. out. println (end-start ));
23. System. out. println (MAX/(end-start)/1000 ));
24 .}
25.
26. private static Connection getConnection () throws SQLException, ClassNotFoundException {
27. Class. forName ("com. mysql. jdbc. Driver ");
28. Connection con = DriverManager. getConnection (URL, USERNAME, PWD );
29. return con;
30 .}
31.
32. private static void testInsert () throws ClassNotFoundException, SQLException {
33. Connection con = getConnection ();
34. con. setAutoCommit (false );
35. PreparedStatement pt = con. prepareStatement (SQL );
36. int I = 0;
37. while (I <MAX ){
38. pt. setLong (1, 1 + (int) (Math. random () * 100000000 ));
39. pt. setLong (2, 1 + (int) (Math. random () * 100000000 ));
40. pt. setString (3, RandomStringUtils. randomAscii (200 ));
41. pt. setInt (4, 1 );
42. pt. setInt (5, 1 );
43. pt. setInt (6, 1 );
44. pt. setInt (7, 1 );
45. pt.exe cuteUpdate ();
46. con. commit ();
47. I ++;
48 .}
49. con. close ();
50 .}
51.
52. private static void testInsertAutoCommit () throws ClassNotFoundException, SQLException {
53. Connection con = getConnection ();
54. con. setAutoCommit (true );
55. PreparedStatement pt = con. prepareStatement (SQL );
56. int I = 0;
57. while (I <MAX ){
58. pt. setLong (1, 1 + (int) (Math. random () * 100000000 ));
59. pt. setLong (2, 1 + (int) (Math. random () * 100000000 ));
60. pt. setString (3, RandomStringUtils. randomAscii (200 ));
61. pt. setInt (4, 1 );
62. pt. setInt (5, 1 );
63. pt. setInt (6, 1 );
64. pt. setInt (7, 1 );
65. pt.exe cuteUpdate ();
66. I ++;
67 .}
68. con. close ();
69 .}
70.
71. private static void testBatchInsert (int batchSize) throws ClassNotFoundException, SQLException {
72. Connection con = getConnection ();
73. con. setAutoCommit (false );
74. PreparedStatement pt = con. prepareStatement (SQL );
75. int I = 0;
76. while (I <MAX ){
77. pt. setLong (1, 1 + (int) (Math. random () * 100000000 ));
78. pt. setLong (2, 1 + (int) (Math. random () * 100000000 ));
79. pt. setString (3, RandomStringUtils. randomasci (200 ));
80. pt. setInt (4, 1 );
81. pt. setInt (5, 1 );
82. pt. setInt (6, 1 );
83. pt. setInt (7, 1 );
84. pt. addBatch ();
85. if (I % batchSize = 1 ){
86. pt.exe cuteBatch ();
87. con. commit ();
88 .}
89. I ++;
90 .}
91. pt.exe cuteBatch ();
92. con. commit ();
93. con. close ();
94 .}
95.
96. private static void testLoadFile (int batchSize)
97. throws ClassNotFoundException, SQLException, UnsupportedEncodingException {
98. String fieldsterminated = "\ t ";
99. String linesterminated = "\ t \ r \ n ";
100. String loadDataSql = "load data local infile 'SQL .csv' INTO TABLE chat_message FIELDS TERMINATED '"
101. + fieldsterminated + "'Lines terminated by '" + linesterminated
102. + "'(src_userid, target_userid, message, s1, s2, s3, s4 )";
103. Connection con = getConnection ();
104. con. setAutoCommit (false );
105. PreparedStatement pt = con. prepareStatement (loadDataSql );
106. com. mysql. jdbc. PreparedStatement mysqlStatement = null;
107. if (pt. isWrapperFor (com. mysql. jdbc. Statement. class )){
108. mysqlStatement = pt. unwrap (com. mysql. jdbc. PreparedStatement. class );
109 .}
110.
111. int I = 0;
112. StringBuilder sb = new StringBuilder (10000 );
113. while (I <MAX ){
114. sb. append (1 + (int) (Math. random () * 100000000 ));
115. sb. append (fieldsterminated );
116. sb. append (1 + (int) (Math. random () * 100000000 ));
117. sb. append (fieldsterminated );
118. sb. append (RandomStringUtils. randomAscii (200). replaceAll ("\\\\",""));
119. sb. append (fieldsterminated );
120. sb. append (1 );
121. sb. append (fieldsterminated );
122. sb. append (1 );
123. sb. append (fieldsterminated );
124. sb. append (1 );
125. sb. append (fieldsterminated );
126. sb. append (1 );
127. sb. append (linesterminated );
128. if (I % batchSize = 1 ){
129. byte [] bytes = sb. toString (). getBytes ();
130. InputStream in = new ByteArrayInputStream (bytes );
131. mysqlStatement. setLocalInfileInputStream (in );
132. mysqlStatement.exe cuteUpdate ();
133. con. commit ();
134. sb = new StringBuilder (10000 );
135 .}
136.
137. I ++;
138 .}
139. byte [] bytes = sb. toString (). getBytes ();
140. InputStream in = new ByteArrayInputStream (bytes );
141. mysqlStatement. setLocalInfileInputStream (in );
142. mysqlStatement.exe cuteUpdate ();
143. con. commit ();
144.
145. con. close ();
146 .}
147 .}

Test results:

Execution Method	Execution time (MS)	Insert count per second
Automatic submission	17437	1176
Transaction commit	22990	909
Each 10 batchInsert entries are submitted	12646	1666
Each 50 batchInsert entries are submitted	13758	1538
Each 100 batchInsert entries are submitted	15870	1333
Loadfile submitted every 10	6973	3333
Each 50 loadfiles are submitted.	5037	4000
Loadfile: each 100 submissions	4175	5000

 

For more details, please continue to read the highlights on the next page:

1 2 Next Page