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

The following together to understand the next set MySQL disk IO detailed steps, I believe we will certainly benefit from reading, the text is not more refined, I hope to set MySQL disk IO detailed steps This short content is what you want.

1. RAID card settings

Close read cache: The cache capacity on the RAID card is limited, so we choose direct mode to read data, thus ignoring read cache.

Turn off read-ahead: RAID card read-ahead has almost no improvement for random IO, so turn read-ahead off.

Close disk cache: Under normal circumstances, if RAID is used, the system will close the disk cache by default, or you can use the command to force it to close.

The above settings can be completed through the RAID card command line, such as LSI chip RAID card using megacli command.

2. Prevent the operating system from updating the atime attribute of the file

# vim /etc/fstab, Add the noatime mount attribute to the partition where the mysql data file is stored, similar to the following:

UUID=ccbb4c85-32ff-4b8d-ae58-f39569b67d96 /data ext4 defaults,noatime 1 2

# mount -o remount,noatime /data

#Command to view device UUID: blkid /dev/sdb1

3. Use bare devices to store InnoDB shared table space (rarely used at present, directly use SSD)

vim my.cnf is as follows:

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

Then start the MySQL service, let it automatically complete the initialization of the partition, and then shut down MySQL. InnoDB tables cannot be created or modified at this point.

Continue to modify my.cnf file as follows:

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

Then restart MySQL service and you can use it.

4. Pay attention to BBWC (Battery Backed Write Cache) problem of RAID card

The RAID card's own battery will be charged and discharged periodically for battery calibration.

Each charge and discharge time is about 3 hours, during which the RAID card will automatically disable the write back policy from the perspective of data security, so that the system IO performance will fluctuate greatly. This has a significant impact on MySQL performance.

You can use MegaCli64 to view and set RAID card cache policies. As follows:

# MegaCli64 -LDInfo -Lall -aALL You can see CurrentCache Policy, the current cache policy.

We can temporarily modify the RAID card policy to force the Write Cache OK if Bad BBU policy to be used during battery charging and discharging, and then restore it to No Write Cache if Bad BBU after charging and discharging is completed to avoid possible data loss caused by power failure.

In addition, we can charge and discharge the image at the time of business downturn, avoiding the execution of RAID card charge and discharge at the peak of business. We can find the time of the next battery recharge from BBU battery logs:

# MegaCli64 -fwtermlog -dsply -a0 -nolog

or # MegaCli64 -AdpBbuCmd -GetBbuProperties -aall

You can also manually trigger the reclean operation of the battery: MegaCli64 -AdpBbuCmd -BbuLearn -aAll

5. Adjust disk IO scheduling algorithm

IO request merging reduces disk seek times. Visits to adjacent mountain areas are processed by merging, while visits to non-adjacent sectors are processed by sorting.

Four IO scheduling algorithms are implemented under Linux. NOOP, Deadline, CFQ, Anticipatory

2.6.17 After the kernel version, the system defaults to CFQ algorithm.

According to the theory (here skipped, need to understand see "simple to understand MySQL" Page371), there are the following conclusions:

1. In a completely random access environment, CFQ and Deadline performance difference is very small, but in the case of large continuous IO, CFQ may increase the response delay of small IO, so it is recommended that MySQL Cloud Virtual Machine be set to Deadline.

For SSD devices, using NOOP or Deadline may usually get better performance than the default.

How to modify the disk IO scheduling algorithm:

# dmesg| grep -i scheduler View IO scheduling algorithms supported by the system [default is noop anticipatory deadline [cfq]]

# more /sys/block/sda/queue/scheduler View scheduling algorithms currently in use

# echo 'deadline' > /sys/block/sda/queue/scheduler Effective immediately after modification

# vim /etc/grub.conf Add elevator=deadline at the end of the kernel line to restart permanently

6. NUMA architecture optimization

Commonly used Cloud Virtual Machine can be divided into three categories:

SMP symmetric multiprocessor architecture

NUMA Non-Consistent Memory Access Structure

MPP Massive Parallel Processing Architecture

The main feature of SMP is sharing. All resources in the system are shared, resulting in very limited scalability of SMP Cloud Virtual Machine. Since each CPU accesses the same memory resource via the same bus, contention for resources arises if two CPUs simultaneously request access to the same memory resource (as a segment of memory address). The more CPUs there are, the more likely this is to happen.

NUMA divides a computer into multiple nodes, each node has multiple CPUs inside, the nodes use a common memory controller, and the nodes are connected and information exchanged through interconnection modules. All memory in a node is equal for all CPUs in the node, but different for all CPUs in other nodes. Each CPU can access the entire system memory, but access to the memory of the local node is faster, access to the memory of the non-local node is slower (through the interconnection module), that is, the speed of CPU access to memory is related to the distance of the node, the distance is called Node Distance.

[root@posp-linux ~]# numactl --hardware

available: 2 nodes (0-1)

node 0 cpus: 0 1 2 3 4 5 12 13 14 15 16 17

node 0 size: 16349 MB

node 0 free: 2654 MB

node 1 cpus: 6 7 8 9 10 11 18 19 20 21 22 23

node 1 size: 16383 MB

node 1 free: 6079 MB

node distances:

node 0 1

0: 10 20

1: 20 10

[root@posp-linux ~]# free -m

total used free shared buffers cached

Mem: 32068 23335 8733 1 679 18982

-/+ buffers/cache: 3673 28395

Swap: 12137 88 12049

As you can see, there are two nodes, each with 16GB of memory. The node distance above has a node local memory declaration distance of 10 and a non-local node memory declaration distance of 20.

NUMA has four memory allocation policies:

Default default is always assigned locally (assigned on the node where the current process is running)

binding bind forces assignment to a specified node

Interleave Interleaves memory across all nodes or specified nodes

preferentially allocated on a specified node, failing to allocate on other nodes

[root@posp-linux ~]# numactl --show Displays NUMA policy for the current system

policy: default

preferred node: current

physcpubind: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

cpubind: 0 1

nodebind: 0 1

membind: 0 1

The default policy is to preferentially allocate memory in the local memory of the CPU where the process is located, which will lead to unbalanced memory allocation between CPU nodes. When a CPU node is short of memory, it will cause swap to occur instead of allocating memory from remote nodes. This is the phenomenon of swap insanity.

MySQL is a single-process multithreaded architecture. When NUMA adopts the default allocation policy, MySQL processes are allocated to one and only one node of NUMA. Assuming MySQL's 14 gigabytes of memory exceeds the node's 8 gigabytes of local memory, Linux would rather use Swap than divide up the physical memory of other nodes. This leads to a problem: although free -m appears to have memory available, MySQL processes are actually using Swap.

MySQL support for NUMA is not very good, if a single machine only runs MySQL, it is recommended to close NUMA. The method is as follows:

# vim /etc/grub.conf Add numa=off at the end of kernel

After saving, restart the Cloud Virtual Machine.

After closing, it looks like this:

[root@node1 ~]# numactl --show

policy: default

preferred node: current

physcpubind: 0 1 2 3

cpubind: 0

nodebind: 0

membind: 0

Or modify NUMA allocation policy to interleave via numactl!!!

If you run multiple MySQL instances on a single machine, you can bind MySQL to different CPU nodes and use the bound memory allocation policy to force memory allocation within the node. This not only makes full use of the NUMA characteristics of the hardware, but also avoids the problem of low utilization of multi-core CPUs by single instance MySQL.

7. File system settings

You can not record the time when mounting:

defaults,noatime,nodiratime

Supplement: Problem of using noatime, nodiradime option when mounting

Without specifying "noatime, nodiradime":

Read files cause atime updates, not mtime and ctime updates

The write file causes only mtime and ctime updates, not atime updates.

Note: When a file system is mounted with the noatime option, reads to the file do not update the atime information in the file properties. The importance of setting noatime is that it eliminates file system writes to files, and files are simply read by the system.

On weekdays, there is often a need to delete files, which is roughly as follows:

Delete files or directories that have not been accessed in the past N days (delete files accessed N days ago)

Watch out for this command!

# find /home/fire/ -atime +N -exec rm -rf {} \;

Assuming that the/home/fire directory was created a week ago, there are two results for this command:

# find /home/fire/ -atime +7 -exec rm -rf {} \;

Specify "noatime": find that/home/fire was created seven days ago and immediately delete the entire directory. The error "find: /home/fire: No such file or directory" is also reported, because find fails after the first rm -rf /home/fire. This is dangerous! The reason is that files are deleted by mistake.

No "noatime" specified: Depends. If/home/fire hasn't been accessed in the past 7 days, delete it as in the case.

If the directory has been accessed in the past 7 days, atime must be within 7 days, then the following directories will be traversed, following the previous logic. But the traversal process changes the atime of the directory.

Looking at the examples above, you will see that finding to delete directories becomes complicated, and you must be careful. So find delete is more appropriate for deleting files, not directories.

When noatime is enabled, how to delete files that have not been accessed in N days:

# find /home/fire/ -atime +N -type f -exec rm -f {} \;

After reading the detailed steps of setting MySQL disk IO, many readers will definitely want to know more about the relevant content. For more industry information, you can pay attention to our industry information column.

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.