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

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

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.