How to solve the problem of excessive CPU consumption in MySQL

Today, I will talk to you about how to solve the problem of excessive CPU consumption in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

User

User space CPU consumption, various logic operations

A lot of tps is under way

Function / sort / type conversion / logical IO access.

User space consumes a lot of cpu, what is the resulting system call? Which functions use cpu cycles?

IO wait

Wait for the completion of the IO request

CPU is actually idle at this time

For example, wa in vmstat is very high. However, when the IO wait increases, the wa does not necessarily rise (wait for a response after the request for I _ ram O, but the process is moved away from the core)

Have an impact

Users and IO waits consume most of the cpu

Throughput degradation (tps)

Increased query response time

Increase in the number of slow queries

The sharp increase in concurrency of mysql will also have the impact of appeal.

How to reduce CPU consumption?

Reduce waiting

Reduce I / O

SQL/index, using the appropriate index to reduce the number of rows scanned (balance the positive benefits and maintenance costs of the index, space for time)

Improve IO processing capacity

Add cache/ plus disk / SSD

Reduce calculation

Reduce the amount of logical computation

Avoid using functions and transfer operations to scalable application servers

Such as substr and other character operations, dateadd/datesub and other date operations, abs and other mathematical functions

Reduce sorting, use indexes to obtain ordered data or avoid unnecessary sorting

Such as union all instead of union,order by index fields, etc.

Prohibit type conversion, use the appropriate type, and ensure that the incoming parameter type is absolutely consistent with the database field type

For example, if the number is used with tiny/int/bigint, the conversion must be changed in the application before being transferred to the database.

Simple types, try to avoid complex types, reduce the additional operations caused by complex types. Smaller data types take up less disk, memory, cpu cache, and cpu cycles

....

Reduce logical IO

Index, optimize indexes and reduce unnecessary table scans

Such as adding indexes, adjusting the order of combined index fields, removing index fields with poor selectivity, and so on.

Table, reasonable split, moderate redundancy

Such as splitting rarely used large fields into separate tables, very frequently small fields are redundant to the "reference table"

SQL, adjust SQL writing, make full use of existing indexes, avoid unnecessary scanning, sorting and other operations

Such as reducing complex join, reducing order by, union all as much as possible, avoiding subqueries, etc.

Data type, as long as it is enough, to reduce the unnecessary use of large fields

If tinyint is enough, don't always use int,int, don't always use int,int, and don't always timestamp if date is enough.

....

Reduce the number of query requests (not the database itself)

Appropriate caching, reducing the granularity of cached data, and properly caching static and frequently requested data

Such as user information, commodity information, etc.

Optimize the implementation to remove unnecessary repeated requests as far as possible

For example, the problem of prohibiting repeated requests for the same data on the same page, reducing access by passing parameters across pages, etc.

After reading the above, do you have any further understanding of how to solve the problem of excessive CPU consumption in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.