Summary: How to use Redis cache and index to process database millions concurrency

Foreword: In the practical application this kind of practice design needs the reader own design, this article only provides one kind of thought. Preparation: After installing the local number Redis server, using MySQL database, insert 10 million data beforehand, can refer to my previous article insert data, here no longer elaborate. I probably do this, the code uses multi-threaded access to my database, before accessing the database to access the Redis cache is not to query the database, it is important to note that Redis maximum number of connections is best set to 300, otherwise there will be a lot of error.

Put the code on it.

?

12345678910111213141516171819202122232425

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();//创建redis连接池 // 设置最大连接数，-1无限制 config.setMaxTotal(300); // 设置最大空闲连接 config.setMaxIdle(100); // 设置最大阻塞时间，记住是毫秒数milliseconds config.setMaxWaitMillis(100000); // 创建连接池 pool = new JedisPool(config, "127.0.0.1", 6379,200000); for (int i =9222000; i <=9222200; i++) {//这里自己设置用多少线程并发访问 String teacherName=String.valueOf(i); new ThreadToMysql(teacherName, "123456",pool).start(); } }}

?

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 66676869707172737475767778798081

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,JedisPool pool) {//构造函数传入要查询登录的老师姓名和密码 this.teacherName=teacherName; this.password=password; this.pool=pool; } public void run() { Jedis jedis = pool.getResource(); Long startTime=System.currentTimeMillis();//开始时间 if (jedis.get(teacherName)!=null) { Long entTime=System.currentTimeMillis();//开始时间 System.out.println(currentThread().getName()+" 缓存得到的结果: "+jedis.get(teacherName)+" 开始时间:"+startTime+" 结束时间:"+entTime+" 用时:" +(entTime-startTime)+"ms"); pool.returnResource(jedis); System.out.println("释放该redis连接"); } else { 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);//获取连接 conn.setAutoCommit(false);//关闭自动提交，不然conn.commit()运行到这句会报错 } 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语句 String t_name=null; try { Statement stmt=conn.createStatement(); ResultSet rs=stmt.executeQuery(sql);//获取结果集 if (rs.next()) { t_name=rs.getString("t_name"); jedis.set(teacherName, t_name); System.out.println("释放该连接"); } conn.commit(); stmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); }finally { pool.returnResource(jedis); System.out.println("释放该连接"); } Long end=System.currentTimeMillis(); System.out.println(currentThread().getName()+" 数据库得到的查询结果:"+t_name+" 开始时间:"+startTime+" 结束时间:"+end+" 用时:"+(end-startTime)+"ms"); } else { System.out.println(currentThread().getName()+"数据库连接失败:"); } } }}

My database table data is like this. You can see that my t_name is 1-10000000, the password is fixed 123456. Creating threads with loops It's good to do the number of incoming loops as the t_name of the query.

using Redis cache substitution for indexed scenarios

1. under the concurrent access:

First access result: very slow because the data does not exist for the first access cache

The slowest more than 90 seconds

Results of running the second access (data already exists in the Redis database):

700 + milliseconds Slowest

2. When I try to access the threads concurrently, Redis simply hangs up,
The reason is that the Reids cache does not have the data to look for , from the database lookup, the simultaneous access to the database at the same time, the queue is too long.
Causes Redis connection Wait timeout ( even if you set the Redis timeout wait time to a few minutes, it will report the Redis connection denied error )

3. When I use the loop to insert 1 million data into the Redis cache server in advance , it takes only 5-6 seconds to get the query results under the 10,000-thread concurrent access test, which is surprisingly fast and does not report any errors.

4. Under 3 of the conditions I raised the concurrent threads to 1 million, the test in the million concurrency conditions query performance, found that there is no pressure, each thread is also a few milliseconds to find the results, this time limit my speed is the computer CPU. My test PC is 4 cores, processing 1 million threads is slower, below, I stopped running to the 50多万个 thread

Well, the above are the fields of the database query are not indexed directly using the Redis cache lookup

And there's a drawback, millions's concurrent access needs to put the data in the cache beforehand, in practice is not scientific (because it is not known that the hot data), the following to see how to use the index and cache effect

1. Add a combined index to the T_name and T_password fields

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

No problem, this completes the millions next to the concurrent access, but so my program creation thread is very slow, because my Computer 4 core CPU (but to create 1 million threads), this time is the performance of the hardware device, the device hardware performance is not a problem under the condition

Here is my summary:

1. There are only two of my optimization scenarios, one is to add a combination index to the field of the query. The other is to add the cache to the user and the database

2. Add Indexing scheme : facing The concurrency is no pressure, in the upper limit of the bottleneck is the maximum number of database connections,
In the above I use Show global status like ' max_used_connections ' view database to know the maximum number of database response connections is more than 5,700, more than this number Tomcat direct error connection is denied or the connection has expired

3. Caching scheme: In the above test can know, if we in advance to synchronize the database thousands data to the Redis cache, the bottleneck is our device hardware performance, if our host has hundreds of core CPU, even if the tens of the concurrency can also be completely stress-free, with a user is very good.

4. Index + cache scheme: The cache does not have to query the data, in 10,000 of the concurrent test database without pressure, the program first through the cache and then check the database greatly reduce the pressure of the database, even if the cache is not hit in the concurrency of 10,000 can also be normal access, 100,000 concurrent under the database is still no pressure, However, the Redis server sets the maximum number of connections 300 to handle 100,000 of threads, 4 cores CPU does not work, many redis can not connect.
I use show global status like ' max_used_connections ' to view the database to see that the maximum number of response connections is 388, so low that the database is not going to hang out.

5. Usage Scenarios:
A. Hundreds of or below 2000 concurrent Direct plus combined index is possible.
B. If you do not want to index and high concurrency, you can first put the data in the cache , hardware device support to resolve millions concurrency.
C. Indexed and cached without prior data, the millions concurrency problem can be resolved with hardware device support.
D. No index and cache without prior data, it is not advisable, more than 80 seconds to get results, the user experience is very poor.

6. Principle:
In fact, the use of Redis is why the database does not crash because the maximum number of Redis connections is 300, so that the database maximum number of simultaneous connections is more than 300, so do not hang up, as to why Redis is set to 300 because the settings are too large to error (connection is rejected) or wait timeout ( Even if the setting waits for a long time to expire it will also report the error.

The last note: This article does not represent the actual application development scenario, more is to provide a thought, a solution, if there are errors, please correct me, thank you

Technology Exchange Group: 494389786

This article source:

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

This code requires a jar package:

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

Http://www.cnblogs.com/fanwencong/p/5782860.html

Summary: How to use Redis cache and index to process database millions concurrency