MDEV-6926: innodb_rows_updated is misleading on slav

Merged Facebook commit dd2d11be7aaf3be270e740fb95cbc4eacb52f4d7
authored by Rongrong Zhong from https://github.com/facebook/mysql-5.6

This fixes MySQL Bug #68220 innodb_rows_updated is misleading on slave
http://bugs.mysql.com/bug.php?id=68220

Added innodb_system_rows_read/inserted/updated/deleted counters
that are the equivalent of innodb_rows_* but that only account for
changes made to system databases (mysql, information_schame and
preformance_schema). These counters will be used on slaves to
differentiated the updates made on system databases from those made on
user databases.

innodb_rows_* status counters are not updated when innodb_system_rows_*
are updated.

https://github.com/facebook/mysql-5.6/commit/dd2d11be7aaf3be270e740fb95cbc4eacb52f4d7

9 files changed, 221 insertions, 11 deletions

diff --git a/storage/innobase/api/api0api.cc b/storage/innobase/api/api0api.cc
index 1c1ae22fa3f..4788b78ebd7 100644
--- a/storage/innobase/api/api0api.cc
+++ b/storage/innobase/api/api0api.cc
@@ -1531,7 +1531,11 @@ ib_execute_insert_query_graph(
 
 		dict_table_n_rows_inc(table);
 
-		srv_stats.n_rows_inserted.inc();
+		if (table->is_system_db) {
+			srv_stats.n_system_rows_inserted.inc();
+		} else {
+			srv_stats.n_rows_inserted.inc();
+		}
 	}
 
 	trx->op_info = "";
@@ -1885,9 +1889,17 @@ ib_execute_update_query_graph(
 
 			dict_table_n_rows_dec(table);
 
-			srv_stats.n_rows_deleted.inc();
+			if (table->is_system_db) {
+				srv_stats.n_system_rows_deleted.inc();
+			} else {
+				srv_stats.n_rows_deleted.inc();
+			}
 		} else {
-			srv_stats.n_rows_updated.inc();
+			if (table->is_system_db) {
+				srv_stats.n_system_rows_updated.inc();
+			} else {
+				srv_stats.n_rows_updated.inc();
+			}
 		}
 
 	} else if (err == DB_RECORD_NOT_FOUND) {
diff --git a/storage/innobase/dict/dict0mem.cc b/storage/innobase/dict/dict0mem.cc
index 885627a61bc..fefa34ead23 100644
--- a/storage/innobase/dict/dict0mem.cc
+++ b/storage/innobase/dict/dict0mem.cc
@@ -53,6 +53,14 @@ Created 1/8/1996 Heikki Tuuri
 UNIV_INTERN mysql_pfs_key_t	autoinc_mutex_key;
 #endif /* UNIV_PFS_MUTEX */
 
+/** System databases */
+static const char* innobase_system_databases[] = {
+	"mysql/",
+	"information_schema/",
+	"performance_schema/",
+	NullS
+};
+
 /**********************************************************************//**
 Creates a table memory object.
 @return	own: table object */
@@ -85,6 +93,7 @@ dict_mem_table_create(
 	table->flags2 = (unsigned int) flags2;
 	table->name = static_cast<char*>(ut_malloc(strlen(name) + 1));
 	memcpy(table->name, name, strlen(name) + 1);
+	table->is_system_db = dict_mem_table_is_system(table->name);
 	table->space = (unsigned int) space;
 	table->n_cols = (unsigned int) (n_cols + DATA_N_SYS_COLS);
 
@@ -131,6 +140,36 @@ dict_mem_table_create(
 }
 
 /****************************************************************//**
+Determines if a table belongs to a system database
+@return */
+UNIV_INTERN
+bool
+dict_mem_table_is_system(
+/*================*/
+	char	*name)		/*!< in: table name */
+{
+	ut_ad(name);
+
+	/* table has the following format: database/table
+	and some system table are of the form SYS_* */
+	if (strchr(name, '/')) {
+		int table_len = strlen(name);
+		const char *system_db;
+		int i = 0;
+		while ((system_db = innobase_system_databases[i++])
+			&& (system_db != NullS)) {
+			int len = strlen(system_db);
+			if (table_len > len && !strncmp(name, system_db, len)) {
+				return true;
+			}
+		}
+		return false;
+	} else {
+		return true;
+	}
+}
+
+/****************************************************************//**
 Free a table memory object. */
 UNIV_INTERN
 void
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 1d13b23715c..166b2e6a79f 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -669,6 +669,14 @@ static SHOW_VAR innodb_status_variables[]= {
   (char*) &export_vars.innodb_rows_read,		  SHOW_LONG},
   {"rows_updated",
   (char*) &export_vars.innodb_rows_updated,		  SHOW_LONG},
+  {"system_rows_deleted",
+  (char*) &export_vars.innodb_system_rows_deleted, SHOW_LONG},
+  {"system_rows_inserted",
+  (char*) &export_vars.innodb_system_rows_inserted, SHOW_LONG},
+  {"system_rows_read",
+  (char*) &export_vars.innodb_system_rows_read, SHOW_LONG},
+  {"system_rows_updated",
+  (char*) &export_vars.innodb_system_rows_updated, SHOW_LONG},
   {"num_open_files",
   (char*) &export_vars.innodb_num_open_files,		  SHOW_LONG},
   {"truncated_status_writes",
@@ -7892,7 +7900,13 @@ ha_innobase::index_read(
 	case DB_SUCCESS:
 		error = 0;
 		table->status = 0;
-		srv_stats.n_rows_read.add((size_t) prebuilt->trx->id, 1);
+		if (prebuilt->table->is_system_db) {
+			srv_stats.n_system_rows_read.add(
+				(size_t) prebuilt->trx->id, 1);
+		} else {
+			srv_stats.n_rows_read.add(
+				(size_t) prebuilt->trx->id, 1);
+		}
 		break;
 	case DB_RECORD_NOT_FOUND:
 		error = HA_ERR_KEY_NOT_FOUND;
@@ -8168,7 +8182,13 @@ ha_innobase::general_fetch(
 	case DB_SUCCESS:
 		error = 0;
 		table->status = 0;
-		srv_stats.n_rows_read.add((size_t) prebuilt->trx->id, 1);
+		if (prebuilt->table->is_system_db) {
+			srv_stats.n_system_rows_read.add(
+				(size_t) prebuilt->trx->id, 1);
+		} else {
+			srv_stats.n_rows_read.add(
+				(size_t) prebuilt->trx->id, 1);
+		}
 		break;
 	case DB_RECORD_NOT_FOUND:
 		error = HA_ERR_END_OF_FILE;
diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h
index f5326fbdb41..802d7b3af53 100644
--- a/storage/innobase/include/dict0mem.h
+++ b/storage/innobase/include/dict0mem.h
@@ -256,6 +256,14 @@ dict_mem_table_create(
 	ulint		n_cols,		/*!< in: number of columns */
 	ulint		flags,		/*!< in: table flags */
 	ulint		flags2);	/*!< in: table flags2 */
+/**********************************************************************//**
+Determines if a table belongs to a system database
+@return */
+UNIV_INTERN
+bool
+dict_mem_table_is_system(
+/*==================*/
+	char	*name);		/*!< in: table name */
 /****************************************************************//**
 Free a table memory object. */
 UNIV_INTERN
@@ -880,6 +888,10 @@ struct dict_table_t{
 				the string contains n_cols, it will be
 				allocated from a temporary heap.  The final
 				string will be allocated from table->heap. */
+	bool		is_system_db;
+				/*!< True if the table belongs to a system
+				database (mysql, information_schema or
+				performance_schema) */
 #ifndef UNIV_HOTBACKUP
 	hash_node_t	name_hash; /*!< hash chain node */
 	hash_node_t	id_hash; /*!< hash chain node */
diff --git a/storage/innobase/include/srv0mon.h b/storage/innobase/include/srv0mon.h
index e2ab81bf53a..2d90f47eefe 100644
--- a/storage/innobase/include/srv0mon.h
+++ b/storage/innobase/include/srv0mon.h
@@ -369,6 +369,10 @@ enum monitor_id_t {
 	MONITOR_OLVD_ROW_INSERTED,
 	MONITOR_OLVD_ROW_DELETED,
 	MONITOR_OLVD_ROW_UPDTATED,
+	MONITOR_OLVD_SYSTEM_ROW_READ,
+	MONITOR_OLVD_SYSTEM_ROW_INSERTED,
+	MONITOR_OLVD_SYSTEM_ROW_DELETED,
+	MONITOR_OLVD_SYSTEM_ROW_UPDATED,
 
 	/* Data DDL related counters */
 	MONITOR_MODULE_DDL_STATS,
diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
index 6df544f56d7..aebc622cb20 100644
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -126,6 +126,18 @@ struct srv_stats_t {
 
 	/** Number of rows inserted */
 	ulint_ctr_64_t		n_rows_inserted;
+
+	/** Number of system rows read. */
+	ulint_ctr_64_t		n_system_rows_read;
+
+	/** Number of system rows updated */
+	ulint_ctr_64_t		n_system_rows_updated;
+
+	/** Number of system rows deleted */
+	ulint_ctr_64_t		n_system_rows_deleted;
+
+	/** Number of system rows inserted */
+	ulint_ctr_64_t		n_system_rows_inserted;
 };
 
 extern const char*	srv_main_thread_op_info;
@@ -846,6 +858,10 @@ struct export_var_t{
 	ulint innodb_rows_inserted;		/*!< srv_n_rows_inserted */
 	ulint innodb_rows_updated;		/*!< srv_n_rows_updated */
 	ulint innodb_rows_deleted;		/*!< srv_n_rows_deleted */
+	ulint innodb_system_rows_read; /*!< srv_n_system_rows_read */
+	ulint innodb_system_rows_inserted; /*!< srv_n_system_rows_inserted */
+	ulint innodb_system_rows_updated; /*!< srv_n_system_rows_updated */
+	ulint innodb_system_rows_deleted; /*!< srv_n_system_rows_deleted*/
 	ulint innodb_num_open_files;		/*!< fil_n_file_opened */
 	ulint innodb_truncated_status_writes;	/*!< srv_truncated_status_writes */
 	ulint innodb_available_undo_logs;       /*!< srv_available_undo_logs */
diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
index c1ed91a1e24..efc89ff1f34 100644
--- a/storage/innobase/row/row0mysql.cc
+++ b/storage/innobase/row/row0mysql.cc
@@ -1394,7 +1394,11 @@ error_exit:
 
 	que_thr_stop_for_mysql_no_error(thr, trx);
 
-	srv_stats.n_rows_inserted.add((size_t)trx->id, 1);
+	if (table->is_system_db) {
+		srv_stats.n_system_rows_inserted.add((size_t)trx->id, 1);
+	} else {
+		srv_stats.n_rows_inserted.add((size_t)trx->id, 1);
+	}
 
 	/* Not protected by dict_table_stats_lock() for performance
 	reasons, we would rather get garbage in stat_n_rows (which is
@@ -1784,9 +1788,20 @@ run_again:
 		with a latch. */
 		dict_table_n_rows_dec(prebuilt->table);
 
-		srv_stats.n_rows_deleted.add((size_t)trx->id, 1);
+		if (table->is_system_db) {
+			srv_stats.n_system_rows_deleted.add(
+				(size_t)trx->id, 1);
+		} else {
+			srv_stats.n_rows_deleted.add((size_t)trx->id, 1);
+		}
+
 	} else {
-		srv_stats.n_rows_updated.add((size_t)trx->id, 1);
+		if (table->is_system_db) {
+			srv_stats.n_system_rows_updated.add(
+				(size_t)trx->id, 1);
+		} else {
+			srv_stats.n_rows_updated.add((size_t)trx->id, 1);
+		}
 	}
 
 	/* We update table statistics only if it is a DELETE or UPDATE
@@ -2010,9 +2025,19 @@ run_again:
 		with a latch. */
 		dict_table_n_rows_dec(table);
 
-		srv_stats.n_rows_deleted.add((size_t)trx->id, 1);
+		if (table->is_system_db) {
+			srv_stats.n_system_rows_deleted.add(
+				(size_t)trx->id, 1);
+		} else {
+			srv_stats.n_rows_deleted.add((size_t)trx->id, 1);
+		}
 	} else {
-		srv_stats.n_rows_updated.add((size_t)trx->id, 1);
+		if (table->is_system_db) {
+			srv_stats.n_system_rows_updated.add(
+				(size_t)trx->id, 1);
+		} else {
+			srv_stats.n_rows_updated.add((size_t)trx->id, 1);
+		}
 	}
 
 	row_update_statistics_if_needed(table);
diff --git a/storage/innobase/srv/srv0mon.cc b/storage/innobase/srv/srv0mon.cc
index 64417b1e5fb..f29621bc90a 100644
--- a/storage/innobase/srv/srv0mon.cc
+++ b/storage/innobase/srv/srv0mon.cc
@@ -1165,6 +1165,26 @@ static monitor_info_t	innodb_counter_info[] =
 	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
 	 MONITOR_DEFAULT_START, MONITOR_OLVD_ROW_UPDTATED},
 
+	{"dml_system_reads", "dml", "Number of system rows read",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_OLVD_SYSTEM_ROW_READ},
+
+	{"dml_system_inserts", "dml", "Number of system rows inserted",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_OLVD_SYSTEM_ROW_INSERTED},
+
+	{"dml_system_deletes", "dml", "Number of system rows deleted",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_OLVD_SYSTEM_ROW_DELETED},
+
+	{"dml_system_updates", "dml", "Number of system rows updated",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_OLVD_SYSTEM_ROW_UPDATED},
+
 	/* ========== Counters for DDL operations ========== */
 	{"module_ddl", "ddl", "Statistics for DDLs",
 	 MONITOR_MODULE,
@@ -1683,6 +1703,26 @@ srv_mon_process_existing_counter(
 		value = srv_stats.n_rows_updated;
 		break;
 
+	/* innodb_system_rows_read */
+	case MONITOR_OLVD_SYSTEM_ROW_READ:
+		value = srv_stats.n_system_rows_read;
+		break;
+
+	/* innodb_system_rows_inserted */
+	case MONITOR_OLVD_SYSTEM_ROW_INSERTED:
+		value = srv_stats.n_system_rows_inserted;
+		break;
+
+	/* innodb_system_rows_deleted */
+	case MONITOR_OLVD_SYSTEM_ROW_DELETED:
+		value = srv_stats.n_system_rows_deleted;
+		break;
+
+	/* innodb_system_rows_updated */
+	case MONITOR_OLVD_SYSTEM_ROW_UPDATED:
+		value = srv_stats.n_system_rows_updated;
+		break;
+
 	/* innodb_row_lock_current_waits */
 	case MONITOR_OVLD_ROW_LOCK_CURRENT_WAIT:
 		value = srv_stats.n_lock_wait_current_count;
diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc
index 5b4f553cf42..f40576615d4 100644
--- a/storage/innobase/srv/srv0srv.cc
+++ b/storage/innobase/srv/srv0srv.cc
@@ -377,6 +377,10 @@ static ulint		srv_n_rows_inserted_old		= 0;
 static ulint		srv_n_rows_updated_old		= 0;
 static ulint		srv_n_rows_deleted_old		= 0;
 static ulint		srv_n_rows_read_old		= 0;
+static ulint		srv_n_system_rows_inserted_old	= 0;
+static ulint		srv_n_system_rows_updated_old	= 0;
+static ulint		srv_n_system_rows_deleted_old	= 0;
+static ulint		srv_n_system_rows_read_old	= 0;
 
 UNIV_INTERN ulint	srv_truncated_status_writes	= 0;
 UNIV_INTERN ulint	srv_available_undo_logs         = 0;
@@ -1110,6 +1114,11 @@ srv_refresh_innodb_monitor_stats(void)
 	srv_n_rows_deleted_old = srv_stats.n_rows_deleted;
 	srv_n_rows_read_old = srv_stats.n_rows_read;
 
+	srv_n_system_rows_inserted_old = srv_stats.n_system_rows_inserted;
+	srv_n_system_rows_updated_old = srv_stats.n_system_rows_updated;
+	srv_n_system_rows_deleted_old = srv_stats.n_system_rows_deleted;
+	srv_n_system_rows_read_old = srv_stats.n_system_rows_read;
+
 	mutex_exit(&srv_innodb_monitor_mutex);
 }
 
@@ -1301,11 +1310,33 @@ srv_printf_innodb_monitor(
 		/ time_elapsed,
 		((ulint) srv_stats.n_rows_read - srv_n_rows_read_old)
 		/ time_elapsed);
-
+	fprintf(file,
+		"Number of system rows inserted " ULINTPF
+		", updated " ULINTPF ", deleted " ULINTPF
+		", read " ULINTPF "\n",
+		(ulint) srv_stats.n_system_rows_inserted,
+		(ulint) srv_stats.n_system_rows_updated,
+		(ulint) srv_stats.n_system_rows_deleted,
+		(ulint) srv_stats.n_system_rows_read);
+	fprintf(file,
+		"%.2f inserts/s, %.2f updates/s,"
+		" %.2f deletes/s, %.2f reads/s\n",
+		((ulint) srv_stats.n_system_rows_inserted
+		 - srv_n_system_rows_inserted_old) / time_elapsed,
+		((ulint) srv_stats.n_system_rows_updated
+		 - srv_n_system_rows_updated_old) / time_elapsed,
+		((ulint) srv_stats.n_system_rows_deleted
+		 - srv_n_system_rows_deleted_old) / time_elapsed,
+		((ulint) srv_stats.n_system_rows_read
+		 - srv_n_system_rows_read_old) / time_elapsed);
 	srv_n_rows_inserted_old = srv_stats.n_rows_inserted;
 	srv_n_rows_updated_old = srv_stats.n_rows_updated;
 	srv_n_rows_deleted_old = srv_stats.n_rows_deleted;
 	srv_n_rows_read_old = srv_stats.n_rows_read;
+	srv_n_system_rows_inserted_old = srv_stats.n_system_rows_inserted;
+	srv_n_system_rows_updated_old = srv_stats.n_system_rows_updated;
+	srv_n_system_rows_deleted_old = srv_stats.n_system_rows_deleted;
+	srv_n_system_rows_read_old = srv_stats.n_system_rows_read;
 
 	fputs("----------------------------\n"
 	      "END OF INNODB MONITOR OUTPUT\n"
@@ -1460,6 +1491,17 @@ srv_export_innodb_status(void)
 
 	export_vars.innodb_rows_deleted = srv_stats.n_rows_deleted;
 
+	export_vars.innodb_system_rows_read = srv_stats.n_system_rows_read;
+
+	export_vars.innodb_system_rows_inserted =
+		srv_stats.n_system_rows_inserted;
+
+	export_vars.innodb_system_rows_updated =
+		srv_stats.n_system_rows_updated;
+
+	export_vars.innodb_system_rows_deleted =
+		srv_stats.n_system_rows_deleted;
+
 	export_vars.innodb_num_open_files = fil_n_file_opened;
 
 	export_vars.innodb_truncated_status_writes =