revision-id: a116e7878696cf1e34de93eb2b8ca06db4b776bf (mariadb-10.1.34-11-ga116e787869) parent(s): be5698265a4195586142d1a34fdd1cce9d95d8a1 author: Jan Lindström committer: Jan Lindström timestamp: 2018-07-25 08:21:25 +0300 message: MDEV-15822: WSREP: BF lock wait long for trx In Galera BF (brute force) transactions may not wait for lock requests and normally BF-transaction would select transaction holding conflicting locks as a victim for rollback. However, background statistic calculation transaction is InnoDB internal transaction and it has no thd i.e. it can't be selected as a victim. If background statistics calculation transaction holds conflicting locks to statistics tables it will cause BF lock wait long error message. Correct way to handle background statistics calculation is to acquire thd for transaction but that change is too big for GA-releases and there are other reported problems on background statistics calculation. This fix avoids adding a table to background statistics calculation if --- .../r/galera_bf_background_statistics.result | 95 ++++++++++++++++++++++ .../galera/t/galera_bf_background_statistics.opt | 1 + .../galera/t/galera_bf_background_statistics.test | 49 +++++++++++ storage/innobase/row/row0mysql.cc | 25 +++++- 4 files changed, 167 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/galera/r/galera_bf_background_statistics.result b/mysql-test/suite/galera/r/galera_bf_background_statistics.result new file mode 100644 index 00000000000..dc367b065f6 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_bf_background_statistics.result @@ -0,0 +1,95 @@ +SELECT @@innodb_stats_persistent; +@@innodb_stats_persistent +1 +CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 INTEGER DEFAULT NULL) ENGINE=InnoDB; +INSERT INTO t1(f1) values (NULL); +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +SELECT count(1) from t1; +count(1) +16384 +SET AUTOCOMMIT=OFF; +INSERT INTO t1 VALUES (9999999,NULL); +SELECT SLEEP(1000);; +ALTER TABLE t1 CHANGE f2 f2 INTEGER NOT NULL DEFAULT 1; +Warnings: +Warning 1265 Data truncated for column 'f2' at row 1 +Warning 1265 Data truncated for column 'f2' at row 2 +Warning 1265 Data truncated for column 'f2' at row 3 +Warning 1265 Data truncated for column 'f2' at row 4 +Warning 1265 Data truncated for column 'f2' at row 5 +Warning 1265 Data truncated for column 'f2' at row 6 +Warning 1265 Data truncated for column 'f2' at row 7 +Warning 1265 Data truncated for column 'f2' at row 8 +Warning 1265 Data truncated for column 'f2' at row 9 +Warning 1265 Data truncated for column 'f2' at row 10 +Warning 1265 Data truncated for column 'f2' at row 11 +Warning 1265 Data truncated for column 'f2' at row 12 +Warning 1265 Data truncated for column 'f2' at row 13 +Warning 1265 Data truncated for column 'f2' at row 14 +Warning 1265 Data truncated for column 'f2' at row 15 +Warning 1265 Data truncated for column 'f2' at row 16 +Warning 1265 Data truncated for column 'f2' at row 17 +Warning 1265 Data truncated for column 'f2' at row 18 +Warning 1265 Data truncated for column 'f2' at row 19 +Warning 1265 Data truncated for column 'f2' at row 20 +Warning 1265 Data truncated for column 'f2' at row 21 +Warning 1265 Data truncated for column 'f2' at row 22 +Warning 1265 Data truncated for column 'f2' at row 23 +Warning 1265 Data truncated for column 'f2' at row 24 +Warning 1265 Data truncated for column 'f2' at row 25 +Warning 1265 Data truncated for column 'f2' at row 26 +Warning 1265 Data truncated for column 'f2' at row 27 +Warning 1265 Data truncated for column 'f2' at row 28 +Warning 1265 Data truncated for column 'f2' at row 29 +Warning 1265 Data truncated for column 'f2' at row 30 +Warning 1265 Data truncated for column 'f2' at row 31 +Warning 1265 Data truncated for column 'f2' at row 32 +Warning 1265 Data truncated for column 'f2' at row 33 +Warning 1265 Data truncated for column 'f2' at row 34 +Warning 1265 Data truncated for column 'f2' at row 35 +Warning 1265 Data truncated for column 'f2' at row 36 +Warning 1265 Data truncated for column 'f2' at row 37 +Warning 1265 Data truncated for column 'f2' at row 38 +Warning 1265 Data truncated for column 'f2' at row 39 +Warning 1265 Data truncated for column 'f2' at row 40 +Warning 1265 Data truncated for column 'f2' at row 41 +Warning 1265 Data truncated for column 'f2' at row 42 +Warning 1265 Data truncated for column 'f2' at row 43 +Warning 1265 Data truncated for column 'f2' at row 44 +Warning 1265 Data truncated for column 'f2' at row 45 +Warning 1265 Data truncated for column 'f2' at row 46 +Warning 1265 Data truncated for column 'f2' at row 47 +Warning 1265 Data truncated for column 'f2' at row 48 +Warning 1265 Data truncated for column 'f2' at row 49 +Warning 1265 Data truncated for column 'f2' at row 50 +Warning 1265 Data truncated for column 'f2' at row 51 +Warning 1265 Data truncated for column 'f2' at row 52 +Warning 1265 Data truncated for column 'f2' at row 53 +Warning 1265 Data truncated for column 'f2' at row 54 +Warning 1265 Data truncated for column 'f2' at row 55 +Warning 1265 Data truncated for column 'f2' at row 56 +Warning 1265 Data truncated for column 'f2' at row 57 +Warning 1265 Data truncated for column 'f2' at row 58 +Warning 1265 Data truncated for column 'f2' at row 59 +Warning 1265 Data truncated for column 'f2' at row 60 +Warning 1265 Data truncated for column 'f2' at row 61 +Warning 1265 Data truncated for column 'f2' at row 62 +Warning 1265 Data truncated for column 'f2' at row 63 +Warning 1265 Data truncated for column 'f2' at row 64 +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +wsrep_local_aborts_increment +1 +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.opt b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt new file mode 100644 index 00000000000..f9b1414a974 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt @@ -0,0 +1 @@ +--innodb_stats_persistent=ON diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.test b/mysql-test/suite/galera/t/galera_bf_background_statistics.test new file mode 100644 index 00000000000..1030e8d4154 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.test @@ -0,0 +1,49 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc + +# +# Test a local transaction being aborted by a slave one while it is running a SLEEP() +# + +SELECT @@innodb_stats_persistent; + +CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 INTEGER DEFAULT NULL) ENGINE=InnoDB; +INSERT INTO t1(f1) values (NULL); +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +SELECT count(1) from t1; + +--connection node_2 +SET AUTOCOMMIT=OFF; +--let $wsrep_local_bf_aborts_before = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` +INSERT INTO t1 VALUES (9999999,NULL); +--send SELECT SLEEP(1000); + +--connection node_1 +ALTER TABLE t1 CHANGE f2 f2 INTEGER NOT NULL DEFAULT 1; + +--connection node_2 +--error ER_LOCK_DEADLOCK +--reap + +--let $wsrep_local_bf_aborts_after = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +# Check that wsrep_local_bf_aborts has been incremented by exactly 1 +--disable_query_log +--eval SELECT $wsrep_local_bf_aborts_after - $wsrep_local_bf_aborts_before = 1 AS wsrep_local_aborts_increment; +--enable_query_log + +DROP TABLE t1; + diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 6a971a973f5..963d939380c 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -70,6 +70,13 @@ Created 9/17/2000 Heikki Tuuri #include "ha_prototypes.h" #include <algorithm> +#ifdef WITH_WSREP +#include "mysql/service_wsrep.h" +#include "wsrep.h" +#include "log.h" +#include "wsrep_mysqld.h" +#endif + /** Provide optional 4.x backwards compatibility for 5.0 and above */ UNIV_INTERN ibool row_rollback_on_timeout = FALSE; @@ -1093,6 +1100,7 @@ UNIV_INLINE void row_update_statistics_if_needed( /*============================*/ + trx_t* trx, dict_table_t* table) /*!< in: table */ { ib_uint64_t counter; @@ -1114,6 +1122,16 @@ row_update_statistics_if_needed( if (counter > n_rows / 10 /* 10% */ && dict_stats_auto_recalc_is_enabled(table)) { +#ifdef WITH_WSREP + if (wsrep_on(trx->mysql_thd) && + wsrep_thd_is_BF(trx->mysql_thd, FALSE)) { + WSREP_DEBUG("Avoiding background statistics" + " calculation for table %s", + table->name); + return; + } +#endif /* WITH_WSREP */ + dict_stats_recalc_pool_add(table); table->stat_modified_counter = 0; } @@ -1537,7 +1555,8 @@ row_insert_for_mysql( ut_memcpy(prebuilt->row_id, node->row_id_buf, DATA_ROW_ID_LEN); } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); + trx->op_info = ""; return(err); @@ -1921,7 +1940,7 @@ row_update_for_mysql( that changes indexed columns, UPDATEs that change only non-indexed columns would not affect statistics. */ if (node->is_delete || !(node->cmpl_info & UPD_NODE_NO_ORD_CHANGE)) { - row_update_statistics_if_needed(prebuilt->table); + row_update_statistics_if_needed(trx, prebuilt->table); } else { /* Update the table modification counter even when non-indexed columns change if statistics is initialized. */ @@ -2158,7 +2177,7 @@ row_update_cascade_for_mysql( } } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); return(err); }