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

This article mainly introduces how to slow the MySQL database, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

There are two common types of database applications: CPU-intensive and Imax-O-intensive. Therefore, when we troubleshoot database problems, we usually start from these two aspects.

For CPU-intensive databases, there are the following performance issues:

1. Complex query statements, stored procedures, triggers, custom functions, etc.

two。 Lock competition problem

3. The amount of complication is large.

The way to solve the problem:

1. Through the slow query log, find out the relevant statements, optimize the query statements to adjust the index strategy. Complex stored procedures, triggers and custom functions can also be implemented by application code.

two。 According to the three tables of innodb_trx, innodb_locks and innodb_lock_watis in infomation_schema, find out the transaction and development coordination of the lock. If the scenario allows, consider lowering the transaction isolation level to read commit.

3. Separate reading and writing and split horizontally. Or add a cache layer so that the highly concurrent read and write pressure can be digested by the cache layer.

4. Adjust the parameters related to mysql and cpu, which are not listed here.

There are the following performance problems for IPUBO-intensive databases:

1. Projected all fields, full table scan, table structure design, index design problems, etc.

two。 The memory buffer setting is too small, resulting in too many disk Igamo

3. The network bandwidth is small (common in distributed systems).

The way to solve the problem:

1. Through the slow query log, find out the statements that have been executed for a long time and have a large result set. Reduce the projected fields, select only the necessary fields for projection, and optimize the table structure and index design.

two。 Adjust the settings of memory buffer, log refresh, refresh method and other parameters, which are not listed here.

3. Increase network bandwidth and adjust kernel parameters.

If the above measures cannot solve the problem or the effect is not obvious, consideration can be given to capacity expansion. Expansion is divided into vertical expansion and horizontal expansion.

Scale-up: generally used to solve the problem of long response time. Increasing the CPU computing power can reduce the response time, increasing the memory can reduce the disk Imax O, and make the disk do raid5, 10, 01 or directly use SSD to improve the processing capacity of the disk.

Scale-out: generally used to solve high concurrency problems. For example, do simple read-write separation, use mycat to slice the amount of data, and so on, try to share the pressure of the single machine.

It is mentioned here to solve the problem of high concurrency, in fact, we can also use an asynchronous way to move the pressure forward to the cache layer to reduce the write pressure; at the same time, caching some hot data can also greatly reduce the read pressure.

Thank you for reading this article carefully. I hope the article "how to slow the MySQL Database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.