In the past communication with many people, we found that when talking about the hardware-based database performance bottleneck analysis, it is often misunderstood that the use of more powerful hosts or storage to replace existing devices.
I personally think that there may be a very large misunderstanding. When we talk about hardware-based optimization, we should not just divide the hardware used by the database into two parts: Host and storage, but further break down the hardware, at least it should be decomposed into the following categories:
- host
- CPU: only the computing speed can be determined. in a timely manner, the computing speed depends on the bus bandwidth between the memory and the memory speed.
- memory: The size determines the amount of data that can be cached, and mainly determines the access speed of hotspot data
- Disk:
- size: determines how much data you can store.
- speed: determines the delay time of each Io request, that is, the iops and Mbps we often call.
- quantity: The number of disks determines
- type
- mechanical: SAS or SATA or FC?
- SSD: disk or PCI Card?
- RAID card:
- cache: the cache size has a significant impact on data writing speed, and the use of policies directly affects Io efficiency
- Battery: the battery charge/discharge policy affects transient Io fluctuations
- other factors, such as bus bandwidth, determine the efficiency of data transmission between the CPU and memory. This is usually less important, but may also cause bottlenecks.
- storage
- memory: storage devices also have memory to store hotspot data accessed by front-end hosts. The memory size also determines the access speed of hotspot data
- Disk: similar to host disk
- line/loop bandwidth: the loop bandwidth must match the disk bandwidth, at least not less than the capacity that the disk can output, otherwise, you will want to be blocked at the high-speed toll station and wait for the vehicle to pass.
- Network
- latency: the latency varies between different network devices. For OLTP devices, the smaller the latency, the better.
- throughput: For a database cluster, the network throughput between nodes may directly determine the processing capacity of the Cluster.
- iops: For OLTP systems, data transmission is mostly in small I/O and multi-concurrency mode. Sometimes large bandwidth does not necessarily meet the demand
The processing capability provided by the hardware must be the multiple aspects listed above (Here is only the main part, and there may be other) The overall capacity jointly determined by the customer. Any bottleneck in any aspect can lead to poor overall performance, that is, what we often call the Barrel Principle.
In the past, performance bottlenecks are most likely to occur in the following aspects:
-
- I/O resource bottlenecks
IO resource bottlenecks mainly occur on servers.Iowait is very high, and usr accounts for a relatively small numberThe system response is slow, and there are usually a large number of execution sessions in the database.
When we encounter IO resource bottlenecks, we can use the following hardware-level optimization solutions:
- Increase memory to increase the amount of data that can be cached: whether this solution works depends on the total amount of hotspot data in the system. After all, the memory cost is relatively high, and the amount of memory that a single device can manage is limited.
- Improve the I/O capability of the underlying storage device: As described earlier in this article, the improvement of the underlying storage capacity also depends on multiple aspects, including the capacity of a single disk and the number of disks, it is also limited by the bandwidth between the storage itself and the storage and the host. Therefore, we need to consider these three factors while optimizing the underlying storage capabilities, so as to balance the overall analysis with local data.
- bottleneck in CPU resources
when there is a bottleneck in CPU resources, it is mainly manifested in the high proportion of USR in server CPU utilization, while iowait is very small. Most of these problems occur when the data volume is not too large and there is enough memory to cache the data. it is also the database performance bottleneck faced by most small and medium-sized websites.
when there is a CPU bottleneck, it may be caused by two reasons:
- rely too much on the database for logical operations: in this case, the Best optimization method is to migrate the operations from the database end to the Application end as much as possible, reducing the computing workload of the Database Host. After all, the cost of resizing stateful system devices (databases) is much higher than that of stateless system devices (applications ). Of course, if you have to solve the problem from the Database hardware, you can only replace the old host by increasing the number of CPU devices (if supported) or by using a host with higher CPU capabilities
- the database logic Io is too large: in this case, what the hardware can do is to improve the CPU processing capability. Either increase the number of CPUs (if supported) or change to a host with more powerful CPU. However, before that, we recommend that you first try to optimize it from the application perspective to see if it can minimize unnecessary requests or reduce the data volume of each request. At the same time, optimize and adjust the schema structure and index from the database perspective to minimize the amount of data to be retrieved for a request, so as to reduce the logic I/O
- Network resource bottlenecks
In general, the network interaction between applications and databases requires a lot of resources, so the bottleneck in this environment may not be great. However, in a distributed cluster environment, the network environment between database nodes is often referred to as a system bottleneck.
In common scenarios such as MySQL cluster or Oracle RAC, the advantages and disadvantages of the data exchange network environment between nodes may directly affect the overall processing capability of the system, because there will be a large amount of data exchange between nodes, they all rely on network transmission.
In such a scenario, a cheaper solution is10-ge SwitchTo replace the commonGigabit SwitchTo improve network processing capabilities and reduce network latency. However, this solution mainly improves the throughput. The latency improvement may not necessarily meet certain very high requirements. At this time, we should consider a more expensive but more efficient solution: replace the common switch with InfiniBand to greatly reduce the data exchange delay caused by the network.
The above only analyzes the bottlenecks of major types of hardware resources and provides possible solutions. You are welcome to discuss them together, or include the "hot" SSD. I may write another article about SSD later.ArticleIn China, SSD is officially used in the core product environment, and there may be fewer people than me.
From http://isky000.com/database/mysql-performance-tuning-hardware
