Introduction to mysql optimization ideas

The content of this article is an introduction to mysql optimization ideas. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Database level problem solving ideas

General emergency tuning ideas:
In case of sudden business processing lag, normal business processing cannot be carried out! A scenario that needs to be resolved immediately!

1、show processlist

2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
select id,name from stu where id=2-1 函数 结果集>30;
show index from table;

3、通过执行计划判断，索引问题(有没有、合不合理)或者语句本身问题

4、show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session

General tuning ideas:
For periodic business lags, for example, the business is extremely slow at 10-11 o'clock every day, but it can still be used, and it will be fine after this period.

1. Check the slowlog, analyze the slowlog, and analyze the slow query statements.

2. Check all slow statements one by one according to a certain priority.

3. Analyze top sql, perform explain debugging, and check the statement execution time.

4. Adjust the index or the statement itself.

1. System level

cpu aspect:
vmstat, sar top, htop, nmon, mpstat

Memory:
free, ps -aux,

IO devices (disk, network):
iostat, ss, netstat, iptraf, iftop, lsof,

vmstat Command description:
Procs: r displays How many processes are waiting for CPU time. b Displays the number of processes in uninterruptible sleep. Waiting for I/O

Memory: swpd displays the number of data blocks swapped to disk. The number of unused data blocks, user buffer data blocks, and data blocks used by the operating system

Swap: The number of data blocks that the operating system swaps from disk to memory and from memory to disk per second . s1 and s0 are preferably 0

Io: The number of data blocks written to device b0 that are read from device b1 per second. Reflects disk I/O

System: Shows the number of interrupts (in) and context switches (cs) that occur per second

Cpu: Shows the number used to run user code, system code , idle, CPU time waiting for I/O

iostat command description
Example command: iostat -dk 1 5
iostat -d -k -x 5 (View device usage (%util) and response time (await))

tps: The number of transmissions per second of the device. "A transfer" means "an I/O request." Multiple logical requests may be combined into a "single I/O request".

iops: When the hardware leaves the factory, the manufacturer defines a maximum number of IOs per second. The size of the "one transfer" request is unknown.

kB_read/s: The amount of data read from the device (drive expressed) per second;

KB_wrtn/s: The amount of data written to the device (drive expressed) per second;

kB_read: The total amount of data read;

kB_wrtn: The total amount of data written; these units are Kilobytes.

1. Solutions to system-level problems

Do you think it is better to have a high load or a low load?
In actual production, it is generally believed that there is no problem as long as the CPU does not exceed 90%.

Of course, the following special situations are not excluded:
Problem 1: High CPU load, low IO load
Insufficient memory

Poor disk performance

SQL problem- ----->Go to the database layer to further troubleshoot the sql problem

There is a problem with IO (the disk is critical, the raid design is not good, the raid is degraded, locked, and the tps per unit time is too high)

tps is too high: a large number of small data IO, a large number of full table scans

Problem 2: high IO load, low cpu load
A large number of small IO write operations:

autocommit, which generates a large number of small IOs

IO/PS is a fixed value of the disk. When the hardware leaves the factory, the manufacturer defines a maximum number of IOs per second.

A large number of large IO write operations

The probability of SQL problems is relatively high

Problem 3: IO and CPU loads are very high
The hardware is not enough or there is a problem with SQL

5. Basic optimization

1. Optimization ideas

Positioning problem points:
Hardware--> System--> Application-- > Database --> Architecture (high availability, read-write separation, sub-database and sub-table)

Processing direction:
Clear optimization goals, compromise between performance and security, and prevent problems before they occur

1. Hardware optimization

Host aspect:
According to database type, host CPU selection, memory capacity selection, disk selection

Balance memory and disk resources

Random I/O and sequential I/O

BBU (Battery Backup Unit) of the host RAID card is turned off

Selection of cpu:
Two key factors of cpu: Number of cores and main frequency

Choose according to different business types:

Cpu-intensive: more calculations, OLTP CPUs with high main frequency and more cores

IO-intensive: For query comparison, the number of OLAP cores is larger, and the main frequency is not necessarily high.

Memory selection:
OLAP type database requires more memory, which is related to the level of data acquisition.

OLTP type data generally requires 2 to 4 times the number of CPU cores in memory, and there is no best practice.

Storage:
Choose different storage devices according to different types of stored data

Configure a reasonable RAID level (raid 5, raid 10, hot spare disk)

For the operating system, there is no need to make too special choices. It is best to make redundant (raid1) (ssd, sas, sata)

raid card: host raid card selection:
implementation Operating system disk redundancy (raid1)

Balance memory and disk resources

Random I/O and sequential I/O

BBU (Battery) of the host RAID card Backup Unit) to close

Network equipment:
Use network equipment that supports higher traffic (switches, routers, network cables, network cards, HBA cards)

Note: The above planning should be considered when initially designing the system good.

1. Server hardware optimization

1. Physical status light:

2. Comes with management device: remote control card (FENCE device: ipmi ilo idarc ), power on/off, hardware monitoring.

3. Third-party monitoring software and equipment (snmp, agent) monitor physical facilities

4. Storage equipment: built-in monitoring platform. EMC2 (acquired by HP), Hitachi (hds), IBM low-end OEM hds, high-end storage is its own technology, Huawei storage

1. System optimization

Cpu:
Basically no adjustment is required, just focus on hardware selection.

Memory:
Basically no adjustment is needed, just focus on hardware selection.

SWAP:
MySQL try to avoid using swap. The default swap in Alibaba Cloud's server is 0

IO:
raid, no lvm, ext4 or xfs, ssd, IO scheduling strategy

Swap adjustment (do not use swap partition)

This parameter determines whether Linux prefers to use swap or release the file system cache. In situations where memory is tight, lower values ​​tend to free up the file system cache. Of course, this parameter can only reduce the probability of using swap, but cannot prevent Linux from using swap.

Modify the MySQL configuration parameter innodb_flush_method and enable O_DIRECT mode. In this case, InnoDB's buffer pool will directly bypass the file system cache to access the disk, but the redo log will still use the file system cache. It is worth noting that Redo log is in overwrite mode. Even if the file system cache is used, it will not take up too much

IO scheduling strategy:

1. System parameter adjustment

Linux system kernel parameter optimization:

User restriction parameters (MySQL does not need to set the following configuration):

1. Application optimization

Business applications and database applications are independent, firewall: iptables, selinux and other useless services (closed):

Do not start the graphical interface runlevel 3 when installing a server with a graphical interface. In addition, think about whether our business will be real in the future. Do you need MySQL or use another kind of database? The highest state of using a database is not to use a database.

6. Database optimization
SQL optimization direction:
Execution plan, index, SQL rewriting

Architecture optimization direction:
High availability architecture, high performance architecture, sub-database Sub-table

1. Database parameter optimization

Adjustment:
Instance overall (advanced optimization, expansion)

Connection layer (basic optimization)
Set up reasonable connection clients and connection methods

This article has ended here. For more other exciting content, you can pay attention to the MySQL Tutorial Video column of the PHP Chinese website!

The above is the detailed content of Introduction to mysql optimization ideas. For more information, please follow other related articles on the PHP Chinese website!