The road of high salary, a more complete summary of MySQL high performance optimization in history

Time:2021-6-15

preface

MySQL for many linux practitioners, is a very difficult problem, most of the cases are because of the database problems and processing ideas are not clear. Before the optimization of MySQL, we must understand the query process of MySQL. In fact, a lot of query optimization work is to follow some principles so that the MySQL optimizer can run in a reasonable way as expected.

Today, let’s experience the optimization of Mysql to help you get a smooth road of high salary!

The road of high salary, a more complete summary of MySQL high performance optimization in history

Philosophy of optimization

Note: there are risks in optimization, so you need to be careful!

2.1 possible problems caused by optimization

*Optimization is not always for a simple environment, it is also likely to be a complex system that has been put into production
*Optimization means have great risks, but you don't have the ability to realize and foresee
*Any technology can solve a problem, but there must be a risk of bringing a problem
*For optimization, the problem brought by solving the problem, control in the acceptable range is the result
*It's a failure to keep the status quo or worse

2.2 optimized demand

*Stability and business sustainability are often more important than performance
*Optimization inevitably involves change, and change has risks
*Optimization makes performance better, maintenance and deterioration are equal probability events
*Remember that optimization should be the work of all departments. No single department can optimize the database
*So optimization is driven by business needs

2.3 who will participate in the optimization

In the process of database optimization, database administrators, representatives of business departments, application architects, application designers, application developers, hardware and system administrators, storage administrators and other relevant business personnel should participate.

Optimization ideas

3.1 what to optimize

There are two main aspects in database optimization: security and performance**
*Security --- data sustainability
*Performance: high performance access to data

3.2. What is the scope of optimization

Storage, host and operating system

*Host architecture stability
*I / O planning and configuration
*Swap partition
*OS kernel parameters and network problems

Applications

*Application stability
*SQL statement performance
*Serial access to resources
*Poor performance session management
*Is this application suitable for MySQL

Database optimization

*Memory
*Database structure (Physics & Logic)
*Instance configuration

Note: whether it is in the design system, positioning problems or optimization, can be carried out in this order.

3.3 optimization dimension

There are four dimensions of database optimization
*Hardware
*System configuration
*Database table structure
*SQL and index

The road of high salary, a more complete summary of MySQL high performance optimization in history

Optimization selection:
*Optimization cost: Hardware > system configuration > database table structure > SQL and index
*Optimization effect: Hardware < system configuration < database table structure < SQL and index

What are the advantages of optimization tools?

4.1 database level

Common tools for checking problems

The road of high salary, a more complete summary of MySQL high performance optimization in history

Tools that are not commonly used but easy to use

The road of high salary, a more complete summary of MySQL high performance optimization in history

4.2. Solutions to problems at the database level

General emergency optimization ideas:

For the sudden business processing stuck, unable to carry out normal business processing! Scenes that need to be solved immediately!
* show processlist;
* explain select id ,name from stu where name='clsn';
* show index from table;
*Judging by the execution plan, the index problem (whether or not it is reasonable) or the statement itself is a problem
* show status like '%lock%'; // Query lock status
* kill SESSION_ ID; // Kill the session in question

General optimization ideas:

For the business cycle stuck, for example, at 10-11 o'clock every day, the business is very slow, but it can still be used. After this period of time, it will be good.
*Check the slowlog, analyze the slowlog, and find out the slow query statements
*According to a certain priority, check all slow statements one by one
*Analyze the top SQL, debug the explain, and check the execution time of the statement
*Adjust the index or statement itself

4.3 system level

In terms of CPU:

* vmstat
* sar
* top
* htop
* nmon
* mpstat

Memory:

* free
* ps
* -aux

IO device (disk, network)

* iostat
* ss
* netstat
* iptraf
* iftop
* lsof

Vmstat command description:

*Procs: R shows how many processes are waiting for CPU time. B shows the number of non interruptible dormant processes. Waiting for I / O
*Memory: swpd displays the number of data blocks swapped to disk. The number of unused data blocks, user buffered data blocks, and data blocks for the operating system
*Swap: the number of data blocks that the operating system switches 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 read from device B1 per second. Reflects disk I / O
*System: shows the number of interrupts per second (in) and the number of context switches (CS)
*CPU: display the CPU time for running user code, system code, idle and waiting for I / O

Vmstat 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 transfers per second of the device“ "One transfer" means "one I / O request". Multiple logical requests may be combined into one I / O request.
*IOPs: when the hardware leaves the factory, the maximum number of IO per second defined by the manufacturer, and the size of "one-time transmission" 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.

4.4 solutions to system level problems

Do you think high load is better or low load is better?

In the actual production, it is generally believed that as long as the CPU does not exceed 90%, there is no problem.

Of course, the following special cases are not excluded:

Problem 1: high CPU load and low IO load

*Not enough memory
*Poor disk performance
*SQL problems -------- go to the database layer to further investigate SQL problems
*IO problems (disk critical, bad raid design, degraded raid, locked, high TPS per unit time)
*TPS too high: a large number of small data IO, a large number of full table scan

Problem 2: high IO load and low CPU load

*Lots of small IO writes
*Autocommit generates a lot of small io
*IO / PS, a fixed value of disk, is the maximum number of IO per second defined by the manufacturer when the hardware leaves the factory
*Massive IO write operations
*The probability of SQL problems is relatively high

Problem 3: IO and CPU load are very high

Insufficient hardware or SQL problem

Foundation optimization

5.1 optimization ideas

Positioning problem points:
Hardware  -->  system  -->  application  -->  database  -->  Architecture (high availability, separation of read and write, database and table)
Processing direction:
Make clear the tradeoff between optimization objectives, performance and safety, and take precautions

5.2 hardware optimization

Host side:

*According to the database type, host CPU selection, memory capacity selection, disk selection
*Balancing memory and disk resources
*Random I / O and sequential I / O
*The BBU (battery backup unit) of the host raid card is closed

CPU selection:

*Two key factors of CPU: core number and main frequency
*Choose according to different business types
*CPU intensive: there are more calculations, and more CPUs and cores with high main frequency of OLTP
*IO intensive: compared with query, OLAP has more cores and the dominant frequency is not necessarily high

Memory selection:

*OLAP type database, need more memory, and data acquisition level
*General memory of OLTP type data is 2 to 4 times of CPU core, and there is no best practice

Storage:

*According to the different types of storage data, choose different storage devices
*Configure reasonable RAID level (RAID5, RAID10, hot spare)
*As for the operating system, it is not necessary to make a special choice. It is better to make a good job of redundance (RAID1) (SSD, SAS, SATA)

Raid card: host raid card selection:

*Redundancy of operating system disk (RAID1)
*Balancing memory and disk resources
*Random I / O and sequential I / O
*The BBU (battery backup unit) of the host raid card should be shut down

Network equipment:

Use traffic to support higher network devices (switches, routers, network cables, network cards, HbAS)

Note: the above plans should be considered in the initial design of the system.

5.3 server hardware optimization

*Physical status light
*With management equipment: remote control card (fence equipment: IPMI ILO idarc), switch, hardware monitoring
*Third party monitoring software and devices (SNMP, agent) monitor physical facilities
*Storage device: built in monitoring platform. EMC 2 (acquired by HP), Hitachi (HDS), IBM low-end OEM HDS, high-end storage is its own technology, Huawei storage

5.4 system optimization

There is no need to adjust the hardware selection.

Memory:

There is no need to adjust the hardware selection

SWAP:

MySQL tries to avoid using swap. The default swap in alicloud servers is 0

IO:

*Raid, no LVM, ext4 or XFS, SSD, IO scheduling strategy
*Swap adjustment (without swap partition)

The road of high salary, a more complete summary of MySQL high performance optimization in history

This parameter determines whether Linux prefers to use swap or free the file system cache. In the case of tight memory, the lower the value, the more likely to release the file system cache. Of course, this parameter can only reduce the probability of using swap, and can not avoid using swap in Linux.

Modify the configuration parameter InnoDB of MySQL_ flush_ Method, open o_ Direct mode. In this case, InnoDB’s buffer pool will directly bypass the file system cache to access the disk, but redo log will still use the file system cache. It is worth noting that the redo log is in the override mode. Even if the cache of the file system is used, it will not occupy too much.

IO scheduling strategy:

The road of high salary, a more complete summary of MySQL high performance optimization in history

5.5 system parameter adjustment

Linux Kernel Parameter Optimization:

The road of high salary, a more complete summary of MySQL high performance optimization in history

User limit parameters (MySQL can not set the following configuration)

The road of high salary, a more complete summary of MySQL high performance optimization in history

5.6 application optimization

Business application and database application are independent, firewall: iptables, SELinux and other useless services (closed)

The road of high salary, a more complete summary of MySQL high performance optimization in history

Do not start runlevel 3 for the server with graphical interface. In addition, consider whether our business really needs MySQL or other kinds of databases in the future. The highest level of using database is not using database.

Database optimization

SQL optimization direction:
Execution plan, index, SQL rewrite
Architecture optimization direction:
High availability architecture, high performance architecture, sub database and sub table

6.1 database parameter optimization

Adjustment:

Overall instance (advanced optimization, extension)

The road of high salary, a more complete summary of MySQL high performance optimization in history

Connection layer (Foundation optimization)

Set up reasonable customer connection and connection mode

The road of high salary, a more complete summary of MySQL high performance optimization in history

SQL layer (basic optimization)

* query_ cache_ Size: query cache
*OLAP type database, need to focus on increasing the memory cache
*But in general, it will not exceed GB
*For data that is often modified, the cache will immediately become invalid
*We can use memory database (redis, memecache) instead of its function

Storage engine layer (InnoDB basic optimization parameter)

The road of high salary, a more complete summary of MySQL high performance optimization in history

Original author: a miserable young man  
Source 01:http://clsn.io/clsn/lx287.html
Source 02: Architecture digest official account

This work adoptsCC agreementReprint must indicate the author and the link of this article

Aduh