Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to optimize the excessive CPU consumption caused by MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Who is consuming cpu?

User + system + IO wait + soft and hard interrupt + idle

Who is the culprit?

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.

Reasonable demand, evaluate the demand-output ratio, and reasonably remove the demand under the extreme output ratio.

... .

Upgrade cpu

If the demand cannot be met after reducing calculation and waiting, the utilization rate of cpu is still high.

It's time to come up with the final killer mace, upgrade cpu, choose faster cpu or more cpu?

Low latency (fast response), requires faster cpu (only one cpu can be used per query)

High throughput, running many queries at the same time, can benefit from multiple cpu processing queries

The above is how to optimize the excessive CPU consumption caused by MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.