Reproduced http://isky000.com/database/mysql-performance-tuning-hardware
Next, the last article on selecting the storage engine for MySQL database performance optimization is the sixth article in the MySQL database performance optimization topic series: hardware optimization for MySQL database performance optimization
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 also 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 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 great impact on the Data Writing speed, and the use of policies also directly affects the IO efficiency.
- Battery: the battery charge/discharge policy affects transient Io fluctuations.
- Others: bus bandwidth determines 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 Disks
- Line/loop bandwidth: the loop bandwidth must be able to match the disk bandwidth, at least not less than the capacity that the disk can output. Otherwise, you will want to be congested on 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 alone may not 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.
- CPU resource bottlenecks
When CPU resources encounter bottlenecks, the main performance is that USR accounts for a high proportion of 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 facing most small and medium-sized websites.
When there is a CPU resource 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.
- Database logic Io is too large: in this case, what you can do from the hardware point of view 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, we optimize and adjust the schema structure and index from the database perspective, so as to minimize the amount of data to be retrieved for a request, so as to reduce the logic Io.
- 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. I have been familiar with SSD in China and officially applied it to the core product environment, but there may be fewer people than I did.
