Get through the MySQL architecture and business of the two-pulse

Currently, MySQL databases are widely used in many OLTP scenarios, and there are many different ways to use them. From the database business requirements, architecture design, operational maintenance, and then to the expansion of the migration, different MySQL architecture has different characteristics, adapt to a certain business scenario, or solve certain business problems.

DBA as a database architecture design, implementation, maintenance personnel, not only to a variety of MySQL architecture is very familiar with, but also to understand the business, for different businesses have a certain division and understanding, and according to business characteristics and architectural characteristics, reasonable choice and use of MySQL to meet business needs.

This article from the MySQL Common architecture, business environment classification, business and architecture with the use of principles three aspects of the MySQL database and business scenarios to explore and explain, let us first of all the MySQL architecture and business classification to understand, and then connect them together, Enables Gangjumuzhang in the design of business and MySQL architectures, enabling users to solve their business needs with the right technology.

I. MySQL database common architecture

In order to have a clear understanding of the common architecture of MySQL database, the following is a description of three aspects of MySQL three common infrastructure, five special requirements architecture, architecture combination and comprehensive use.

1. three common infrastructure of MySQL

(1) MySQL Single instance architecture

MySQL Single instance, is the deployment of a MySQL instance on the server to provide services externally, this is the first contact with the MySQL database will be used in the way, but also common learning, study the use of MySQL database.

MySQL single-instance usage is the first stage of MySQL database use, usually in this case, the MySQL database and the application will be on the same server.

The main benefit of this approach is that it is easy to deploy and use, either directly by compiling the installation, or by unpacking the binaries, and quickly having a MySQL database environment that can be used. At the same time, this approach, less dependency, no need to rely on other third-party tools or software, maintenance and fault location is also relatively easy.

Familiarity and mastery of MySQL single-instance environment skills is also the foundation for maintaining other MySQL architectures.

It is important to note that MySQL Single instance can be used in the learning and development environment, but the availability and disaster preparedness of this method is weak, if the database used as a business system, try not to use this way.

(2) MySQL Master-slave master-Slave architecture

MySQL master-slave master-slave environment, is based on the MySQL single instance environment, the MySQL for a full library backup, and then recover one or more MySQL instance, through the Change Master command, specify the newly recovered MySQL instance, Read the change log from that MySQL node and apply it locally so that the newly recovered instance is consistent with the original MySQL instance data.

Therefore, the original data consistent changes in the instance, called Master Master node, from the master node to get the log, and in the local application, so that the data and the master phase to maintain a consistent node, called Slave from the node; Such an architectural environment is called Master-slave Master-slave environment.

The Master-slave master-slave environment is a very characteristic feature of MySQL database and a common architecture for MySQL database application in production environment.

Through the Master-slave architecture, we can make the data of the online database have many copies, and play a certain data backup function. Slave from the library data changes only through the application log implementation, generally do not actively generate write data, but can provide external data read services, so by adding a few slave from the library, so that only the data read business to slave from the library query, can greatly improve the business read performance and throughput. In the Internet industry, very much data read operation is much higher than the data write operation of the business scenario, through the master master node to write data, read data on the slave node, such a read-write separation of the architecture, can well meet business needs.

Of course, MySQL master-slave master-slave architecture, the implementation can also be very flexible, 1 master master node, can have 1 or more slave from the node, and a Slave node can also be used as the slave node of other nodes, If a slave node is followed by another node as the node's slave from the node, it is called cascade replication.

MySQL's master-save architecture, based on the MySQL single-instance architecture, improves the performance, availability, and scalability of MySQL databases, while also providing the basic guarantee for MySQL databases to pursue higher availability.

(3) MySQL MHA High-availability architecture

Although the MySQL database master-salve master-slave architecture, so that the database has many copies, but these maste master node and slave from the node, is still relatively independent, especially if the master master node fails, still can not provide database services. In order to achieve a higher availability of the database for a variety of failures and special situations, higher availability can be achieved through other components on a master-slave basis.

MySQL High-availability scheme is more, but the current more mainstream, more mature scenarios, or MySQL + MHA high-availability architecture.

Simply put, in order to achieve higher availability, it is necessary to master-slave the master-slave environment on the basis of the business connection master IP, the master host of the actual IP, into a virtual VIP or domain name. The application accesses the database through the VIP, reads and writes the data, and under normal circumstances, the business reads and writes on master; if the master node fails, the high-availability component detects the failure and switches the VIP to slave from the library. At the same time for the slave from the library log transfer and application, to ensure that the data on the slave, and the master node before the failure of the data as far as possible, so that the new slave node can still provide external database services.

Of course, for the specific implementation, in the MySQL master-slave master-slave structure, VIP and database log leveling scheme also has a variety of implementations, can also be a variety of in-depth customization, and even some companies do not use open source software, directly developed to achieve the various functions of high-availability components.

Currently, MySQL + MHA is a highly available implementation that is more mainstream and mature, and there may be some other, more sophisticated, high-availability implementations, but all can be categorized into the scope of achieving increased availability.

Summary

Here we go. mysql single-instance architecture, Mysqlmaster-slave master-slave architecture, MYSQL+MHA high-availability architecture, these three infrastructures can be well met for the various versatility requirements of MySQL database, in other words, This is the three basic architectures that must be used and mastered in the MySQL database architecture.

2. Five special business requirements architecture

With these three common infrastructures in MySQL, the vast majority of MySQL database scenarios and problems can be well met and resolved. However, some special scenarios, or some special problems, can also be used in databases other than MySQL database, a specific class or a few types of problems solution. For these special business requirements, I'll start by describing and explaining the issues to be solved before proposing the corresponding solution.

(1) MySQL + distributed proxy level extension Architecture

problem: If the scale of business further expanded, read and write magnitude, especially write magnitude reached a very large level, such as data write hundreds of thousands of per second, or even millions of, the daily data volume of hundreds of millions of or even billions of of the scale, so read and write is far from a master node can be supported, Then you have to expand it.

In general, the MySQL extension can be divided into: vertical expansion according to different business vertical splitting, and through a certain sub-library table strategy implemented by the level of library tables two ways. These two methods can be used alone or in combination with each other. But if you are solving large amounts of data and high concurrency, the main way is to extend MySQL horizontally.

the idea of MySQL level expansion

Database and table tables on a single server are always constrained by the resources of one server, even if the hardware of the server reaches top with, there are still bottlenecks. For business access, if there is a proxy layer or the middleware layer of a database and a table, through the proxy layer according to a certain rule mapping and transformation, to the underlying multiple database and multiple tables on multiple servers, This is equivalent to multiple servers supporting a business together, the capacity to support is related to the number of underlying servers. In the absence of a proxy proxy to the bottleneck, the higher the number of underlying servers, the greater the performance and capacity of the entire scale-out cluster, which can scale almost linearly. This way of thinking can solve a lot of data storage and concurrency problems.

In particular, the horizontal expansion of the cluster in addition to the MySQL database, the need for a distributed proxy middleware, this level of expansion of the middleware type is also more, MySQL official and some large companies have developed. We use more of the MYCAT middleware. For the underlying shards, there can be dozens of, hundreds of, or even thousands of.

Of course, the horizontal expansion can solve the problem of large data volume, need to have a shard strategy, and correspondingly, there will be a restriction on the use of this strategy, such as chip key selection, cross-node access, distributed transactions and other issues, need to be aligned with the business and consider, before it can be used well.

(2) Tokudb/myrocks/innodb High Performance write architecture

Questions: MySQL database horizontal split, can be extended to read and write large data volumes, but the corresponding number of the underlying server needs to be more, but for the data write is very large, the data read very little, the large amount of data, the use of high-performance writing architecture is more appropriate.

The volume of business data writes is very large, the volume of reading is very high, generally mainly to the data insert write performance, at the same time, the data compression efficiency is particularly high requirements. This special writing requirement requires special optimization and design of data writing, and has better compression efficiency and algorithm, which can compress the large amount of data written and save space. This write architecture can often be seen as a special storage engine for MySQL databases.

specifically to the implementation, MySQL's high-performance write cluster can use the TOKUDB storage engine. In recent years, Facebook has also open up its internally implemented Myrocks, which can be used as a storage engine for high-performance writing. MySQL default InnoDB storage engine, after the new 5.7 and later version optimization, write performance and compression performance also have higher performance, also can be used as a choice of data writing.

(3) MySQL + cache (Memcached, Redis, etc.) high concurrent read architecture

problem: If the business accesses some small amount of hot data in MySQL, the concurrency of access is very high, the timeliness of access, the data consistency requirements are very high, this time using the MySQL database itself to support the data read, may be concurrency, timeliness bottlenecks. At this point, you can use the caching system in conjunction with MySQL.

The cache system is to store a small amount of hot data in the MySQL database into memory, because the memory IO efficiency is much higher than the hard disk IO, the corresponding CPU consumption will be much less, so that the cache system in response to a business request time, will be much less than the time required to access the MySQL database directly. Therefore, the cache system can support high-concurrency access to hot data, write data to the MySQL database, read the data, prioritize the cache, and return the results directly from the cache if it is not in the cache, and then return the data from the MySQL database to the application if it is not. And then put the data results into the cached content.

Specifically, the technical architecture used by the cache system is memcached and Redis. Memcached is a classic caching system, used in conjunction with lamp, LNMP popular architecture. Redis is a new Key-value key-valued NoSQL database for several years, and it can be persisted as a Key-value database in addition to being cached.

(4) MySQL + Small File system (MongoDB, Ceph, etc.) Large segment access architecture

problem: in MySQL database, it is usually the small-size segment that conforms to the principle of relational database, such as numeric type, character data, but in the actual environment, in addition to these characters commonly used segments, there will be some large fields, such as the user picture file, uploaded audio, video files, Post content such as large text fields, there are also some JSON files, XML files, and so on, which can be stored in binary form in the MySQL database, but read and management will be more troublesome. At this point, you can use the small file system to combine MySQL.

A small file system is a system that can store and quickly access structured data. For pictures, audio, video, txt files, JSON files, XML files and other large fields, generally only a simple read and write operations, the fields are stored in the small file system, and the corresponding access links to the table in the MySQL database. Through the database table, the file location information can be read and written quickly, in the small file system, through the file location information, can achieve the rapid read and write access to large segments.

In particular, the small file system also has a lot of technical software, more common mongodb document-type NoSQL database, Ceph distributed small file system.

(5) MySQL + inforbright/greenplum statistical analysis architecture

problem: Real-time response to a business-required query on a MySQL database usually refers to an OLTP business, but for data that has already been generated, there is usually a result summary and statistical analysis required after the next day. Such OLAP requirements are typically less frequent, but each execution consumes a lot of resources and, if run on a system like OLTP, can cause interaction between these two categories of business. At this point, you can use the MySQL database and OLAP statistics business classification of the architecture.

After MySQL has generated business data, it usually takes the next day to make a statistical, aggregated, and analytical analysis of the data from the previous days, every angle, each dimension, to embody and reflect the operational situation of the business. This is to let MySQL support the online OLTP business, through the data Flow program, the daily production of data flow to the offline Data Warehouse system, in the Data Warehouse system, a variety of statistical analysis of data, the results are aggregated, and the results of data transfer to the results of the display library. This makes it possible to combine use and execution of online OLTP and offline OLAP.

Specifically, for the MySQL database can be combined with the OLAP Data Warehouse architecture, you can choose Inforbright Data Warehouse, you can also choose Greenplum distributed MPP database warehouse. Relatively speaking, inforbright Data Warehouse is relatively lightweight, similar to MySQL use; Greenplum distributed MPP Data Warehouse can support the statistical analysis of massive data, function, performance, capacity and so on is more powerful than inforbright, the cost is also greater.

Summary

MySQL Five special business architecture, can be said in MySQL three common, general-purpose architecture on the basis of the face of special business scenarios, encountered special problems, targeted solutions.

For a large number of data read and write, you can use a horizontal expansion of the architecture, for a large number of data write requirements, you can use the MySQL high-performance write architecture, for hot data high concurrency, fast response needs, can adopt mysql+ cache architecture, for special large-scale access requirements, can be used mysql+ Small file system architecture; For offline statistical analysis, the mysql+ statistical analysis architecture can be used.

3. architecture mix and synthesis

MySQL three more common infrastructure and five special requirements architecture, can be used according to the scene alone, or according to a specific scenario can be combined with several architectures, or integrated together.

(1) Architecture combination

For architectures with only one or two special cases, a simple combination of infrastructure and special architectures is available, and the types of architectures that can be used in a production environment are:

• MYSQL+MHA high-availability architecture combined with MySQL distributed proxy horizontal extension architecture

• MYSQL+MHA High-availability architecture and MySQL small file system large segment Access architecture combination

• MYSQL+MHA high-availability architecture combined with MySQL cache high concurrent Read architecture

• MySQL distributed proxy horizontal extension architecture and MySQL small file system large segment Access architecture combination

• MySQL distributed proxy horizontal extension architecture combined with MySQL cache high concurrent Read architecture

• MySQL High performance write architecture combined with MySQL inforbright/greenplum statistical analysis architecture

(2) Architecture synthesis

In the case of more complex business scenarios, several special database architectures can be combined to use:

MYSQL+MHA High-availability architecture, MySQL distributed proxy horizontal extension architecture, MySQL cache high concurrency read architecture, MySQL small file system large segment access architecture, MySQL inforbright/greenplum statistical analysis architecture.

II. Classification of business environment

The first part describes the MySQL architecture, which is an understanding of the MySQL database itself and is counted as a "confidant." All database systems provide services to the objects are business systems, so the DBA to understand the business system, the characteristics of the business and the appropriate scenario, to be aware of, can be counted as "know." To be able to gain the best of both worlds.

1, using the deduction data from the database use classification

From the database operation point of view, business system for database operation, large aspects can be divided into "read data" and "write data" two categories. To expand, write data can be specifically divided into Insert data, update modification data, delete deleted data three cases. Therefore, the use of the database can also be subdivided into insert, delete delete, change update, check select four cases.

Business systems can be categorized according to the operational classification of the data by the business system:

(1) Read-only business system

Read-only refers to the case that only query select, no data modification. In this case, there are already some data in the database, and this data is only for query or display, there will be no data changes, such as the data in the cache, archived data, historical results data, statistical data, etc., are only to query and display, no further changes in the data.

(2) can read and write business system

In the case of write operations, there are three types of read-write business systems:

• Read-write business system with insert only, no update and delete

This situation refers to the data table data will only increase, and the data in the table can no longer change, no further modification or deletion operation, such as Operation Record table, status change record table, message record table, etc.

• Can read and write business systems with insert and update, without delete

This situation refers to data in the data table, can be added and modified, but once the data is generated, can change, but cannot be modified. This is also a common idea of database design, that is, the data table can be invalid, but the effective deletion, not really deleted from the data table, but modified the table represents the status bit of the column values, so that the data has always been traceable.

• Read-write business system for Insert, Update, delete

This situation refers to the data table data can be carried out a variety of operations, can be queried, can also make a variety of changes, add and remove all kinds of operations can also be done.

2. Common Business Table classification

Classify tables from a business perspective, although different applications use different tables, they can be abstracted into several large types of tables.

(1) Configuration table

This table usually holds some basic configuration information or dictionary information for the business. The amount of data in a table is generally small, and changes are not frequently changed, typically a select query operation.

(2) Status table

This kind of table usually holds the state information of the entity read image in the business system, the common user Information table, the Order Information table and so on. The amount of data in this table is directly related to the size of the physical image, such as how many registered users an app has, and how many records the user table of the app usually has. The change in the state table is usually frequent, and the Insert, Update, select operations are always available, and the delete operation is usually determined according to the business conditions.

(3) Log table

This kind of table is usually used to record the state information of some entity in the business system, such as user login form, Recharge information record table, etc. The data size of this kind of table is usually big, and if the business state changes frequently, the record change information is more, this kind of table data volume and insert performance are required relatively high. The operation of the log table is usually based on the insert operation, and the individual business queries the log table. The high-performance write architecture in MySQL's Five special requirements architecture is primarily the need to apply such a table.

(4) Filing table

This table is a table that archives data from the above three OLTP business tables or separates hot and cold. Data archiving, hot and cold separation of the three types of online business tables, on the one hand, can control the data size of the online business table, ensure the performance of the business table, on the other hand, after archiving, can be used to better query the archive historical data to reflect and support. The size of the data in the archive table is related to the corresponding Line table size and archive period. The operation of the archive table, in addition to the data loading of the archive process, is mainly a select query operation, after the archive is considered read-only table.

(5) Statistical data sheet

The Statistical data table, refers to the business has the offline statistic analysis demand, needs the various online table and the archive table data, passes through the ETL process to the online OLAP statistical analysis system the original data table. Such tables typically have very large data volumes, and an OLAP analytics platform summarizes data from multiple online business systems for statistical analysis. The operation of statistical data table, in addition to data flow action, is mainly a variety of statistical analysis program Access calculation.

(6) Statistical Results table

The statistic result table is the data in the Statistic data table when the business has the demand of the offline statistic analysis, and the statistic analysis result data according to certain logic. This statistic results in a small amount of data. The operation of the statistic result table, the processing result flow action, is mainly for the Access interface to select query.

By combing the types of business tables, you can make a general division of all business systems and be aware of them.

3. DBA's grasp of the business

By dividing the business system into four classes by using the data, and then dividing the business common table type, you can have an overall understanding of the common business Usage database. But for specific business scenarios, it is also necessary to identify and consider the specific realities of each company.

In most cases, a specific business can be classified according to the circumstances of a business type, but each business specific magnitude will be different, you need to understand the specific business environment of the magnitude, and then according to the business type and MySQL schema usage, the corresponding can be.

If there are instances in the actual environment that are not in the existing classification, then the new Type Division and schema correspondence can be made through the existing ideas.

third, MySQL business and architecture in combination with the use of Principles

The above two sections explain the MySQL architecture and the business environment by classifying it, so that you can have a certain understanding of the MySQL schema and the business environment itself. I will explain the use of the MySQL business and architecture in conjunction with the principles of both architecture design and operations.

1. Applicability Principle

The applicability principle is that when considering which or several business scenarios to use for a particular business scenario, we try to use the appropriate technical architecture to solve the right problem.

(1) Requirements and scenarios

MySQL's three general-purpose infrastructures are available in a number of scenarios. However, the general business scenario in the data magnitude, access scale, read-write mode and so on a relatively large change, it becomes a special requirement of the scene, you can consider the use of a specific scenario corresponding to the MySQL architecture technology, as far as possible to ensure applicability.

Conversely, if the actual business in the magnitude, scale, read and write mode has not yet reached a very special scene, as far as possible to use a common infrastructure to meet business needs, but also reduce the complexity of the system and hidden dangers.

(2) Overall and part

Both a business system and a MySQL database architecture are viewed and considered from both a holistic and a partial perspective.

A business system, the first is a whole, from the overall view of the various business needs and usage, grasp the overall, and then consider the specific needs, if there is no special requirements, you can follow the general scenario to design and consider, if a part has special requirements, you can separate this part of the content, For the corresponding architecture design.

A number of common and special architectures that are combined to achieve a framework that supports the business system as a whole.

(3) Stability and upgrade

In general, business systems use a common architecture for data support, and business systems can run stably when a common architecture is applied. In the continuous operation of business systems, when new business scenarios arise, consider the steps and stages of ensuring existing business stability and upgrading business systems to new architectures.

Generally do not upgrade all of a sudden, it is recommended to use the first Test, then go online, sub-batch transition and upgrade the way.

2. Phased principle

The development of the business system is stage, and the development of MySQL database architecture is also stage. Different stages of the information and main processing ideas are different, considering the phases from different dimensions is also the use of architecture and business important principles.

(1) Quantity phase

Quantity is a more obvious stage judgment indicator. Business systems usually have dau, UV, PV and other indicators to help determine the size of the business system. Database system, QPS, TPS, the amount of data in a table, the number of tables under a library, the number of libraries under one instance, the total number of instances, the number of servers, are all closely related to the architecture.

Example of the amount of data in a table: If a table runs for one year and the number is below 100,000, it can be considered as a small table with a data volume of more than 100,000-10 million, which can be considered as a table, and the amount of data above 10 million can be considered as a large table, which requires consideration of archiving or horizontal splitting; It has to be handled separately with a special architecture.

(2) Unified organization

When the size of the business and the size of the data are relatively small, if there are many different architectures, can be maintained. However, if the number of DB instances and the business modules are large, it is important to unify one or a few data architectures. A unified architecture organization that makes business systems and architectures easier to control and maintain.

(3) Scale control

Business to a certain scale, the underlying structure of the database must be to control the scale, an instance can not be too large, a table can not be too large, if the size of the agreed-upon scale, the need for strength splitting, or table splitting, so that the instance and library tables are maintained in a unified set of scale.

3. The principle of extensibility

As the application business changes over time, the underlying MySQL architecture also needs to be scalable as the business changes, maintaining the scope for change and expansion to support business growth.

(1) The structure between the open

As can be seen from the three infrastructure of MySQL, MySQL Single instance architecture →mysql Master-slave Master-slave architecture →mysql MHA High-availability architecture, which is gradually evolving and has a direct dependency. Subsequent Oracle's INNODB cluster architecture also has a direct evolutionary relationship with these infrastructures.

The architecture of the other five special needs, as the business classification changes, special circumstances may also change, depending on these changes from a special framework to adapt to become another special architecture.

(2) OLTP and OLAP

Database system is generally divided into two categories of OLTP and OLAP, but in the current business system and architecture design, both of these requirements are needed to support. As long as the establishment of a more stable and reliable data flow system, the two can be easily realized, OLTP and OLAP interoperability, OLTP business data transfer to OLAP statistics, OLAP statistics, and then back to the OLTP for display.

(3) Use of new architectures

In addition to the common three infrastructure and five special architectures, there are new technologies and trends to try to refine and solve some of the problems of existing architectures, such as InnoDB cluster, which can be better addressed for MySQL extensions and high availability.

While these new technologies are not yet fully stable and mature, they can easily be expanded into new technology architectures after the subsequent new technology architectures are stable and can be used to better solve business problems.

PostScript

This article attempts to explain the MySQL architecture and business from the MySQL architecture, business environment classification, MySQL business and architecture in three ways, in the hope of architecting and business understanding from an architectural perspective, to face and solve a variety of business problems in a deeper, comprehend by analogy way. Some of these specific details that are not very relevant to the architecture are not fully expanded in this article and will be explained later in the article.

This article was reproduced in : https://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=2650765228&idx=1&sn= c9122f974413c28a7499df2089ed7e4d&chksm= f3f9c239c48e4b2f448f93964dd6c5cd91a45281f48a91df35e89d38c891f46176ea2689e3a4&mpshare=1&scene=1& Srcid=0122xmrarstrgfpaok9jgixi#rd

Get through the MySQL architecture and business of the two-pulse