The effect of hardware environment on the performance of database system

The effect of hardware environment on system performance

The hardware environment of any one system will play a critical role in performance, which I think every reader friend is very clear. In the Environment of database application system, because of the characteristics of the database itself and the role in the system, he is the most difficult to expand in the whole 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, it is certain that many readers will want to know that the database server host parts of the hardware who is the most important, the impact of the overall performance of each part of the proportion of each, so as to be able to select the appropriate host model as a database host. But I can only regret to tell you that there is no law or law that can accurately give the answer.

Of course, you don't have to be too depressed. Although there is no law to know exactly how we can match the hardware of a host, but depending on the type of application, there is a general principle that can be consulted.
First of all, the database host is the place to access the data, then its IO operation is naturally not less, so the database host IO performance is definitely the most important factor to consider, regardless of what type of database application is applicable. However, the IO performance here is not just the physical disk IO, but the overall IO performance of the host, which is the overall IO performance of the host's entire IO system. The IO performance itself can be divided into two categories, one is the number of IO accesses per second, that is, the amount of IOPS we often say, and one is the total IO per second, which is what we often call IO throughput. Determining the IO Performance component in the host is primarily determined by disk and memory, and of course includes a variety of IO-related board cards. Second, because the database host and the ordinary application server compared to the relative concentration of resources, a single host on the need to do more natural computation, so the database host CPU processing capacity can not be ignored. Finally, because the database is responsible for the storage of data, and the amount of data passed in the interaction with each application is more than other kinds of servers, the performance of the network device of the database host may become the bottleneck of the system.

Because these three parts of the above are the main factors that affect the performance of the database host, other parts become a performance bottleneck is much smaller, so we can make a simple analysis of the various types of applications, and then give the three types of components of the basic selection recommendations.

1. Typical OLTP Application system
For all kinds of database system environment of the most common OLTP system, which is characterized by a large amount of concurrency, the overall data volume is more, but each access to less data, and access to the data is more discrete, active data accounted for the overall data is not too large. The database for this type of system is actually the most difficult to maintain, the most difficult to optimize, the overall performance requirements of the host is also the highest. Because he not only the traffic is very high, the amount of data is not small.
For these characteristics and analysis, we can get an approximate direction of the OLTP. Although the overall data volume of the system is large, but the proportion of the system active data in the total data is small, then we can expand
Large memory capacity to cache the active data to memory as much as possible, although IO access is very frequent, but the amount of data accessed per visit is small and discrete, then our requirement for disk storage is that IOPS performance is very good, throughput is a secondary factor;
Concurrency is very high, the CPU processing requests per second naturally a lot, so the CPU processing power needs to be strong; Although the amount of data per interaction with the client is not particularly large, the network interaction is very frequent, so the host interacts with the client
Network equipment is not too weak for traffic capacity.

2. Typical OLAP Application system
The main feature of OLAP system used for data analysis is that the data volume is very large, concurrent access is not many, but the amount of data that need to be retrieved each time accesses is more, and the data access is relatively centralized, there is not too obvious active data concept.
Based on the various characteristics of OLAP system and the corresponding analysis, the approximate strategy for the hardware optimization of the OLAP system is as follows: The volume of data is very large, so the disk storage system needs to be as large as possible, the volume of data is large, and the access data is more concentrated The performance requirements of the system need to be as large as possible IO throughput per second, so the disk with the largest throughput per second should be used, although the IO performance requirements are also relatively high, but the number of concurrent requests is less, so CPU processing power is more difficult to become a performance bottleneck, so CPU processing power is not too stringent requirements Although the number of accesses per request is very large, but most of the data in the execution is not returned to the client, the amount of data returned to the client is small, so the network device requirements for interacting with the client are not too high, and because the OLAP system can parallelize well because of its long process, the general OLAP systems are a cluster of multiple hosts, and the amount of data interaction between hosts and hosts in a cluster is generally very large, so the network equipment between hosts in the cluster is highly demanding.

3, in addition to the above two typical applications, there is a special kind of application system, their data volume is not particularly large, but the access request and its frequent, and most of the read request. It may require tens or even tens of thousands of of requests per second, and each request is very simple, and most likely only one or a few smaller records, such as a database-based DNS service, is a type of service. Although the amount of data is small, but the access is very frequent, so large memory can be used to cache most of the data, which can guarantee very high hit rate, disk IO is small, so the disk does not need to be particularly high performance, concurrent requests are very frequent, more than the need for strong CPU processing capacity to handle Although the application and database interaction is very large, but each interaction data is small, the overall traffic will be larger, but generally the average gigabit NIC is enough.

In many people's view, the fundamental determinant of performance is the quality of hardware performance. In practice, however, hardware performance can only have a fundamental impact on the system capability at some stage. When our CPU processing power is enough, the IO system is strong enough to handle, if our application architecture and business implementation is not optimized, a very simple implementation has to go around a lot of bend back and forth multiple times, then strong hardware is useless, because the back and forth of the interaction will always consume time. In particular, some business logic design is not particularly reasonable application, database Schema design is not reasonable, a task in the system is divided into a number of steps, each step uses a very complex query statement. I have encountered such a system, the system is to buy a well-known manufacturer of a project management software. The system was originally run on a Dell2950 PC server, and the user complained that the system was slow to respond, but I looked at the system and was busy from the state above the server (the system is not too large). Later, the user strongly requested to improve the system performance by replacing the hardware facilities, although I have been opposed, but finally in the management of the requirements, replaced by a Sun S880 minicomputer, the host CPU processing capacity of at least 3 times times the original machine, storage system from the original use of local disk to use the EMC The interrupt storage CX300. In the trial phase, the overall performance of the system was not improved, and the plan to replace the hardware was canceled.

Therefore, in the application of hardware configuration, we should be a rational perspective, only the right is the best. Not that the better the hardware resources, the better the system performance will be. Moreover, the hardware system itself always has an extension limit, if we blindly hope that by upgrading hardware performance to solve the system performance problems, then one day will encounter insurmountable bottlenecks.
At that time, even if there is more money to hit it is useless.

The effect of hardware environment on the performance of database system