Summary: How to Use redis cache and indexing to process millions of concurrent databases and redis Indexes

Summary: How to Use redis cache and indexing to process millions of concurrent databases and redis Indexes

Note: In practical application, this design requires your own design. This article only provides one idea. Preparations: After the installation, the local redis server will use the mysql database to insert 10 million pieces of data in advance. You can refer to my previous article to insert data, which will not be detailed here. My general practice is as follows. The Code uses multiple threads to access my database. If I access the redis cache before accessing the database, I will query the database, it should be noted that the maximum number of connections in redis is preferably set to 300, otherwise many errors will occur.

Paste the code

Package select; import redis. clients. jedis. jedisPool; import redis. clients. jedis. jedisPoolConfig; public class SelectFromMysql {public static void main (String [] args) {JedisPool pool; JedisPoolConfig config = new JedisPoolConfig (); // create a redis connection pool // set the maximum number of connections, -1 unlimited config. setMaxTotal (300); // set the maximum idle connection config. setMaxIdle (100); // set the maximum blocking time. Remember to set it to milliseconds config. setMaxWaitMillis (100000); // create a connection pool = new JedisPool (config, "127.0.0.1", 62.16,200000); for (int I = 9222000; I <= 9222200; I ++) {// set the number of threads used to concurrently access String teacherName = String. valueOf (I); new ThreadToMysql (teacherName, "123456", pool ). start ();}}}

 

Package select; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; import redis. clients. jedis. jedis; import redis. clients. jedis. jedisPool; public class ThreadToMysql extends Thread {public String teacherName; public String password; public JedisPool pool; public ThreadToMysql (String teacherName, String password, JedisPo Ol pool) {// constructor input the name and password of the instructor to query logon this. teacherName = teacherName; this. password = password; this. pool = pool;} public void run () {Jedis jedis = pool. getResource (); Long startTime = System. currentTimeMillis (); // start time if (jedis. get (teacherName )! = Null) {Long entTime = System. currentTimeMillis (); // start time System. out. println (currentThread (). getName () + "cache Result:" + jedis. get (teacherName) + "Start Time:" + startTime + "End Time:" + entTime + "time used:" + (entTime-startTime) + "ms"); pool. returnResource (jedis); System. out. println ("release this redis connection");} else {String url = "jdbc: mysql: // 127.0.0.1/teacher"; String name = "com. mysql. jdbc. driver "; String user =" root "; String password = & Quot; 123456 & quot; Connection conn = null; try {Class. forName (name); conn = DriverManager. getConnection (url, user, password); // get the connection conn. setAutoCommit (false); // disable automatic submission, otherwise conn. when commit () runs to this sentence, an error is returned.} catch (ClassNotFoundException e1) {e1.printStackTrace ();} catch (SQLException e) {e. printStackTrace ();} if (conn! = Null) {String SQL = "select t_name from test_teacher where t_name = '" + teacherName + "' and t_password = '" + password + "'"; // SQL Statement String t_name = null; try {Statement stmt = conn. createStatement (); ResultSet rs1_stmt.exe cuteQuery (SQL); // obtain the result set if (rs. next () {t_name = rs. getString ("t_name"); jedis. set (teacherName, t_name); System. out. println ("release this connection");} conn. commit (); stmt. close (); conn. close ();} catch (SQLException e) {e. printStackTrace ();} finally {pool. returnResource (jedis); System. out. println ("release this connection");} Long end = System. currentTimeMillis (); System. out. println (currentThread (). getName () + "Database Query Result:" + t_name + "Start Time:" + startTime + "end Time:" + end + "Time: "+ (end-startTime) +" ms ");} else {System. out. println (currentThread (). getName () + "Database Connection Failed :");}}}}

My database table data is like this. We can see that my t_name is 1-10000000, And the password is fixed 123456. Using the loop creation thread, it is good to set the number of incoming loops as the t_name of the query.

Using redisCache replacement and indexing Solution

1.At 200Concurrent access:

First access result: Because this data does not exist in the first access cache, the speed is very slow.

Slowest 90 + seconds

Result:

Slowest, more than 700 milliseconds

2.When I try 1000Redis During Concurrent thread accessCause: reidsNo data to be searched in the cache,Search from the database, 1000Threads concurrently access the database for too long, resulting in redisConnection wait timeout (Even if the redisThe timeout wait time of is set to 100.It's useless in minutes. redis will be reportedConnection rejected error)

3. After I insert 1 million pieces of data into the redis Cache Server in advance using a loop, I only need 5 ~ The query results are obtained in 6 seconds, which is surprisingly fast and no error is reported.

4. when I upgraded the number of concurrent threads to 1 million under 3 conditions, I tested the query performance in the millions of concurrent conditions and found that there was no pressure at all. The results can be found in several milliseconds for each thread, at this time, the speed is limited by the computer CPU. My test computer is 4-core and processing 1 million threads is slow. below is, I stopped running when I ran to more than 0.5 million threads.

 

All of the above are the fields queried by the database that are directly searched using the redis cache without indexing.

In addition, there is a drawback that millions of concurrent accesses need to put data in the cache in advance, which is not scientific in reality (because they do not know those are hot data ), next let's take a look at how to use the index and cache effect.

1. Add a composite index to the t_name and t_password Fields

 

Let's take a look at the results of creating 1 million concurrent access threads when there is an index and redis cache has no data in advance.

No problem. In this way, millions of concurrent accesses are completed, but the thread created for my program is slow because my computer has 4-core CPU (but 1 million threads need to be created ), this is the performance of the hardware device. It is okay if the hardware performance of the device is sufficient.

The following is my summary:

1. There are only two types of optimization solutions. One is to add a composite index to the queried fields. The other is to add cache to users and databases.

2. indexing solution: Face 1 ~ There is no pressure for concurrent connections, and the bottleneck is the maximum number of connections to the database, in the above example, I used show global status like 'max _ used_connections 'to view the database. We can see that the maximum number of response connections in the database is more than 5700. If this number is exceeded, tomcat directly reports that the connection is rejected or the connection has expired.

3. cache solution: the test above shows that if we synchronize tens of millions of data records in the database to the redis cache in advance, the bottleneck is the hardware performance of our equipment, if our host has hundreds of core CPUs, even tens of millions of concurrent jobs can be completely stress-free, with a good user experience.

4. index + cache solution: cache data that is not to be queried in advance, and test the database without pressure at 10 thousand of the concurrency. The program first queries the cache and then queries the database, which greatly reduces the pressure on the database, even if the cache does not hit 10 thousand of the concurrency, it can still access the database normally. If the cache does not hit 0.1 million of the concurrency, there is still no pressure on the database. However, the redis server sets a maximum of 300 connections to process 0.1 million of threads, the 4-core CPU cannot be processed, and many redis instances cannot be connected. I used show global status like 'max _ used_connections 'to check the database and found that the maximum number of response connections is 388, so the database will not be suspended because of this low level.

5. Application Scenario: a. Concurrent queries of hundreds or less than 2000 can directly add a composite index. B. If you do not want to add indexes and high concurrency, you can put the data in the cache in advance. With the support of hardware devices, you can solve millions of concurrent jobs. C. indexing and caching without data in advance can solve the problem of millions of concurrent connections supported by hardware devices. D. If no index is added and no data is cached in advance, it will take more than 80 seconds to get the result. The user experience is poor.

6. principle: in fact, if redis is used, why does the database not crash? Because the maximum number of connections in redis is 300, so the maximum number of connections in the database is also more than 300, so it will not crash, as for why redis is set to 300, an error is reported (the connection is rejected) or the wait timeout occurs (this error is reported even if the wait timeout period is set to a long time ).

Note: This article does not represent the actual application development scenario. It is more about providing an idea and a solution. please correct me if you have any errors. Thank you.

Technical Exchange Group: 494389786

Source code:

Http://download.csdn.net/detail/qq_32780741/9606370

The jar package required by this Code:

Http://download.csdn.net/detail/qq_32780741/9606380