Send a wave of dry goods, test 2000 threads concurrently query 10 million database tables and index optimization

Following the article "Absolute dry Goods, teach you 4 minutes to insert 10 million data to MySQL database table, quickly come in" published in the blog Park home page to show a lot of reading, I this article is on the previous article of the Tens database table under high concurrent access to test access

The knowledge points of this article are as follows:

1. How to write dozens of lines of code can be simulated test high concurrent access to the Tens database table

2. Compare high concurrency (200 times/Second, 2000 times/second, 10,000 times/sec) database performance

3. Compare the Tens database with the large difference in index and non-index (in fact, I am surprised by the result of this test.)

For the 10 million data inserted in the previous article into the database, we conducted high concurrency under test (simulation teacher input name and password in 1 seconds to log into the database), the thread class code is as follows

Package Insert;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.resultset;import Java.sql.sqlexception;import Java.sql.statement;public class Threadtomysql extends Thread {public String teachername; public string Password;public threadtomysql (string teachername, string password) {// The constructor passes in the name and password of the teacher to be queried for login This.teachername=teachername;this.password=password;   public void Run () {String URL = "Jdbc:mysql://127.0.0.1/teacher";   String name = "Com.mysql.jdbc.Driver";   String user = "root";  String password = "123456";  Connection conn = null; try {class.forname (name); conn = drivermanager.getconnection (URL, user, password);//Get Connection Conn.setautocommit (FALSE);// Turn off autocommit, otherwise Conn.commit () will run to this sentence error} catch (ClassNotFoundException E1) {e1.printstacktrace ();} catch (SQLException e) { E.printstacktrace ();} if (conn!=null) {long starttime=system.currenttimemillis ();//Start time string sql= "SELECT ID from t_teacher where t_name= '" + Teachername+ "' and t_password= '" +password+ "'";//sql statement stringid=null;try {Statement stmt=conn.createstatement (); ResultSet rs=stmt.executequery (SQL);//Get result set if (Rs.next ()) {id=rs.getstring ("id");} Conn.commit (); Stmt.close (); Conn.close ();}    catch (SQLException e) {e.printstacktrace ();}    Long End=system.currenttimemillis (); System.out.println (CurrentThread (). GetName () + "Query results:" +id+ "Start Time:" +starttime+ "End Time:" +end+ "spents:" + (End-starttime) + " MS ");} else {System.out.println (CurrentThread (). GetName () + "database connection failed:");}}}

The test class code is as follows:

Package Insert;public class Testthreadtomysql {public static void main (string[] args) {for (int i = 1; I <=2000; i++) { String teachername=string.valueof (i); new Threadtomysql (TeacherName, "123456"). Start ();

I. Test without index:

Set the maximum number of connections for the database to 250:

Test code:

Package Insert;public class Testthreadtomysql {public static void main (string[] args) {for (int i = 1; I <=200; i++) {S Tring teachername=string.valueof (i); new Threadtomysql (TeacherName, "123456"). Start ();}}

Test results:

More than 100 seconds ah ... My God, this user experience is also out O (∩_∩) o haha ~

Two. Add the index again after the high concurrency test:

The database index SQL statement is as follows: Here I have a question, last week I added index for half an hour I did not add the index I stopped, this afternoon only took 551 seconds to add up the index ... I don't understand.

Clean down the project code and run again (try to remove the cache as often as possible, otherwise the results will vary):

Have you been shocked to see this result? Haha add the index from more than 100 seconds to a millisecond, query speed hint 10,000 times, query performance has been greatly improved by the abnormal level ~ ~ ~

I have 2 seconds to query a single record without indexing.

Use explain to view the statement to be able to scan the whole table, the performance of course greatly decreased

Let's challenge 2000 threads to concurrently access the query database. Look at the results:

Set the maximum number of database connections to 2500

Test code changed to 2000

Package Insert;public class Testthreadtomysql {public static void main (string[] args) {for (int i = 1; I <=2000; i++) { String teachername=string.valueof (i); new Threadtomysql (TeacherName, "123456"). Start ();}}

Results:

Performance is no problem, average dozens of milliseconds, very satisfied

Let's challenge 10,000 threads concurrently high concurrent access, you can first think about the results, haha

Setting the maximum number of database connections 12000

Change the test code to 10000 (prompt again.) Clean up the project to get rid of the cache, so the results are more accurate)

Package Insert;public class Testthreadtomysql {public static void main (string[] args) {for (int i = 1; I <=10000; i++) {String teachername=string.valueof (i); new Threadtomysql (TeacherName, "123456"). Start ();}}

The results are as follows (after running the computer found a little card):

The result is two kinds of error, 1. Connection request denied 2. Connection failed 3. But there's also a part of it that's successfully connected and running correctly

Then I see the maximum number of connection responses in the database:

You can see that even if your database is set to a higher level, your database server will not respond .... Response at most 5,758

A small summary, 1. You can test your own high Concurrency Challenge database performance, 2. A general understanding of the power of indexes in query performance is good for beginners to learn

Send a wave of dry goods, test 2000 threads concurrently query 10 million database tables and index optimization