Why to avoid big business and how to solve big business?

What is a big deal

The running time is relatively long, and the transaction that has not been committed for a long time can be called a large transaction

The cause of big business

The operation data is quite large
A lot of lock competition
There are other non DB time-consuming operations in the transaction
。。。

The impact of big business

In the case of concurrency, the database connection pool is easy to burst
Lock too much data, causing a lot of blocking and lock timeout
Long execution time, easy to cause master-slave delay
Rollback takes a long time
Undo log inflation
。。。

How to query big business

notesThe operation of SQL in this paper is based on MySQL 5.7

Take the transaction whose query execution time exceeds 10 seconds as an example

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>10

How to avoid big business

General solution

In a transaction, avoid processing too much data at one time
In a transaction, try to avoid unnecessary queries
In a transaction, avoid time-consuming operations, resulting in transaction timeout. Some non DB operations, such as RPC calls and message queuing operations, should be put out of the transaction as far as possible

Solution based on mysql5.7

In InnoDB transactions, row locks are added when they are needed, but they are not released immediately when they are not needed. Instead, they are not released until the end of the transaction.If you need to lock multiple rows in a transaction, put back the locks that are most likely to cause lock conflicts and affect concurrency
Through setmax_ EXECUTION_ Time command to control the maximum query time of each statement to avoid the accidental query time of a single statement
Monitoring information_ schema.Innodb_ TRX table, set long transaction threshold, alarm and / or kill if exceeding
In the business function test phase, it is required to output all general_ Log, analyze log behavior and find problems in advance
Setting InnoDB_ undo_ The table spaces value separates undo log into independent table spaces. If the rollback segment is too large due to large transaction, it is more convenient to clean up after setting

Appendix query transaction related statements

notesAll SQL statements are based on MySQL 5.7

#Query all running transactions and running time
select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t

#SQL to query transaction details and execute
select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join information_schema.PROCESSLIST b
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

#Query all historical SQL records of transaction execution
SELECT
  ps.id 'PROCESS ID',
  ps.USER,
  ps.HOST,
  esh.EVENT_ID,
  trx.trx_started,
  esh.event_name 'EVENT NAME',
  esh.sql_text 'SQL',
  ps.time 
FROM
  PERFORMANCE_SCHEMA.events_statements_history esh
  JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
  JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
  LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id 
WHERE
  trx.trx_id IS NOT NULL 
  AND ps.USER != 'SYSTEM_USER' 
ORDER BY
  esh.EVENT_ID;
  
 #Simple query transaction lock
 select * from sys.innodb_lock_waits
 
 #Query transaction lock details
 SELECT
  tmp.*,
  c.SQL_Text blocking_sql_text,
  p.HOST blocking_host 
FROM
  (
  SELECT
    r.trx_state wating_trx_state,
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_Id waiting_thread,
    r.trx_query waiting_query,
    b.trx_state blocking_trx_state,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query 
  FROM
    information_schema.innodb_lock_waits w
    INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id 
  ) tmp,
  information_schema.PROCESSLIST p,
  PERFORMANCE_SCHEMA.events_statements_current c,
  PERFORMANCE_SCHEMA.threads t 
WHERE
  tmp.blocking_thread = p.id 
  AND t.thread_id = c.THREAD_ID 
  AND t.PROCESSLIST_ID = p.id

reference resources

MySQL long transaction

Interviewer: do you know what problems big business brings and how to solve them?