MySQL:slave delays a list of foreign key checks and self-incrementing locks

There is not much readability in this article, it is entirely my own notes. 1. Phenomenon

Big delay, big thing.

Table structure

Image.png

No IO

Image.png

SQL THREAD occupies 100% of CPU

Image.png

2. Pscak sampling

Sample 30 points

Foreign key check accounts for 70%

Image.png

Self-increasing lock acquisition accounts for 30%

Image.png

Third, self-adding lock acquisition logic

The following logic is actually the function of the innodb_autoinc_lock_mode parameter.

Switch (lock_mode) {case AUTOINC_NO_LOCKING://innodb_autoinc_lock_mode=2 / * Acquire only the AUTOINC mutex. * / dict_table_autoinc_lock (masked prebuild-> table); break; case AUTOINC_NEW_STYLE_LOCKING: / / innodb_autoinc_lock_mode=1 note that no break cleverly completes the logic / * For simple (single/multi) row INSERTs, we fallback to the old style only if another transaction has already acquired the AUTOINC lock on behalf of a LOAD FILE or INSERT. SELECT etc. Type of statement. * / if (thd_sql_command (m_user_thd) = = SQLCOM_INSERT | | thd_sql_command (m_user_thd) = = SQLCOM_REPLACE) {dict_table_t* ib_table = masked prebuild-> table; / * Acquire the AUTOINC mutex. * / dict_table_autoinc_lock (ib_table); / * We need to check that another transaction isn't already holding the AUTOINC lock on the table. * / if (ib_table- > n_waiting_or_granted_auto_inc_locks) {/ * Release the mutex to avoid deadlocks. * / dict_table_autoinc_unlock (ib_table);} else {break;}} / * Fall through to old style locking. * / case AUTOINC_OLD_STYLE_LOCKING://innodb_autoinc_lock_mode=0 triggers DBUG_EXECUTE_IF ("die_if_autoinc_old_lock_style_used", ut_ad (0);); error = row_lock_table_autoinc_for_mysql (m_prebuilt); / / self-incrementing lock if (error = = DB_SUCCESS) {/ * Acquire the AUTOINC mutex on the table on this function. * / dict_table_autoinc_lock (masked prebuild-> table);} break; default: ut_error;}

Binlog row format, innodb_autoinc_lock_mode=1 is not supposed to trigger row_lock_table_autoinc_for_mysql plus self-adding lock. I don't know why. Currently know:

If the master library statement mode, slave library innodb_autoinc_lock_mode=1, insert select will definitely trigger.

If you innodb_autoinc_lock_mode=0 from the library, it will definitely trigger.

But none of them are satisfied. Doubt.

IV. Plan

Delete foreign key

Innodb_autoinc_lock_mode is set to 2, and logically it will not do row_lock_table_autoinc_for_mysql.

Author Wechat:

Wechat .jpg