Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the principles of MySQL high-performance architecture design". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the principles of MySQL high-performance architecture design".

01. Technology selection

Choose the mature platform and technology, at the same time is the most familiar, can achieve the extreme, use good, not bad, do not need to be raw. At present, the mainstream branch versions of MySQL in the industry are Oracle official versions of MySQL, Percona Server and MariaDB.

02. Highly available options

The high availability solution is essentially a low downtime solution, which can be understood as the opposite of high availability is unavailable. In most cases, database downtime will lead to database unavailability. With the development of technology, many highly available components in open source databases (master-slave replication, semi-synchronous, MGR, MHA, Galera Cluster), corresponding scenarios, only suitable, not omnipotent, need to understand the advantages and disadvantages of each high availability.

03. Table design

The principles that are consistently adhered to and advocated in watch design:

Single table data quantity

All tables need to be annotated. It is recommended to limit the amount of data in a single table to less than 3000 million.

Do not save large field data

Big data does not store pictures, files, etc. in the database.

Table usage specification

Split large fields and fields with low access frequency to separate hot and cold data

The number of fields in a single table is controlled within 20.

Index specification

1. The number of indexes in a single table does not exceed 5.

two。 The number of fields in a single index does not exceed 5

Self-increment columns are recommended for 3.INNODB primary keys, primary keys should not be modified, and strings should not be used as primary keys.

If you do not specify a primary key, INNODB uses a unique and non-null index instead

4. In the case of a composite index, the largest field is placed in front of the index

5. Avoid redundant or duplicate indexes: create federated indexes reasonably (avoid redundancy)

6. Do not index on low cardinality columns, such as' gender'

7. Do not perform mathematical and functional operations in the index column

Character set utf8mb4 (partial characters, emoji)

04. Optimization principle

05. Replication mode

MySQL replication mode provides asynchronous mode, semi-synchronous mode, strong consistency of global transactions, and binglog synchronization. Synchronization is required between different business systems or between two databases. Asynchronous mode can prevent the spread of failures and efficiency problems, but strong consistency will be more complex, concurrency and transaction size are limited.

06. Separation principle

Distinguish the core business, important business, channel, and internal business systems, and set different architectures for different systems. Set up the best for the core business as sub-library, multi-active special highway, other business can do read-write separation, caching.

07. Expansibility

For the system scalability is very important, try to achieve horizontal expansion. Avoid over-reliance on vertical expansion, and have the ability to scale vertically and horizontally at the same time, for example, stateless applications should be deployed with multiple sets of load balancing and multi-active deployment, and database sub-database architecture.

08. Separation of reading and writing

Read more and write less (10% write 90% read)

There is a delay in replication, and the business is insensitive to delay.

Implementation method:

1. Configure read-write separation through application code

two。 Routing read-only libraries through intermediate agents &

3. Business and database are one unit.

09. Sub-database sub-table

When the number of data records in the table exceeds 30 million, no matter how good the index can no longer improve the speed of data query, it is necessary to split the table into more small tables to increase performance, increase flexibility and avoid database collapse.

The introduction of intermediate price should take into account the performance cost and aggregation requirements.

The principle of sub-database is to divide the database on the upper layer of app as far as possible, that is, traffic.

How much is appropriate: availability and performance meet TPS.

Routing: write to the configuration file or insert the table or zookeeper.

10. Filing principle

Historical data is archived regularly or moved to other big data platforms. It allows lightweight databases to cache more useful data.

In the MySQL partition table, be careful to avoid partition locks and only write-read scenarios.

11. Connection pooling requirements

Long links, automatic heavy links, delay and exception records, elastic links, detection full, exception alarms, advanced requirements

Is to record all visits, can expand a lot of capabilities.

Application and database connection pool settings, database connection number settings, FAQs.

A) the database connection pool setting applied is too small, once the database is relatively slow (new online applications, lack of indexes, etc.).

Queuing is serious, even an avalanche, but unfortunately the database capacity is still far from being exhausted.

B) it does not have the ability to find and relink the database in time.

C) isolation level setting: different performance under RR and RC.

twelve。 Application decoupling

Through the application to access the database rather than directly, the important business can not rely on the low security level system, the important business in the application layer is decoupled from the ordinary business, and the key business should be independent.

13. Component failure immunity

Single application, single hardware, or even single infrastructure, single site disaster recovery, business impact, fault resilience, to be conducted at the quarterly level.

14. Keyword component burden reduction

Especially for database access, the cost of database is the highest, the scalability is the most difficult, the availability guarantee is the most difficult, and the recovery is the most difficult and time-consuming.

Lighten the burden: don't use it if you can, use the simplest and lowest-cost statements, avoid big transactions, and be cautious about using two-stage transactions.

15. Grayscale database

To reduce the global impact of changing the database during release, only the grayscale of the application is not enough, there should be a special grayscale database. Under the framework of sub-database and read-write separation, a complete application architecture with database becomes very natural.

The grayscale environment is the production environment, and the production data also affects the production environment, but the scope is wider and more real than the test environment. In fact, it is a small-scale production environment. Similar to the internal test of the game.

16. High simulation architecture

Establish a high simulation architecture

Database, operating system upgrade: whether the application adapts, will the performance get better or worse?

Launch the application, change the system (such as changing the platform), and judge the business impact and performance bottleneck in advance

Deal with sudden trading volume, such as Singles' Day, where is the performance limit and where is the bottleneck.

17. Disaster recovery guarantee

High availability is the core requirement of operation and maintenance, and disaster recovery is the last barrier.

For example, double activity is better than single activity, MGR is better than replication architecture, and important systems should be built with high availability and disaster recovery.

18. Polycentric construction

Redundancy is the foundation, and multi-center construction is to improve disaster tolerance and expansion capacity, and to ensure business.

19. The application and database are a whole.

Applications work with operation and maintenance personnel to solve application decoupling, database decoupling, debt recovery, business monitoring, application routing, failover and so on. Usability, efficiency, failure recovery and other aspects should be involved together.

20. Performance improvement

The use of open source databases should be reasonable and effective with other types of surrounding databases to maximize performance. For example: Redis, MongoDB, ES, ClickHouse and so on.

Summary

1. The most suitable architecture is to combine software features and business scenarios, while achieving a cost-benefit balance.

two。 In the case of big data, we can use the separation of read and write and the division of database and table, but we should choose the appropriate one.

3. Those who are not suitable for sub-library should consider doing their best to make the core library small, and then expand the capacity through vertical expansion.

4. Use all kinds of technology, high availability and disaster recovery means to ensure its availability.

Thank you for your reading, the above is the content of "what are the principles of MySQL high-performance architecture design". After the study of this article, I believe you have a deeper understanding of what the principles of MySQL high-performance architecture design have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.