Shulou(Shulou.com)06/01 Report--

Background:

Business feedback makes a DDL operation on the master database, and the execution time is very fast, but the slave keeps reporting the master-slave delay alarm.

Analysis:

1. Looking at the current active thread on the host, it is found that the slave playback process is in Waiting for table metadata lock, but no other suspicious processes are found.

-+- -+ | Id | User | Host | db | Command | Time | State | Info | +-- -+ | 6 | system user | | NULL | Connect | 3649022 | | Waiting for master to send event | NULL | | 7 | system user | | testtt | Connect | 9 | Waiting for table metadata lock | alter table vault add column aa int | | 181873 | vector | 127.0.0.1 purl 41634 | testtt | Query | 0 | starting | show processlist | +-| -+

two。 Looking at the uncommitted transactions of the system, it is found that a business process opened the transaction but never committed.

+- -- + | host | user | time | trx_mysql_thread_id | trx_started | left (p.info 100) | +- -- +-+ | 127.0.0.1 41634 | vector | 0 | | 181873 | 2020-04-23 20:41:29 | select p.host | P.userdanagerp.timestationt.trxkeeper mysqlthreaded.trxrecording startedleft (p.info) From information_sc | +-+-- -+

3. After manual KILL dropped this thread, the business returned to normal.

Summary: slave computers can also display open transactions by setting set autocommit=0 and other ways, thus blocking slave playback threads.

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.