Impact of hardware environment on system performance

Preface

All sections earlier in this chapter describe the impact of the software environment in the entire system on the system performance. In this section, we will analyze the impact on the database system from the system hardware environment, we also provide some targeted optimization suggestions from the perspective of database server hosts.

The hardware environment of any system plays a key role in performance. I think every reader and friend knows this very well. In the database application system environment, the characteristics of the database and the role in the system determine that the database is the most difficult to expand in the entire system. Therefore, in most environments, the performance of the database server host (or host cluster) largely determines the performance of the entire application system.

Since our Database Host resources are so important, many readers may want to know who is the most important hardware part of the database server host, the ratio of each part to the overall performance, so that the appropriate host model can be selected as the database host. However, I can only tell you with regret that no law or law can give this answer very accurately.

Of course, you don't have to be too frustrated. Although there is no rule to know exactly how to select the hardware of a host, depending on the application type, in general, there is still a principle that can be roughly followed for reference.

First, the Database Host is the place where data is accessed.IoNaturally, there are no fewer operations, so the Database HostIoPerformance must be a top priority factor, which is applicable to any type of database applications. HoweverIoPerformance is not just a physical diskIoBut the overall host.IoPerformance is the whole hostIoOverall systemIoPerformance. WhileIoPerformance itself can be divided into two categories, one is available per secondIoThe number of visits, which we often sayIopsQuantity, and the secondIoTotal traffic, which we often sayIoThroughput. Depends on the hostIoThe performance components are mainly determined by the disk and memory. Of course, they also includeIoRelated board.

Second, because of the Database Host and common applicationsProgramCompared with the server, the resources are relatively concentrated, and the computing workload on a single host is naturally large, so the Database HostCPUThe processing capability cannot be ignored.

Finally, because the database is responsible for data storage, the amount of data transmitted in the interaction with each application is more than that of other types of servers, therefore, the performance of the network devices of the Database Host may also become a bottleneck of the system.

The above three components are the most important factors affecting the performance of the database host, and the chances of other components becoming performance bottlenecks are much lower. So we will make a simple analysis on various types of applications, the basic selection suggestions for these three types of components are given in a targeted manner.

 

TypicalOLTPApplication System

For the most common database system environmentsOLTPThe system features a large concurrency and a large amount of data. However, the data accessed each time is relatively small, and the accessed data is relatively discrete. The proportion of active data to the total data is not too large. Databases of such systems are actually the most difficult to maintain and optimize, and have the highest overall performance requirements on hosts. Because he not only has a high access volume, but also has a small amount of data.

Based on the above features and analysis, we canOLTPTo obtain a general direction.

Although the total data volume of the system is large, but the proportion of the system's active data in the total data volume is not large, we can expand the memory capacity to make as many active data as possibleCacheTo memory;

AlthoughIoFrequent access, but the amount of data accessed each time is small and discrete, so our requirements for disk storage are:IopsPerformance should be good, and throughput is a secondary factor;

High concurrency,CPUThere are naturally many requests to be processed per second, soCPUProcessing capability needs to be strong;

Although the amount of data for each interaction with the client is not very large, but the network interaction is very frequent, so the network device for interaction between the host and the client must not be too weak in traffic capabilities.

 

TypicalOLAPApplication System

Used for data analysisOLAPThe main feature of the system is that the data volume is very large and there are not many concurrent accesses, but each access requires a large amount of data to be retrieved, and the data access is relatively concentrated, there is no obvious concept of active data.

Based onOLAPSystem features and corresponding analysis,OLAPThe general system hardware optimization policy is as follows:

The data volume is very large, so the unit capacity of the disk storage system should be as large as possible;

If the data volume for a single access is large and the access data is concentratedIoThe performance requirement of the system is to be as large as possible per second.IoThroughput, so the disk with the maximum throughput per second should be selected;

AlthoughIoHigh performance requirements, but few concurrent requests, soCPUProcessing capability is hard to become a performance bottleneck, soCPUThe processing capability is not demanding;

Although each request has a large access volume, most of the data in the execution process is not returned to the client, and the data returned to the client is small, therefore, the requirement for network devices to interact with clients is not too high;

In additionOLAPBecause the system has a long operation process and can be parallelOLAPThe system is a cluster composed of multiple hosts, and the data interaction between hosts in the cluster is generally very large, so the network equipment between hosts in the cluster is very high.

 

Special Application Systems

In addition to the two typical applications above, there is also a special type of application system. Their data volume is not very large, but the access requests are frequent, and most of them are read requests. It is possible that tens of thousands or even tens of thousands of requests are required per second. Each request is very simple. Most requests may only return one or several small records, such as database-basedDNSThis is the type of service.

Although the data size is small, but access is extremely frequent, it can be achieved through a large memoryCacheStore most of the data, which ensures a very high hit rate, diskIoTherefore, disks do not require high performance;

Highly concurrent requestsCPUProcessing capability can be processed;

Although the amount of interaction between applications and databases is very large, there is little interaction data each time, and the overall traffic is also large, but generally the General Gigabit Nic is enough.

 

In the opinion of many people, the fundamental determining factor of performance is the performance of hardware. But in fact, the hardware performance can only have a fundamental impact on the system performance in some stages. When ourCPUSufficient processing capability,IoWhen the processing capability of the system is strong enough, if our application architecture and business implementation are not optimized enough, a simple implementation would have to go around many corners and interact with each other multiple times, hardware is useless, because round-trip interaction always takes time. In particular, some business logic design is not particularly rational for applications, databasesSchemaThe design is not reasonable enough. A task is split into multiple steps in the system, and each step uses a very complexQueryStatement.

I have met such a system, which is a project management software of a well-known manufacturer. The system initially runs on oneDell2950OfPcserverThe user complained that the system response was slow, but the system was busy from the server status (the system concurrency was not too large ). Later, the user strongly urged to improve the system performance by replacing the hardware facilities. Although I have always opposed it, I finally changed it toSunOfS880Minicomputer, hostCPUAt least the processing capability of the original machine3More than double, the storage system also changed from the original use of local disks to useEMCInterrupt StorageCx300. In the trial phase, we found that the overall system performance was not improved, and finally the hardware replacement plan was canceled.

Therefore, we should look at the hardware configuration of the application system from a rational perspective. Only the right one is the best. It doesn't mean that the better the hardware resources, the better the system performance. Moreover, the hardware system itself always has an extension limit. If we blindly want to solve the system performance problem by upgrading the hardware performance, we will encounter insurmountable bottlenecks one day. At that time, even if there was more money, it would be useless.

Transferred from MySQL performance tuning and Architecture Design