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

As a DBA, it is sometimes challenged with questions like this:

If the scale of the existing business increases by 10 times or 100 times, can the database support it? We'll make a big push next month. Is there a problem with the database? Plan to do the O work, the code logic remains the same, can the database switch from Oracle to MySQL,MySQL support the business? Server purchase and selection, which server is more suitable for us?

In the face of questions such as the above, how should DBA face it?

As a DBA, how should I evaluate the use of existing resources?

If the existing database resources are really unsustainable, what principles should be followed to transform them?

This article is a summary of some experiences in view of the above problems for your reference.

I. Evaluation work

In the face of such problems, the first thing to do is to carry out an assessment, which can be followed by the following steps:

1. Establish a performance baseline

According to the running status of the system, the performance baseline is established. Establish a corresponding relationship between business indicators and performance indicators. The performance indicators mentioned here include CPU, MEM, DISK, NET and so on. Among many resources, there must be an imbalance, and the shortage of resources is most likely to become the bottleneck after business growth. In the specific operation, we can first determine a business peak period, and collect the use of various resources of the system through the monitoring platform or monitoring tools. Then, based on the collected information, analyze where the possible performance deficiency is.

For DBA, you should be aware of the performance usage of the system you are in charge of. Through the understanding of the business and mapping the business indicators to the performance indicators, it is easy to infer that there is a maximum amount of business that the system can carry. In addition, there will also be a clear understanding of the shortcomings that may affect the growth of the bearer business.

Generally speaking, the application of database class is the application of resource consumption class. There is a large consumption of CPU, MEM, DISK, NET and so on. However, due to the uneven development level of different hardware, the resource consumption characteristics of different databases are also different, so specific problems need to be analyzed.

Let's talk about my personal views on hardware development and its relationship with databases:

CPU

Compared with other hardware, CPU technology develops rapidly. With the improvement of CPU main frequency and the development of multi-core CPU technology, the computing power provided by CPU will not become the performance bottleneck of the system. However, we need to note that some databases cannot fully take advantage of the power of CPU (for example, MySQL is like this). At this time, in order to make full use of the resources of CPU, we can consider a scheme such as "mixed running of multiple instances" to improve the utilization of CPU.

MEM

With the development of memory technology, the price of memory is getting cheaper and cheaper. Now we can see 128C, 256GB, and even TB-level memory in a production environment. Generally speaking, the database usually uses memory as a buffer, and the configuration of large memory can significantly improve the performance of the database. In addition, the technology of the database itself is also adapting to the scenario of large memory, and the usual strategy is to divide subpools. Subdivide the managed units further, such as Sub Pool in Oracle and multi-instance buffer pool in MySQL.

NET

With the rapid development of GigE, 10GbE and InfiniBand technology, the quality of service with low latency and high bandwidth has brought many changes to the database and even the whole IT system. Common application areas are:

Accelerate distributed databases, such as Oracle RAC.

Speed up big data processing, such as improving Hadoop MapReduce processing.

The change of storage architecture, from Scale-Up to Scale-Out.

Disaster recovery plan, active and standby strategy. DISK

Compared with the development of other hardware technology, the development of traditional mechanical disk is relatively the slowest, and it is often the most likely to become the performance bottleneck of database. With the emergence of flash memory technology, it brings a kind of change to storage technology. Let's take a look at the comparison of the main performance indicators:

From the above indicators, after the use of flash memory technology, the storage capacity is greatly improved and the biggest bottleneck of the system is eliminated. This is why many DBA strongly recommend the use of flash memory on different occasions, which will bring about a qualitative leap in the improvement of database performance. But at the same time, we should also note that the traditional relational database is designed according to the disk IO model, does not take into account the flash technology, and now belongs to the stage when the software lags behind the hardware; relatively speaking, the flash technology has more advantages for the non-relational model.

Many optimization theories based on traditional design have changed, such as the problem of index clustering factor. This is the main point we need to pay attention to when considering database optimization. In addition, the performance advantage of NoSQL is not obvious because traditional databases are combined with flash technology. It needs to be analyzed when choosing the architecture.

2. Establish a business stress model

According to the business characteristics, the business pressure model is established. The simple understanding is to abstract the business simulation to facilitate stress magnification testing later. To do this, you need to have a full understanding and evaluation of the business.

The following is illustrated by a small example:

This table simulates the business of a certain type of e-commerce, including the main modules and the main operations in the modules. Transaction complexity varies according to different operations (transaction complexity can be understood as the number of SQL statements executed). According to different reading and writing conditions, distinguish between data reading and data writing. When the total volume of business (transaction volume) is estimated, it is easy to calculate the amount of data operations. In this way, the business stress model is transformed into a data stress model. The difficulty here lies in the abstract ability of business logic and the proportional evaluation of module business volume.

With the table at a glance above, you can refine the operations for each business operation. Finally, it is abstracted into SQL statements and corresponding access characteristics. Its pseudo code can be described as

The stress test code can be compiled according to the above pseudo code. Call the test code through some tools, creating the pressure to simulate the test. For example, oradbtest/mydbtest (formerly a testing tool of Ali Lou Fang Xin) or sysbench, which I often use, are good stress testing tools.

It is suggested that enterprises should sort out their own business stress model according to their own situation. This is very useful in many occasions, such as system transformation, upgrade, expansion evaluation, new hardware selection and so on. It is more meaningful than the similar TPCC test report provided by the manufacturer. As far as I know, many larger companies have more mature stress models.

3. Simulated pressure test

The best way to examine whether the existing database can support the growing business pressure is to simulate stress testing. Observe the performance of the database under near-real pressure. Focus on the changes of the bearing capacity of the database, the main performance bottlenecks and so on. There are usually two ways, one is to divert flow from the real environment (and the flow can be magnified as needed, using tools such as TCPCOPY), and the other is to simulate stress through pressure tools based on the business stress model sorted out earlier. The former is suitable for capacity expansion evaluation and system transformation evaluation of existing projects, while the latter is suitable for scenarios such as prototype scheme evaluation and performance benchmark testing of new projects.

The performance bottleneck exposed in the above simulated pressure test results is the direction that we need to focus on improvement and optimization.

Second, optimize the levels and steps

According to the above evaluation results, to determine the following improvement, optimization scheme. Some steps can be followed:

1. Analyze the bottleneck point

According to the above evaluation results, the performance bottleneck is analyzed. According to different bottleneck points, different strategies can be adopted. Sometimes performance testing is full-process, and it is difficult for a complex system to clearly locate the performance bottleneck. At this point, some APM tools can be used to quantify the entire access path to help find bottlenecks. Can also be similar to the above practice, do a good job of abstraction, only exert pressure on the database side, observe the behavior of the database, and determine whether the database is a bottleneck. If it is judged that the carrying capacity of the database is not enough, it can be considered according to different levels.

This step is the most complex and difficult part of the overall assessment of database hosting capacity. It is important to distinguish whether it is due to insufficient database hosting capacity or other components. Even if it is clearly the problem of the database, it is necessary to distinguish whether it is the local problem of the overall or; whether the single business function is slow or the whole is slow; whether it is slow occasionally or always, and so on. The definition of these problems will help to clarify the level of the problem and adopt different strategies to solve it.

In view of the lack of database carrying capacity, I divide the common problems into levels, which can be simply divided into statement level, object level, database level, database architecture level, application architecture level and business architecture level. Different levels take different approaches, which are described below.

2. Level-statement level

For example, the core problem of performance is only the problem of a SQL statement, which can be optimized pertinently. This method is a less invasive optimization way, and its scope of influence is also relatively small. The following compares common statement-level optimization methods. To be clear, the following method has ruled out other factors such as inaccurate statistics, only in terms of the way the SQL statement itself is optimized.

Rewrite SQL

By rewriting the statement, the execution plan can be adjusted and the operation efficiency can be improved. The disadvantage of this approach is that developers need to modify the original code before going online. In addition, some SQL generated by the Oamp R Mapping tool cannot modify statements directly or use this method.

Use Hint

Many databases provide the function of Hint. In this way, the execution process of the statement is specified. This approach also requires modifying the source code and going through the process of deployment. In addition, this modification method also has the problem of poor adaptability. Because it specifies a unique execution process, a solidified execution process may not be the best way with the change of data size and data characteristics. This approach actually abandons the optimal path that the optimizer may produce.

Storage Summary, SQL profile, Plan baseline

There are also built-in features in Oracle that can solidify the way a statement is executed, which is essentially similar to using Hint above. Its disadvantages are similar to those above.

Adjust parameters

Sometimes you can change the execution plan of a statement by adjusting some parameters. But this method should pay attention to the scope of application, do not use it globally, to avoid affecting more statements. The scope of use at the session level should also be controlled to avoid having a greater impact.

3. Level-object level

For example, the core problem of performance cannot be solved at the SQL level, and the adjustment at the object level needs to be considered. This situation should be more cautious, and the possible risks and benefits need to be fully assessed. The structural modification of an object can involve hundreds or even thousands of execution plan changes related to this statement. Without adequate testing, it is difficult to ensure that there are no problems. If it is an Oracle database, consider using SPA to evaluate it. For other databases, you can collect the relevant statements manually in advance, replay the above statements after simulation modification, and evaluate the performance changes.

1) influencing factors

When making adjustments at the object level, you need to consider other factors in addition to the performance impact on other statements. Common ones are as follows:

Database maintenance cost

Common ones, such as indexes. By adding indexes, you can often speed up the query, but increasing the index will lead to an increase in the cost of data DML.

Operation and maintenance cost

Common ones, such as global partition indexes. After the partition maintenance action, the global partition index will cause the index to fail, and it is necessary to maintain the index automatically or manually.

Storage cost

A common index, the index structure is the real space-occupying structure in the database. In some previous cases, there have even been cases where the total size of the index exceeds the table size, so its space usage should be evaluated when it is added.

2) Lifecycle management

There is another very important concept-"object lifecycle management", which simply means the birth, aging, sickness and death of the object. In many systems, the object starts from new construction, and the data continues to increase and expand. When the data scale reaches a certain order of magnitude, a variety of performance problems appear. The query performance of a million-level table can certainly not be compared with that of a multi-billion-level table. Therefore, in the early stage of object design, the relevant archiving, cleaning, dumping, and compression strategies should be considered, and the evaluation of storage space should be considered together with life cycle management.

Many performance problems are easily solved after data cleaning. However, data cleanup often comes at a cost, and this issue must be considered at the beginning of the design. When doing database review, in addition to the regular structure review, statement review, we should also consider this part of the factor.

4. Hierarchy-database level

At this level, the problem is often more serious. In general, the initial configuration of the database is based on the type of load on which the system is running. If there is a performance problem after running for a period of time, which is assessed as an overall problem, you can consider adjusting the database level. However, this configuration is often costly, such as the need for special downtime window operation and so on. And the risk of this operation is also relatively large, it may bring a lot of uncertain factors, so we should be careful and careful.

5. Hierarchy-database architecture level

If the performance core issues cannot be solved at the above level, you may need to adjust the database architecture. Common, such as the use of read-write separation of access, sub-database sub-table storage and so on. This is very intrusive to the application, and in some cases even as good as refactoring the entire system.

For example, with the development of business, the amount of data or access to the system exceeds expectations, and the space or performance requirements can not be met through a single database. At this point, you may need to consider adopting a sub-database and sub-table strategy to meet this part of the requirements. However, it is often more difficult to transform than to redevelop a system.

For example, we may need a data middle layer to shield the details of the subsequent subdatabase and table. This middle layer may need to complete a series of functions, such as statement parsing, access routing, data aggregation, transaction processing and so on. Even if the middle-tier products are used, the function of the database will be relatively "weakened" for the application, and the application-level code has to make a lot of adjustments to adapt to this change. In addition, how to move a running system to the new structure smoothly is undoubtedly another problem of changing tires for speeding sports cars and so on.

If the project is in operation, there is a database schema-level adjustment, which is likely to indicate a mistake in the early project design and planning stage, or a deviation in the business expectations of the project. Therefore, these two points must be fully evaluated at the initial stage and remain fully "flexible" in design.

6. Hierarchy-Application Architecture level

In some cases, it is impossible to rely on the database alone, and the whole application architecture needs to be considered comprehensively. In the whole system architecture, the database is often at the end of the system, and its expansibility is the worst. Therefore, in the early stage of application architecture design, the design should be based on the principle of not putting pressure on the database as much as possible. Or even if there is great pressure, the system can take automatic degradation and other ways to ensure the smooth operation of the database.

Common examples are adding cache, clipping peak and filling valley through MQ and so on. By increasing the cache, the access pressure to the database can be greatly reduced and the throughput capacity of the whole system can be improved. With the introduction of MQ, the pressure on the database can be continuously exerted on the database in the form of "steady state" without being crushed to death by an abnormal peak.

7. Hierarchy-Business Architecture level

The last case is to make some adjustments from a business perspective. This is often a compromise to ensure the overall operation of the system by making appropriate subtraction. It doesn't even rule out sacrificing part of the user experience to satisfy the usability of most users. This requires our architects to have a clear understanding of the capabilities that the system can provide and a full understanding of the business. Only when we have a full understanding of what kind of business to carry and the cost of carrying the business, we can make some choices.

Here, we should avoid some misunderstandings and think that technology is "omnipotent". Technology can solve some problems, but not all problems, or the cost of solving all problems is unacceptable. At this time, the result of "taking a step back" can be achieved by making a slight adjustment from the business point of view.

Extended reading: homemade gadgets greatly speed up MySQL SQL sentence optimization (with source code)

A comprehensive analysis of the classification, discovery and optimization of Oracle waiting events

Step by step interpretation of Oracle AWR performance Analysis report

SQL optimization: an article explains the correct posture of Oracle Hint

Performance Optimization weapon: selection and practice of Database Audit platform Themis

Author: Han Feng

Source: Yixin Institute of Technology

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.