Как отслеживать большие транзакции в серии операций по эксплуатации и обслуживанию MySQL

задняя часть MySQL Эксплуатация и техническое обслуживание

long transaction

задний план

Вы когда-нибудь сталкивались с такой ситуацией?

  1. Определенный SQL выполняется очень медленно, в результате чего вся транзакция находится в стадии выполнения.
  2. SQL определенного сеанса был выполнен, но фиксации нет, и он находился в стадии ожидания
  3. Сессия находится в стадии ожидания блокировки и не завершена

Выше, большинство причин вызвано крупными событиями, поговорим на смежные темы

ключевые слова

  • окрестности
1. MySQL5.7.22 
	低版本MySQL这边不再考虑，就像还有使用SAS盘的公司一样，费时费力，MySQL5.7+ 标配
2. InnoDB存储引擎
3. CentOS 6
  • Соответствующие характеристики крупных транзакций
1. transaction开启到结束的时间非常长，我们这边举例为10s
2. 正在执行的事务
3. 未提交的事务

настоящий бой

  • Как контролировать эти транзакции в процессе
1. select * from sys.processlist
2. show processlist
3. select * from information_schema.processlist
4. select * from sys.session
5. select * from information_schema.innodb_trx;
6. select * from performance_schema.events_statements_current
  • Как отслеживать незафиксированные транзакции
select * from information_schema.innodb_trx
  • Как совместить два
select trx_id,INNODB_TRX.trx_state,INNODB_TRX.trx_started,se.conn_id as processlist_id,trx_lock_memory_bytes,se.user,se.command,se.state,se.current_statement,se.last_statement from information_schema.INNODB_TRX,sys.session as se where trx_mysql_thread_id=conn_id;
+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+
| trx_id  | trx_state | trx_started         | processlist_id | trx_lock_memory_bytes | user | command | state    | current_statement                 | last_statement                    |
+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+
| 1592104 | LOCK WAIT | 2018-06-26 11:51:17 |              3 |                  1136 | NULL | Query   | updating | update lc_1 set id=4 where id = 1 | NULL                              |
| 1592100 | RUNNING   | 2018-06-26 11:49:08 |              2 |                  1136 | NULL | Sleep   | NULL     | NULL                              | update lc_1 set id=3 where id = 1 |
+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+

Как видите, через это можно сразу указать, что оператор транзакции находится на рабочей фазе, а какие транзакции находятся в фазе употребления блокировки. Если мы столкнулись с этой ситуацией, что нам делать?
Вы умничка, по trx_started обязательно будете искать подсказки, но если это в среде репродукции, то это очень сложное и занятное дело
Но это не имеет значения, у нас еще есть артефакты, которые можно использовать.

  • Как быстро решить проблему ожидания блокировки
dba:sys> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2018-06-26 11:49:58
                    wait_age: 00:00:03
               wait_age_secs: 3
                locked_table: `lc`.`lc_1`
                locked_index: GEN_CLUST_INDEX
                 locked_type: RECORD
              waiting_trx_id: 1592102
         waiting_trx_started: 2018-06-26 11:49:58
             waiting_trx_age: 00:00:03
     waiting_trx_rows_locked: 2
   waiting_trx_rows_modified: 0
                 waiting_pid: 3
               waiting_query: update lc_1 set id=4 where id = 1
             waiting_lock_id: 1592102:32:3:4
           waiting_lock_mode: X
             blocking_trx_id: 1592100
                blocking_pid: 2
              blocking_query: NULL
            blocking_lock_id: 1592100:32:3:4
          blocking_lock_mode: X
        blocking_trx_started: 2018-06-26 11:49:08
            blocking_trx_age: 00:00:53
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2

В конце концов, MySQL очень внимателен, и даже оператор kill SQL создается, вам нужно только скопировать и вставить его.

Если вы будете внимательны, вы обнаружите, что с помощью innodb_lock_waits вы можете видеть только заблокированный оператор, но не можете видеть, какой оператор запроса владеет блокировкой.Почему это?

Не продавайте, потому что в транзакции с блокировкой может быть несколько операторов запроса, или она может быть выполнена, но нет фиксации, поэтому все операторы запроса не могут быть переданы.

тогда что нам делать?哈哈，如果幸运的话，你可以根据我上述的案例 current_statement，last_statement 得到答案。

Иными словами, даже если не нашли ответ на вопрос, это не мешает вам решать текущие проблемы Казахстана.

Суммировать

  1. MySQL 5.7 незаметно предоставляет множество практических инструментов и новых функций, которые требуют от администраторов баз данных копаться и исследовать. Вкопайте, казалось бы, ничем не примечательные функции в черное оружие, и вы сможете стать блестящим Top5 MySQLer
  2. Если вы хотите делать хорошую работу, вы должны сначала заточить свои инструменты
Категории