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

This article shows you how to optimize CPU consumption in MySQL, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

user

User space CPU consumption, various logic operations

A lot of tps going on.

Function/sort/type conversion/logical IO access…

IO Wait

Waiting for IO request to complete

At this point the CPU is effectively idle

For example, wa in vmstat is high. But IO wait increases, and wa does not necessarily rise (wait for a response after requesting I/O, but the process moves away from the core).

have an impact

Users and IO wait consume most of the CPU.

throughput degradation (tps)

Query response time increases

Increase in slow queries

This effect can also be caused by a sharp increase in concurrency to mysql.

How to reduce CPU consumption?

reduce waiting

Reduce IO volume

SQL/index, reduce the number of rows scanned using the appropriate index (balance the positive benefit and maintenance overhead of the index, space for time).

Improve IO processing capabilities

Add cache/add disk/SSD

reduce computational

Reduce logic operations

Avoid using functions to shift operations to scalable application servers

Character operations such as substr, dateadd/datesub, mathematical functions such as abs

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

For example, union all replaces union, order by index field, etc.

Prohibits type conversion, uses appropriate types and ensures that the type of the incoming parameter is exactly the same as the database field type

For example, numbers are used tiny/int/bigint, etc., which must be converted before being passed into the database.

Simple types, try to avoid complex types, reduce the additional operations caused by complex types. Smaller data types consume fewer disks, memory, cpu cache, and cpu cycles

….

Reduce logical IO

index, optimize the index, reduce unnecessary table scans

such as adde index, adjusting that ord of combining index fields, removing index field with poor selectivity, and so on

table, reasonable split, moderate redundancy

For example, rarely used large fields are split into separate tables, and very frequent small fields are redundant to "reference tables"

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

For example, reduce complex join, reduce order by, try to unite all, avoid sub-queries, etc.

Data type, enough is good, reduce unnecessary use of large fields

If tinyint is enough, don't always int, int is enough, don't always bigint, date is enough, don't always timestamp

….

Reduce query requests (not the database itself)

Appropriate caching, reduce cache data granularity, and appropriately cache static and frequently requested data

Such as user information, commodity information, etc.

Optimize implementation to eliminate unnecessary duplicate requests as much as possible

For example, the problem of prohibiting multiple requests for the same data on the same page, reducing access through cross-page parameter transfer, etc.

reasonable demand estimate demand-output ratio reasonable elimination of demand below the extreme of output ratio

….

The above is how to optimize CPU consumption in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to 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.