MySQL optimization (ii)--what affects MySQL performance

Several aspects that affect performance:

1. Server hardware
2. Server System
3. Selection of the database storage engine
4. Database parameter Configuration
5. Database structure design and SQL statements

One, server hardware 1, CPU selection whether our application is CPU-intensive

For CPU-intensive applications, we need to speed up the processing of SQL statements. Since MySQL's SQL statement processing is single-threaded, we need better CPUs instead of more CPUs.

How much concurrency is in our system

A single CPU can handle only one SQL statement at a time. So, in case of high concurrency, more CPUs are needed instead of faster CPUs.

Select a 32-bit or 64-bit CPU

The 64-bit is already the default configuration.

2, the choice of memory

Memory IO is much higher than disk, even SSD or fusion_io. So the data is cached in memory to read, can greatly improve performance.
In a common MySQL engine, MyISAM caches the index to memory, and the data is not cached. Instead, InnoDB caches both data and indexes.

Caching is not only useful for reading, it can also be optimized for writes, and we can combine multiple writes into one write operation through caching.

How to choose memory: as large as possible, model as much as possible, a single memory capacity to play.

3, the choice of disk

• Traditional mechanical disk
• RAID enhanced traditional mechanical disk
• SSD and PCIe cards for solid state storage
• Networked Storage Nas and SAN

How to choose traditional mechanical HDD

• Storage capacity
• Transmission speed
• Access time
• Spindle speed
• Physical size: The smaller the performance, the less storage space

RAID Enhancements

什么是RAID：把多个容量小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。

RAID 0, multi-disk concatenation. Lowest cost, easy data loss
RAID 1, mirroring. Disk utilization is reduced by half.
RAID5, distributed parity disk array
RAID10, Shard Mirror

level	features	is redundant	Number of disks	Read	Write
RAID0	Cheap, fast, dangerous	Whether	N	Fast	Fast
RAID1	High-speed reading, simple, safe	Yes	2	Fast	Slow
RAID5	Security, cost of this	Yes	N+1	Fast	Depending on the slowest disk
RAID10	Expensive, high-speed, safe	Yes	2N	Fast	Fast

Recommended RAID10

Solid State Storage

Solid-state disks have better random read and write performance than mechanical disks.
Solid-state disks are more capable of supporting concurrency than mechanical disks.
Solid-state disks are more susceptible to damage than mechanical disks

• Ssds:
  1. Use the SATA interface. can replace traditional disks without any changes
  The SSD of the 2.SATA interface also supports RAID technology. Note The SSD RAID controller differs from the traditional.
• Pci-e
  1. The SATA interface is not available and requires unique drive and configuration
  2. Price is more expensive than SSD, but performance is better than SSD

Usage scenarios for solid state storage:
1. Suitable for scenarios where there is a large number of random I/O.
2. Suitable for I/O bottlenecks for single-threaded workloads.

Networked storage

SAN: The server can be used as a hard disk by a light-brazing linked server. Suitable for a large number of sequential reads
NAS: Using a network connection, accessed through file-based protocols such as NFS or SMB.

Networked storage is the right scenario:

• Database backup

4, the impact of network performance

Latency, bandwidth (throughput)

The impact of network bandwidth, not much to say. Many people may think that the database server and the Web server communication is under the Intranet, the bandwidth has little impact. In fact, in the case of big promotion, we have 50 servers, while requesting 2M data to the database, then we need 100M bandwidth.
Suggestions:

• Use high-performance and high-bandwidth network interface devices and switches.
• Bind multiple network cards to enhance availability and bandwidth.
• Network isolation as much as possible.

5, the impact of server hardware on performance, summary

• Cpu
  1, 64-bit CPUs must work in a 64-bit system.
  2, for high concurrency scenarios, the number of CPUs is more important than the frequency
  3. For CPU-intensive scenarios and complex SQL, the higher the frequency, the better.

• Memory
  1. Select the highest frequency memory that the motherboard can use
  2, the size of the memory is very important to performance, so as large as possible

• I/O subsystem
  pcie–>ssd–>raid10–> Disk –>san

Second, the server system

MySQL-Suitable operating system

• Windows
• Freebsd
• Solaris
• Linux

The following is an example of a CentOS system:

System parameter Optimization

kernel-related parameters (/etc/sysctl.conf)

Network-related
-net.core.somaxconn=65535

    对于一个TCP连接来说，服务器端和客户端需要进行三次握手来建立网络的连接。当三次握手成功之后，我们可以用netstat命令查看端口的状态由监听转变成了连接，接着该连接就可以传输数据了。对于一个监听状态的端口，都会有自己的监听队列，而该参数就决定了监听队列的最大长度。

-net.core.netdev_max_backlog=65535
-net.ipv4.tcp_max_syn_backlog=65535

Speeding up the recovery of TCP connections

• net.ipv4.tcp_fin_timeout=10
• Net.ipv4.tcp_tw_reuse=1
• Net.ipv4.tcp_tw_recycle=1

Default and maximum values for TCP connection receive and send buffer sizes

• Net.core.wmem_default = 87380
• net.core.wmem_=16777216
• net.core.rmem_default=87380
• net.core.rmem_max=16777216

Detects if the TCP connection used has expired

• net.ipv4.tcp_keepalive_time=120 Send Time
• NET.IPV4.TCP_KEEPALIVE_INTVL=30 does not return the interval that is sent again
• Net.ipv4.tcp_keepalive_probes=3 sent several times

Memory-related parameters

• Kernel.shmmax = 4294967295

Attention:
1. This parameter should be set large enough to accommodate the entire InnoDB buffer pool size under a shared memory segment
2, the size of this value for 64-bit Linux system, the maximum value is the physical memory value -1byte, the recommended value is greater than half of physical memory, the general value is greater than the INNODB buffer pool size.

• Vm.swappiness=0
  This parameter has a noticeable effect on performance when memory is low.

  拓展：Linux系统内存交换区：在linux系统安装时都会有一个特殊的磁盘分区，称之为系统交换分区。如果我们使用free-m在系统中查看可以看到类似下面内容，其中swap就是交换分区。

  当操作系统因为没有足够的内存时就会将一些虚拟内存写到磁盘的交换区中，这样就会发生内存交换

There are some controversies about whether to use swap partitions on the MySQL server:

-The risk of outright disabling:
1, reduce the performance of the operating system
2, easy to cause memory overflow, crash, or are killed by the operating system

Conclusion: Keep the swap partition on the MySQL server, but control when to use it. Vm.swappiness=0 is to tell the Linux kernel not to use swap zones unless the virtual memory is completely full.

Increase resource limit (/etc/security/limit.conf)
This file is actually a configuration file for the Linux Pam, which is the plug-in authentication module.
An important configuration is the limitation of the number of open files.

• * Soft Nofile 65535
• * Hard Nofile 65535

Where: * is valid for all users, soft refers to the current system in effect, hard indicates the maximum value that can be set in the system, Nofile indicates that the limit of resources is the maximum number of open files, 65535 is the number of restrictions.

Conclusion: Increase the number of open files to 65535 to ensure that enough file handles can be opened.
Note: Changes to this file need to be restarted for the system to take effect.

Disk scheduling Policy (/sys/block/devname/queue/scheduler)

• NoOp anticipatory deadline [CFQ]
  View: Cat/sys/block/sda/queue/scheduler

NoOp (Elevator-type scheduling strategy)

    noop实现了一个FIFO队列，它像电梯的工作方法一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。

Deadline (cutoff time scheduling policy)
The best for the database.

modifying disk scheduling policies

echo deadline > /sys/block/sda/queue/scheduler

Selection of File systems

Windows:
-FAT
-NTFS

Linux:

• EXT3
• EXT4
• XFS (higher performance)

Third, the storage engine MySQL architecture

Note: The storage engine is for tables instead of libraries (different tables in one library can use different storage engines)

MyISAM

MySql5.5 Previous version of the default storage engine.
The MyISAM Storage Engine table is composed of myd and myi.

Characteristics:

• Concurrency and lock levels: concurrency for read-write blending is not too good
• Table Damage Repair
• Supported index types: full-Text indexing
• Support Compression: Compressed table only supports read operation, not support write operation

Applicable scenarios:

• Non-transactional applications
• Read-only class applications
• Space class applications

Innodb

InnoDB using tablespace for data storage

MySQL optimization (ii)--what affects MySQL performance