Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL performance comparison of large amounts of data storage     - C ++ complex class of operator overloading (Programming)

- Windows 8.1 and Ubuntu 14.04 dual system uninstall Ubuntu Tutorial (Linux)

- ImportTsv-HBase data import tool (Database)

- Cooling solutions Ubuntu system (Linux)

- How to find the available network adapter on Ubuntu (Linux)

- MySQL root password reset under CentOS (Database)

- Android in the event delivery and handling mechanism (Programming)

- Ubuntu and Derivative Edition users install LMMS 0.4.15 (Linux)

- Sqoop data export import command (Database)

- Debian (Wheezy) Install Java environment / replace OpenJDK as the SUN JDK (Linux)

- Install multiple Linux distributions and Fedora 21 first experience on the same hard disk (Linux)

- ASM Management - How to Rename diskgroup (Database)

- Linux network monitoring tools ntopng installation (Linux)

- MySQL database under Linux to achieve automatic backup scheduled backup day (Database)

- CentOS 6.4 Telecom ADSL dial-up network configuration (Linux)

- Python: Finding meet the conditions specified in the file directory (Programming)

- mysqldump MySQL command-line tool (Database)

- RedHat 6.5 installation and deployment Openfire (Server)

- Simple to use Linux GPG (Linux)

- Java Learning: elegant string (Programming)

 
         
  MySQL performance comparison of large amounts of data storage
     
  Add Date : 2018-11-21      
         
       
         
  Revision of the unit IM
User chats to be stored in the database.

Usually JAVA Insert MySQL There are several ways
1. Insert automatically submitted
 2. Insert the transaction commits
 3. Submit Batch
4. Use the Load File Interface

Simulation table structure 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 codes are four ways, Insert 2w record records the execution time.

rely
commons-lang3-3.3.2.jar
 mysql-connector-java-5.1.31-bin.jar (low version of the driver has a performance impact)

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));
twenty four. }
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 ( 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.executeUpdate ();
46. ​​con.commit ();
47. ++;
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 ( 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.executeUpdate ();
66. ++;
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 ( 77. pt.setLong (1, 1 + (int) (Math.random () * 100000000));
78. pt.setLong (2, 1 + (int) (Math.random () * 100000000));
79. pt.setString (3, RandomStringUtils.randomAscii (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.executeBatch ();
87. con.commit ();
88.}
89. ++;
90.}
91. pt.executeBatch ();
92. con.commit ();
93. con.close ();
94.}
95.
96. private static void testLoadFile (int batchSize)
97. throws ClassNotFoundException, SQLException, UnsupportedEncodingException {
98. String fieldsterminated = "\ t \ t";
99. String linesterminated = "\ t \ r \ n";
100. String loadDataSql = "LOAD DATA LOCAL INFILE 'sql.csv' INTO TABLE chat_message FIELDS TERMINATED BY '"
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 ( 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.executeUpdate ();
133. con.commit ();
134. sb = new StringBuilder (10000);
135.}
136.
137. ++;
138.}
139. byte [] bytes = sb.toString () getBytes ().;
140. InputStream in = new ByteArrayInputStream (bytes);
141. mysqlStatement.setLocalInfileInputStream (in);
142. mysqlStatement.executeUpdate ();
143. con.commit ();
144.
145. con.close ();
146.}
147.}

Test Results:

Execution mode execution time (in milliseconds) Insert the number per second
Automatic commit 174,371,176
Transaction commit 22,990,909
batchInsert Submit 126,461,666 per 10
batchInsert Submit 137,581,538 per 50
batchInsert Submit 158,701,333 per 100
loadfile submit 69,733,333 per 10
loadfile submit 50,374,000 per 50
loadfile submit 41,755,000 per 100

The test, using a home computer, work computer performance than the unit configuration is better.

MySQL Configuration
innodb_buffer_pool_size = 512m
 innodb_flush_log_at_trx_commit = 0
 sync_binlog = 0
 innodb_support_xa = 0
 log_bin = master

Version 5.6.14

Each test, Insert 200w record.

1. Load File Interface, ordinary table, four indexes, records submitted once every 100


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

);

create index inx_1 on chat_message (src_userid, target_userid, s1, ts);

create index inx_2 on chat_message (src_userid, target_userid, s2, ts);

create index inx_3 on chat_message (src_userid, target_userid, s3, ts);

create index inx_4 on chat_message (src_userid, target_userid, s4, ts);
Test Results
 Sec 10638 Insert

 2. Load File interface that uses a date range partitioning


create table chat_message (
 
    id bigint 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,

    primary key (id, ts)

)

partition by range (UNIX_TIMESTAMP (ts))

(

    partition p1 VALUES LESS THAN (UNIX_TIMESTAMP ( '2015-10-01 00:00:00')),

    partition p2 VALUES LESS THAN (UNIX_TIMESTAMP ( '2015-11-01 00:00:00')),

    partition p3 VALUES LESS THAN (UNIX_TIMESTAMP ( '2015-12-01 00:00:00')),

    partition p4 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-01-01 00:00:00')),

    partition p5 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-02-01 00:00:00')),

    partition p6 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-03-01 00:00:00')),

    partition p7 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-04-01 00:00:00')),

    partition p8 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-05-01 00:00:00')),

    partition p9 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-06-01 00:00:00')),

    partition p10 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-07-01 00:00:00')),

    partition p11 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-08-01 00:00:00')),

    partition p12 VALUES LESS THAN (UNIX_TIMESTAMP ( '2016-09-01 00:00:00'))

);

 

create index inx_1 on chat_message (src_userid, target_userid, s1, ts);

create index inx_2 on chat_message (src_userid, target_userid, s2, ts);

create index inx_3 on chat_message (src_userid, target_userid, s3, ts);

create index inx_4 on chat_message (src_userid, target_userid, s4, ts);
Test Results
 Sec 10989 Insert

 3. On the date range partitioning based on an increase of four sub-partitions


create table chat_message (
 
    id bigint 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,

    primary key (id, ts, src_userid)

)

partition by range (UNIX_TIMESTAMP (ts))

subpartition by key (src_userid)

subpartitions 4 (

    partition p201506 VALUES LESS THAN (UNIX_TIMESTAMP ( '2015-10-01 00:00:00')),

    partition p201507 VALUES LESS THAN (UNIX_TIMESTAMP ( '2015-11-01 00:00:00')),

    partition p201508 VALUES LESS THAN (UNIX_TIMESTAMP ( '2015-12-01 00:00:00'))

);

 

create index inx_1 on chat_message (src_userid, target_userid, s1, ts);

create index inx_2 on chat_message (src_userid, target_userid, s2, ts);

create index inx_3 on chat_message (src_userid, target_userid, s3, ts);

create index inx_4 on chat_message (src_userid, target_userid, s4, ts);

Test Results
 8810 Insert second

3. On the date range partitioning based on an increase of 16 sub-district

 6688 Insert second

 4. On the date range partitioning based on the increase of 64 sub-district

Per 8368 Insert

in conclusion:
1. On the date range partition, and then increase Hash partition, significantly reduced the number of second Insert
2. As the amount of data increases, Insert number per second dropped significantly. For example, the table has data 200w, 200w additional data, Insert per second from about 1w fell directly about 1k.
     
         
       
         
  More:      
 
- PostgreSQL query lock and kill the process (Database)
- Chromium Install Flash Official Guide (Linux)
- Nginx concerning the location and rewrite applications proxy_pass (Server)
- How to install Unbound and DNSCrypt in Archlinux (Server)
- How to disable IPv6 on Ubuntu, Linux Mint, Debian (Linux)
- Using IntelliJ IDEA Import Spark Spark latest source code and compile the source code (Linux)
- Ubuntu System Log Configuration / var / log / messages (Linux)
- Python console achieve progress bar (Programming)
- CentOS 6.6 command-line automatic completion (Linux)
- Use nice, cpulimit and cgroups limit cpu usage (Linux)
- MySQL partition table Comments (Database)
- Linux SVN installation and configuration graphic tutorials (Server)
- Tab set to four spaces in Vim (Linux)
- Installation and use GAMIT / GLOBK Software (Linux)
- Based kubernetes Construction Docker Cluster Management Comments (Server)
- Linux Network Programming - raw socket instance: MAC Address Scanner (Programming)
- Understanding the Linux load average on other UNIX-Like systems (Linux)
- Linux system started to learn: how to solve the xxx is not in the sudoers file error (Linux)
- Let the terminal under Mac OS X as like Linux has displayed a variety of colors (Linux)
- STL spatial Configurator (Programming)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.