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

This article focuses on "the evolution of common bottlenecks and architectures of relational databases". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn about the common bottlenecks and architecture evolution of relational databases.

I. Common bottlenecks in relational databases

1. A large number of concurrent read / write operations lead to unbearable load pressure on a single database; 2. The amount of data stored in a single table is too large, resulting in inefficient retrieval

II. Structural evolution

Evolution 1. Master is responsible for the write operation of database read-write separation, while Slave, as a standby library, does not open write permissions, but allows read permissions, and keeps data synchronized before master and slave. If there is a high TPS (transactions per second, different from QPS) in Master, there will be a delay in synchronizing data between master and slave, so it is best to put the same data in the cache before writing to Master, so as to avoid the backup database cannot get the data in the case of high concurrency. Evolution 2. The so-called vertical division of database is to split the data tables redundant in a single database into different business libraries according to the vertical division of the actual business of the enterprise, so as to realize the data management and read / write operation of divide and conquer. According to the actual production experience, taking mysql as an example, when the data of a single table exceeds 5 million rows, the read operation will gradually become a bottleneck, and even index reconstruction can not solve the problem of low retrieval efficiency caused by data expansion. Because writing is sequential, basically the write operation will not become a bottleneck, and the read operation will have an upper limit. Evolution 3, the horizontal sub-database and horizontal sub-table horizontal sub-table is the original redundant in a single database of a single business table is divided into n "logically related" business sub-tables (such as: tab_011 tab_013...), different business sub-tables stored in different interval data, external formation of a whole, this is what we call Sharding (sharding) operation. If the TPS of Master is too high, we can also split the library horizontally. this implementation is complicated and needs to be responsible for data routing with the help of special sharding middleware. When the subsequent storage reaches a certain threshold, you only need to horizontally expand the existing business database and business table, and migrate the data.

III. The difference between MySQL Sharding and MySQL Cluster

MySQL Cluster is a cluster mode, which expands the parallel processing ability of the database, but the cost of use and maintenance is huge. MySQL Sharding is a distributed mode, which can not only improve the parallel ability of the database, but also solve the retrieval bottleneck caused by the excessive amount of data in a single table.

IV. Sharding middleware

1. Clearly define the Shard Key (routing condition) in the SQL statement, because the routing dimension determines the location of the data, so you need to define a set of specific routing algorithms and rules. 2. Common Sharding middleware: Cobar: open source, Proxy architecture, support MySQL database Mycat: open source, Proxy architecture, support any database TDDL: partial open source, application integration architecture, support MySQL and OracleShark (recommended): open source, application integration architecture, support MySQL3, multi-machine SequenceID solutions should take into account the uniqueness and continuity of the generated ID It is a desirable solution to rely on an independent peripheral single point system to complete it. If you apply to the database for each generation, it will inevitably degrade the performance. It is recommended to take a piece of ID from the database and cache it locally. 4. distributed transactions ensure the final consistency of data by using message middleware.

5. HA scheme of database

1. Master-slave switching is implemented based on the configuration center, and the data source information is uniformly configured in the ZooKeeper. Before the master-slave switch, we must make sure that Slave has read and write permissions turned on. 2. Implement master-slave switch based on Keepalived, install Keepalived program on master-slave machine, modify / etc/keepalive/keepalive.conf configuration file and configure VIP (Virtual IP Address, virtual IP address). 3. To ensure data consistency in the process of switching between master and slave databases, semi-synchronous replication can understand the forced data synchronization between master and slave, and ensure the real-time performance of master-slave data. Only when the slave node responds, the master node will respond to the transactions that have been completed by the requester. It is not recommended to enable this feature in scenarios with high traffic. Through redundant tables to achieve data consistency, redundant data checking. Message queue or log comparison. At this point, I believe you have a deeper understanding of the "common bottlenecks and architecture evolution of relational databases". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.