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

Some common factors affecting performance: server hardware server system database storage engine MyISAM: can make good use of memory, but does not support transactions, table-level locks InnoDB: transaction-level storage engine, perfect support for row-level locks and transaction ACID characteristics, database parameters configuration database table structure design and SQL statement execution efficiency version of the database CPU resources and available memory size

In the server hardware, the most likely to affect database performance is the CPU resources and the amount of available memory, as well as Icano.

Points to consider when choosing CPU:

Is our application CPU-intensive? If it is CPU-intensive, then we need a CPU with strong computing power, that is, what is the concurrency of our application with high frequency? If the concurrency is large, you need to choose more core CPU to improve throughput. If you choose a multi-core CPU, it is best to use a version above MySQL5.6, because the lower version does not support multi-core CPU very well. Note: MySQL currently does not support concurrent processing of the same SQL by multiple CPU. Note: do not use a 32-bit operating system on 64-bit CPU

Memory:

The more capacity, the better, but the impact on performance is limited, because the performance main frequency can not be increased indefinitely, then choose the highest frequency and best traditional mechanical disk that the server motherboard can support.

Use traditional machine disks. The process of reading data from a mechanical hard disk:

Move the head to the correct position on the surface of the disk and wait for the disk to rotate, so that the required data is under the head waiting for the disk to rotate, and all the required data is read out by the head.

Note: step 1 + part 2 = the access time of the disk. Time consumed in the third step = transfer speed of the disk

Therefore, the selection of mechanical hard disk mainly refers to the following points:

Storage capacity transfer speed access time spindle speed physical size using RAID to enhance the performance of traditional machine disks

RAID:

RAID is the abbreviation of disk redundancy queue (Redundant Arrays of Independent Disks). To put it simply, the function of RAID is to combine multiple disks with smaller capacity into a group of disks with larger capacity, and to provide data redundancy to ensure data integrity.

Commonly used RAID mode-RAID 0:

RAID 0 is the earliest RAID pattern, also known as data stripe. It is the simplest form of building disk array, it only needs more than 2 hard disks, the cost is low, and it can improve the performance and throughput of the whole disk. RAID 0 does not provide redundancy or error repair capabilities, but the implementation cost is the lowest.

RAID 0 pattern structure diagram:

The commonly used RAID mode-RAID 1:

RAID 1, also known as disk mirroring, is based on mirroring the data from one disk to another, that is, when the data is written to another disk, an image file will be generated on another idle disk to maximize the reliability and repairability of the system without affecting performance.

RAID 1 pattern structure diagram:

The commonly used RAID mode-RAID 5:

RAID 5 is also called distributed parity disk array, which distributes data to multiple disks through distributed parity blocks. In this way, if any disk data fails, it can be rebuilt from the parity block. However, if two disks fail, the data of the entire volume cannot be recovered.

RAID 5 pattern structure diagram:

The commonly used RAID schema, RAID 10, is suitable for the database:

RAID 10, also known as multipart mirroring, first RAID 1 to the disk and then RAID 0 to the two groups of RAID 1 disks, so it has good performance for reading and writing, and it is easier and faster to rebuild than RAID 5.

RAID 10 pattern structure diagram:

For the selection of RAID level, please refer to the following table:

Use solid-state storage SSD and PCI cards

Compared with mechanical disk, solid state disk has better random read and write performance, better concurrency support than mechanical disk solid state disk, and is easier to damage than mechanical disk solid state disk.

Characteristics of solid state disks:

Using the SATA interface, SSD, which can replace traditional disks without any changes to the SATA interface, also supports RAID technology.

Characteristics of PCIe card:

Can not use SATA interface, requires a unique driver and configuration price equivalent to SSD expensive, higher performance than SSD

Usage scenarios for solid state disks:

The use of network storage NAS and SAN to solve the bottleneck of single-threaded load in scenarios with a large number of random Istroke O

SAN (Storage Area Network) and NAS (Network-Attached Storage) are two methods of loading external file storage devices onto the server

The SAN device is linked to the server through optical fiber, the device is accessed through the block interface, and the server can use it as a hard disk:

Advantages and disadvantages of SAN:

It is suitable for a large number of sequential read and write, read and write Imax O, cache, Imax O and random read and write slowness, which is not as good as local RAID disk.

NAS devices use network links and are accessed through file-based protocols such as NFS or SMB.

Scenarios suitable for network storage:

Database backup

Network impact on performance:

Latency, throughput / bandwidth, network quality (packet loss) recommendation: use high-performance and high-bandwidth network interface devices and switches to bind multiple network cards to enhance availability and bandwidth to isolate the network as much as possible, do not expose the database to the extranet summary: the impact of server hardware on performance

CPU:

64-bit CPU must work in 64-bit systems. For scenarios with high concurrency, the number / frequency ratio of CPU is finally as high as possible for CPU-intensive scenarios and complex SQL.

Memory:

Choosing the amount of memory at the highest frequency that the motherboard can support is important for performance, so be as large as possible

Ipaw O subsystem:

PCIe-> SSD-> Raid10-> disk-> impact of SAN operating system on performance-suitable operating system for MySQL

What MySQL needs to pay attention to in different operating systems:

The database name table name under Windows is not case-sensitive, but it is case-sensitive on Unix/Linux, so if you use FreeBSD in uniform lowercase as much as possible, you need to use the latest version, because the old version does not support MySQL very well. MySQL is supported on Solaris systems, and it is famous for its stability. CentOS system parameter optimization

Kernel related parameters (/ etc/sysctl.conf):

Set the length of the maximum listening queue: when the rate of packets received by each network interface of net.core.somaxconn=65535 is faster than the rate at which the kernel processes these packets, the maximum number of packets allowed to be sent to the queue, that is, the maximum number of completed connections: net.core.netdev_max_backlog=65535 specifies the maximum number of clients that can accept SYN synchronization packets, that is, semi-connection limit: net.ipv4.tcp_max_syn_backlog=65535 if the socket is closed by the local side This parameter determines how long it stays in the FIN-WAIT-2 state: net.ipv4.tcp_fin_timeout=10 turns on reuse. Allow TIME-WAIT sockets to be reused for new TCP connections. Default is 0, which means disabled: net.ipv4.tcp_tw_reuse=1 enables fast recycling of TIME-WAIT sockets in TCP connections. Default is 0. Off: net.ipv4.tcp_tw_recycle=1 default TCP data send window size (bytes): net.core.wmem_default=87380 maximum TCP data send window size (bytes): net.core.wmem_max=16777216 represents the default value of the receive socket buffer size (bytes): net.core.rmem_default=87380 represents the maximum receive socket buffer size (bytes): net.core.rmem_max=16777216TCP send Interval between keepalive probe messages (seconds) Used to confirm whether the TCP connection is valid: when the net.ipv4.tcp_keepalive_time=120 probe message does not get a response, the interval time to resend the message (in seconds): the maximum number of keepalive probe messages sent by net.ipv4.tcp_keepalive_intvl=30 before determining that the TCP connection is invalid: one of the most important parameters in the net.ipv4.tcp_keepalive_probes=3Linux kernel parameters Used to define the maximum value of a single shared memory segment: the parameter kernel.shmmax=429496295 should be set large enough to accommodate the size of the entire Innodb buffer pool under one shared memory segment. For 64-bit Linux systems, the preferred maximum value is-1byte of physical memory. The recommended value is more than half of the physical memory, which is generally larger than the size of the Innodb buffer pool. You can also take the physical memory-1byte parameter, which has a significant impact on performance when memory is insufficient. This parameter tells the Linux kernel not to use swap partitions unless the virtual memory is completely full: vm.swappiness=0

Reference:

Https://www.cnblogs.com/DengGao/p/tcp_parameter.html

Linux system memory swap partition:

When the Linux system is installed, there will be a special disk partition, called the system swap partition, that is, the swap partition. When the operating system does not have enough memory, it will write some virtual memory to the disk swap partition, so memory swapping will occur.

There is some controversy over whether to use swap partitions on MySQL servers:

Some people think that swap partition should be disabled completely on the Linux system where the MySQL service is located, while others think that disabling swap partition brings the following risks: reducing the performance of the operating system can easily cause memory overflow, collapse, or the process is concluded by the operating system kill: so it is necessary to keep the swap partition on the MySQL server, but it is necessary to control when to use the swap partition. At this point, we need to use the vm.swappiness=0 parameters we mentioned above.

Add the resource limit (/ etc/security/limit.conf), which is actually the configuration file of the Linux PAM, the plug-in authentication module. We will control the limit on the number of open files by adding the following parameters at the end of the file:

* soft nofile 65535 * hard nofile 65535

Description:

* the soft that is valid for all users refers to the setting that the current system takes effect. For the same resource, the value of soft cannot be higher than hard. Hard indicates that the maximum value that can be set in the system is nofile, which means that the limited resource is the maximum number of files opened. 65535 is the limited number of times.

Since the default number of openable file handles on the system is relatively small, we usually increase the number of openable files on the operating system where the MySQL service resides to 65535 to ensure that enough file handles can be opened. It should be noted that the changes to this file will not take effect until the system is rebooted.

Disk scheduling policy (/ sys/block/vda/queue/scheduler):

The disk queue scheduling algorithm on Linux determines the order in which requests from block devices are sent to the underlying devices. By default, completely fair queues such as cfq are used in desktop systems, but systems that run MySQL services are not suitable, because under the workload of MySQL, cfq will insert some unnecessary requests in the queue, resulting in poor response time.

In addition to the default cfq policy, you can choose the following policies:

Noop (elevator scheduling strategy):

NOOP implements a FIFO queue, which organizes Imax O requests like elevators, and when a new request arrives, it merges the request after the most recent request to ensure that the same media is requested. NOOP tends to starve to death and is good for writing, so NOOP is the best choice for flash devices, RAM and embedded devices.

Deadline (Media time scheduling Policy):

Deadline ensures that the request is served within a deadline, which is adjustable, while the default read period is shorter than the write period. This prevents write operations from starving to death because they cannot be read. Deadline is the best choice for database applications.

Anticipatory (expected Imax O scheduling policy):

It is essentially the same as Deadline, but after the last read operation, you have to wait for 6ms before you can continue scheduling other IDeadline O requests. It inserts a new Icano operation in each 6ms and merges some small write streams into one large write stream, trading write latency for maximum write throughput. AS is suitable for environments with more writes, such as file servers, and AS performs poorly with database environments.

The command to modify the disk scheduling policy, for example, I changed the policy to deadline:

Impact of echo deadline > / sys/block/vda/queue/scheduler file system on performance

The file system used by the server has a certain impact on the server's Icano performance, and the choice of file system is very dependent on the operating system. For example, under Windows, only FAT and NTFS are available:

Under Linux, there are EXT3, EXT4 and XFS. These three file systems all have logging function, which is very important for data security. The performance of XFS is higher than that of EXT3 and EXT4:

If you use EXT3 and EXT4, there are several mount parameters to learn about. The mount parameters of the EXT3/4 file system can be configured in the / etc/fstab file:

The first is the data parameter, which has three optional values, which represent different log policies: data=writeback | ordered | journal. Writeback is the best choice for Innodb. Writeback means that only the original data is written to the log, and the original data writing and data writing are not synchronized. This is the fastest configuration, because Innodb has its own transaction log, so choosing Innodb is the best option to record only the original data. However, it provides some guarantee of consistency. Before writing the original data, the data will be written first to make them consistent. This option is slower than writeback, but if there is a crash, it is more secure. Journal provides a behavior of atomic logs, which is recorded in the log before the data is written to the final location. This option is not necessary for Innodb. Journal is the slowest of the three options.

Then let's take a look at the other two important parameters. Before introducing these two parameters, we need to understand that the Linux operating system records the file access time atime by default, and the file system records some timestamps of the file when the file is accessed, created, modified, and so on. For example: file creation time, last modification time, and last visit time; this is not necessary in most cases. Because the system needs to access a large number of files when it is running, reducing some actions (such as reducing the number of timestamp records, etc.) will significantly improve the efficiency of disk IO and improve the performance of the file system. If you encounter a situation where the machine has a high IO load or a high CPU WAIT, you can try to use noatime and nodiratime to disable recording the last access timestamp.

So noatime and nodiratime are used to disable the access time of the file and the time to read the directory, and after disabling the option of these two times, you can reduce some write operations. When the system reads files and directories, it does not have to write to record the above two times.

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.