Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL performance comparison of large amounts of data storage     - Linux Firewall IPCop Profile (Linux)

- PostgreSQL procedural language learning (Database)

- Linux file compression and archiving (Linux)

- Linux set to select the appropriate level of security of the network according to deployment (Linux)

- A brief description of Java 8 new features introduced syntax (Programming)

- To install Jetty server configuration in detail (Server)

- Generated characters using Java Videos (Programming)

- MongoDB version 3.2 WiredTiger storage engine performance tests (Database)

- Linux beginners to develop the seven habits (Linux)

- Debian 7.8 system installation and configuration process (Linux)

- PHP Performance Analysis and Experiment: Performance Micro Analysis (Programming)

- Linux operating system buffer overflow attacks Countermeasures (Linux)

- How to install Ubuntu applications Device 7 (Linux)

- Install Oracle database error process of [INS-35172] (Database)

- Java Foundation - The relationship between abstract classes and interfaces (Programming)

- Oracle table compression Technology Introduction (Database)

- To setup Cocos2dx project under Ubuntu (Linux)

- Linux CentOS configuration SVN + SVN hook (Server)

- MySQL Online DDL tools of pt-online-schema-change (Database)

- CentOS 6.x Basic System Optimization after installation (Linux)

 
         
  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:      
 
- Python 3 for instructions encoded string conversion (Programming)
- Gentoo: startx problem appears Failed to load module (Linux)
- Proficient in C / C ++ can also find a very good job (Programming)
- Linux system security Comments (Linux)
- Linux POST fstab configuration file read-only variable can not be modified problem (Linux)
- Docker deployment practices in Ubuntu (Server)
- [Android] Eclipse does not update the Android SDK Manager solution [using GoAgent] (Programming)
- Oracle Bug caused by the collection of AWR Snapshot fault (Database)
- Custom Android UI template Comments (Programming)
- Create a custom pixel format based on an existing image data BufferedImage (Programming)
- Oracle RAC 10.2.0.5 upgrade to 11.2.0.4 problems encountered (Database)
- Install NetBeans IDE 8.0 on Ubuntu, Linux Mint, Elementary OS, and Debian (Linux)
- Java filter (Programming)
- How a lot of blocking malicious IP address in Linux (Linux)
- Android Studio quick overview of Gradle (Programming)
- How apt-get limited use IPv4 or IPv6 protocol to download (Linux)
- Awk include binding capacity larger than the specified size of all files directory (Linux)
- Oracle 11g statistics collection - collection of multi-column statistics (Database)
- CentOS 6.5 platform offline compile and install PHP5.6.6 (Server)
- 12 Linux Process Management Commands (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.