Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to solve the problem of high concurrency in the database. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope you will have a certain understanding of relevant knowledge after reading this article.

How to solve the problem of high database concurrency? Solve the problem of high database concurrency using cached Web application architecture, add Redis cache database, add database index, statically page, use stored procedure, MySQL master-slave read/write separation, separate tables and databases, and Load Balancer cluster.

Common solutions to high database concurrency:

1) Cache Web application architecture: Add a cache layer between the Web layer and the DB(database) layer. The main purpose is to reduce the burden of database reading and improve the speed of data reading. Cache access media memory, can consider the use of distributed cache layer, so that it is easier to break the memory limit, while increasing flexibility.

2) Add Redis cache database

3) Add database index

4) static page: the highest efficiency, the least consumption is pure static html page, so we try to make the pages on our website use static pages to achieve, this simplest method is actually the most effective method. Users can directly access the page, without going through so many processes like VC structure, which is more suitable for the situation where a large amount of page information is transferred by the foreground program, but the update frequency is very small.

5) Use stored procedures: processing a request requires multiple access to the database operation, you can put the operation into the storage process, so that only one database access can be done.

6) MySQL master-slave read-write separation: When the write pressure of the database increases, the cache layer (such as Memcached) can only relieve the read pressure of the database. Concentrating reads and writes on one database can overwhelm the database. The master-slave model is used to achieve read-write separation to improve read-write scalability and library scalability. Read-write separation is writing only on the master server and reading only on the slave server. The basic principle is to let the master database handle transactional queries and the slave database handle ect queries. Database replication is used to synchronize changes caused by transactional queries (add, delete and change) to the slave database in the cluster. MySQL read-write separation improves system performance:

1. Master and slave are only responsible for their own reading and writing, which greatly alleviates the contention between X lock and S lock.

Slave can configure MyISAM engine to improve query performance and save system opening.

3, master direct write is concurrent, slave binlog recovery sent through the master library is asynchronous.

Slave can set some parameters separately to improve its reading performance.

5. Increase redundancy and improve availability. To achieve master-slave separation, MySQL middleware such as Atlas can be used

7) Sub-table sub-library: On the basis of cache layer cache, MySQL master-slave replication, and read-write separation, the write pressure of MySQL main library begins to appear bottleneck, and the data volume continues to soar. MyISAM uses table locks, and serious lock problems occur under high concurrency. A large number of high and MySQL applications begin to use InnoDB engine instead of MyISAM. MySQL architecture using Master-Slave mode can only expand the reading of the database, while the operation of the data is still concentrated on the Master. At this time, it is necessary to further expand the throughput capacity of the database to meet the needs of high concurrent access and mass data storage. For a single table that is accessed very frequently and has a large amount of data, the first thing to do is to reduce the number of records in the table, in order to reduce the time required for data queries and improve the throughput of the database.

Before dividing tables, you first need to select an appropriate table strategy (try to avoid multi-table associative queries), so that the data can be distributed evenly among multiple tables without affecting normal queries. Sub-table can solve the problem of query efficiency reduction caused by large data volume of single table, but it can not bring qualitative improvement to concurrent processing ability of database. In the face of high concurrent read and write accesses, when the database master server cannot bear the pressure of write operations, it is meaningless to expand the ve server no matter how, so as to split the database, so as to improve the database entry capacity, that is, the vertical split of the database.

8) Load Balancer Cluster: Distribute a large number of concurrent requests to multiple processing nodes. Load Balancer clusters also achieve high availability because individual processing nodes do not affect the entire service. Load Balancer will be the ultimate solution for large Web sites to deal with high traffic loads and a large number of concurrent requests.

About how to solve the problem of high concurrency database to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.