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

Saturday, April 1, 2017

After adding two fields to the main database of a business, the business side reported that the new field could not be seen from the library after 30 minutes.

Execute show slave status on slave\ G as shown below

Show porcesslist; is shown in the following figure:

In figure 2 above, you can see that there is a large delay, and the alter operation on the slave library has been waiting for metadata lock and is in a blocking state.

Solution:

Use SELECT * FROM information_schema.innodb_trx\ G to find the problem caused by the transaction that was not committed:

Kill2359; can just kill the thread.

After killing this thread, the show slave status\ G master-slave delay immediately dropped, and the show processlist was no longer in a locked state. [show slave status\ G even if it is a lock, that is, a short time system lock]

If we use zabbix's percona monitoring, we can adjust the threshold of the relevant triggers, as shown below:

The default is 100 on the template. Usually only alter table or select.. Operations such as for update will cause LOCK, so if the lock thread exceeds 50 in normal business, you need to pay attention to the situation.

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.