current position:Home>MySQL slow log optimization

MySQL slow log optimization

2022-01-26 23:56:59 Wall view

Performance problems with slow logs

  1. cause I/O and CPU resource consumption : Slow logs usually scan a large amount of non purposeful data , Nature will cause I/O and CPU Resource consumption , Affect the normal use of other businesses , It's probably because a single is slow SQL Can slow down the performance of the entire database , And this kind of slow SQL, In the actual business scenario , Usually the program initiates several SQL request , adopt SHOW PROCESSLIST The command can capture both N Similar SQL The request is executing .
  2. Lock waiting consumption : Due to slow SQL(select Inquire about ) It will block MDL Lock acquisition , So for XtraBackup Full backup and for tables DDL Operations can be blocked , once DDL Blocked , The request for the table becomes a serial block , The follow-up business cannot be carried out .
  3. Lock application consumption : For non select Slow transaction of query , SQL Also hold the lock without releasing , So that subsequent transactions cannot apply for locks , Cause waiting failure , It is unacceptable for the business itself .

How to collect slow logs ?

    ELK Slow log of system analysis  

  • MySQL Open slow log ——> File logging slow logging
  • ELK Environment building
  • MySQL Server installation Filebeat And carry on mysql-slow.log Filter processing configuration
  • ELK-WEB View dimensions

 Percona Analyze slow logs

  Percona Of pt-query-digest It is a product that can be aimed at MySQL A tool for customized analysis of slow logs

 

  • MySQL Open slow log ——> File logging slow logging
  • Percona Component installation and writing pt-query-digest Timing script
  • The remote database is periodically deleted and retained
  • Remote database provides Web API Interface query display

You need to understand the basis of optimization

   The idea of optimizing slow logs is “ collect —— analysis —— Optimize —— The prevention of ”

   Optimize SQL The basic means of is EXPLAIN, On this basis , in the light of SQL Statement fixed-point optimization eliminates .

  EXPLAIN The basic grammar is EXPLAIN + SQL, We need to target EXPLAIN Read it :

  

  • select_type: Query mode

  • type: How to scan ,ALL( Full table scan );SIMPLE( Simple query );RANGE( Range queries )……

  • table: Choose the target

  • possible_keys: Possible indexes ( The index entries that the optimizer may select )

  • key: Index actually used ( it is to be noted that , If key by NULL Or it's not the index item you expect to see , It needs to be dealt with )

  • key_len: Index length ( Need to pay attention to ), The actual index length used , This item is for the federated index , Because there is a situation that not all union indexes are applied , The index length is compared with the defined length of the joint index

  • rows: Number of lines scanned ( Need to pay attention to ), In theory, the more you scan , The greater the performance consumption ( Be careful , Not the actual number of data rows, but the target data )

  • extra: Additional information ( Need to pay attention to )Using temporary ( Use temporary table );Using filesort ( Using file sorting );Using index( Adopt overlay index );Using join buffer (Block Nested Loop) BNL Optimize , If this item appears, it means multiple tables JOIN The connection did not go through the index

SQL Specific optimization ideas

   Add index optimization slow log

   When adding an index , You need to pay attention to the following things :

 

  • Avoid using functions in index fields , Try to complete the calculation on the program side ;

  • Avoid implicit conversions , Pay attention to the type difference of conditional query , For example, string types need quotation marks ;

  • order by Fields need to be indexed , Otherwise it will happen filesort;

  • When the cost of full table scanning is lower than the cost of using index , You need to reselect the condition option with large discrimination ;

  • Due to inaccurate metadata, the optimizer makes a wrong choice , Metadata collection and statistics need to be done manually ;

  • The order of use of the federated index is based on the order in which the index fields are created .

   besides , For multi table associated query SQL I also offer you some suggestions :

  • For the statement of multi table associated query, you must add an index in the connection field , It's very important ;

  • Always small watch drives big watch , Choose your driver table reasonably .

   You know, the goal of optimization is to minimize JOIN in Nested Loop The number of cycles , To ensure that “ Always drive large result sets with small result sets ( This is important )”.A JOIN B, among ,A The driver for ,A Each line and B    Cycle through JOIN, See if the conditions are met , So when A For small result sets , Faster , that :

  • Try not to nest too many JOIN sentence , The more connected tables , The more performance consumption , The more complex the business will be ,MySQL No Oracle, This requires you to remember ;

  • If the character sets of different tables of multi table joint query are inconsistent , Will cause the connection field index to fail .

   Last , You also need to pay attention to these two points when adding indexes :

  • Suggest using pt-osc、gh-ost And other tools to add indexes , This enables the execution of DDL Statement does not block the table ;

  • It is necessary to operate in the low peak period of business , Try to avoid affecting the business .

   Optimize slow logs by splitting hot and cold data

 

   You may be right “ A scheme to optimize slow logs by splitting hot and cold data ” Feel strange , But actually , This scheme is very practical , Especially suitable “ The super large table cannot add a valid index for the time being ”, The super large table is formed due to the continuous insertion of historical data , Later businesses need to query some specific conditions , And the discrimination of these specific conditions is relatively low , Even adding indexes is inefficient    It won't improve too much .

 

   such as A The system only needs nearly a year's data , But this scan condition can't add the appropriate index , So archive the previous data , Under certain conditions , It can effectively reduce the number of scanning lines , Greatly speed up SQL Statement execution time .

 

   Split hot and cold data , Slow logs for specific scenarios are effective , It is also conducive to data management , According to my experience , You can set up scheduled tasks , According to every day / Once a week / Monthly frequency , Specify data archiving during low peak periods , After the execution is completed, the mail / Wechat notification is enough .

 

 

copyright notice
author[Wall view],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/01/202201262356555645.html

Random recommended