MySQL 20 high-performance architecture design principles (worth collecting)

Open Source Database Architecture Design Principles

01. Technology selection

Choose mature platforms and technologies that are the most familiar to you and that you can use to the extreme, and use the good instead of the bad, and the familiar instead of the new. Currently, the mainstream MySQL branch versions in the industry include Oracle's official version of MySQL, Percona Server, and MariaDB.

02. High availability options

The essence of high availability solutions is to explore low downtime solutions. It can be understood that the opposite of high availability is unavailability. In most cases, database downtime will lead to database unavailability. With the development of technology, there are many high-availability components in open source databases (master-slave replication, semi-synchronization, MGR, MHA, Galera Cluster). For corresponding scenarios, there is only suitable one, not a panacea. It is necessary to understand the advantages and disadvantages of each high-availability component.

03. Table design

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

• Data volume of a single table
  

All tables need to be annotated, and the amount of data in a single table is recommended to be controlled within 30 million

• Do not save large field data
  

Do not store large data such as images and files in the database

• Table Usage Guidelines

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

The number of fields in a single table should be controlled within 20

• Index Specification
  

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

2. The number of fields in a single index does not exceed 5

3. For INNODB primary keys, it is recommended to use auto-increment columns. Primary keys should not be modified, and strings should not be used as primary keys.

If you do not specify a primary key, INNODB will use a unique and non-null value index instead.

4. If it is a composite index, put the field with the largest difference at the front of the index

5. Avoid redundant or duplicate indexes: Create joint indexes appropriately (avoid redundancy)

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

7. Do not perform mathematical operations or function operations on index columns

• Character set utf8mb4 (uncommon characters, emoticons)
  

04. Optimization principles

05. Copy method

MySQL replication 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. The asynchronous method can prevent the spread and expansion of failures and efficiency problems; however, strong consistency will be more complicated, and concurrency and transaction size will be limited.

06. Separation Principle

Differentiate business systems for core business, important business, channels, and internal business, and set up different architectures for different systems. The best setting for core businesses is to use sub-databases and multi-active dedicated highways, while other businesses can use read-write separation and caching.

07. Scalability

Scalability is very important for the system, and horizontal expansion should be achieved as much as possible. Avoid over-reliance on vertical expansion, and have the ability to expand both vertically and horizontally. For example, stateless applications should be deployed with multiple sets of load balancing and multi-active database architecture.

08. Read-write separation

• Scenario with more reads and less writes (10% writes and 90% reads)
• Replication has delays, but the business is not sensitive to delays
• Implementation:

1. Configure read-write separation through application code,

2. Routing read-only libraries through an intermediate proxy

3. Business and database as one unit

09. Separate databases and tables

• When the number of data records in a table exceeds 30 million, even the best indexes cannot improve the speed of data query. At this time, the table needs to be split into more small tables to increase performance and flexibility and avoid database crashes.
• The introduction of the intermediate price requires consideration of performance costs and aggregation demand.
• The principle of database division is to divide the database as much as possible at the upper layer of the app, that is, the traffic.
• The appropriate amount of points: availability and performance meet TPS.
• Routing: write to the configuration file or insert table or zookeeper.
  

10. Archiving Principles

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

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

11. Connection Pool Requirements

Long link, automatic relink, delay and exception recording, elastic link, full detection, abnormal alarm, advanced requirements

It records all access situations and can expand many capabilities.

Application and database connection pool settings, setting the number of connections allowed by the database, and common problems.

A) The application's database connection pool is set too small. Once the database response is slow (new application, lack of index, etc.), it should be

There are serious queues and even avalanches, and unfortunately the database capacity is far from being exhausted.

B) It does not have the ability to detect failures in a timely manner and reconnect to the database.

C) Isolation level setting: Different performance under RR and RC.

12. Application Decoupling

Access the database through applications rather than directly. Important businesses cannot rely on systems with low security levels. Important businesses and ordinary businesses at the application layer are decoupled, and key businesses must be independent.

13. Component Failure Immunity

Single application, single hardware, or even single infrastructure, single site disaster recovery, business impact, and fault recovery capabilities should be drilled on a quarterly basis.

14. Reduce the burden of keyword components

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

Reduce the burden: Do not use it if possible, use the simplest and lowest-cost statements, avoid large transactions, and use two-phase transactions with caution.

15. Grayscale Database

To reduce the global impact of database changes during release, it is not enough to have only application grayscale, but also a dedicated grayscale database. Under the database partitioning and read-write separation architecture, a complete application architecture including a database becomes very natural.

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

16. High-simulation architecture system

Establishing a high-simulation architecture system

• Database and operating system upgrades: whether applications adapt and whether performance improves or deteriorates
• Application launch and system changes (such as changing platforms) to predict business impact and performance bottlenecks
• To cope with sudden transaction volumes, such as during the Double Eleven shopping spree, we need to understand where the performance limits are and where the bottlenecks are.
  

17. Disaster recovery

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

For example, active-active dual is better than active-active single, MGR is better than replication architecture, and important systems must be built for high availability and disaster recovery.

18. Multi-center construction

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

19. The application and database are integrated

Application and operation and maintenance personnel work together to solve application decoupling, database decoupling, debt collection and replenishment, business monitoring, application routing, fault switching, etc. Availability, efficiency, fault recovery and other aspects must all be involved.

20. Performance Improvements

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

Summarize

1. The most suitable architecture is one that combines software features and business scenarios while achieving a balance between cost and benefit;

2. In the case of big data, read-write separation and database and table sharding can be used, but the appropriate ones must be selected;

3. If it is not suitable for database sharding, you should consider making the core database as small as possible and then expand the capacity through vertical expansion;

4. Use various technologies, high availability and disaster recovery measures to ensure its availability.

The above is the detailed content of MySQL 20 high-performance architecture design principles (worth collecting). For more information about MySQL architecture design, please pay attention to other related articles on 123WORDPRESS.COM!