diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 42276ad..e031d1e0 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1751,14 +1751,14 @@ variable_value SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; variable_value {checked_valid} -SELECT variable_value - @innodb_rows_deleted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'; -variable_value - @innodb_rows_deleted_orig +SELECT sum(variable_value) - @innodb_rows_deleted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_deleted'; +sum(variable_value) - @innodb_rows_deleted_orig 1830 -SELECT variable_value - @innodb_rows_inserted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; -variable_value - @innodb_rows_inserted_orig +SELECT sum(variable_value) - @innodb_rows_inserted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_inserted'; +sum(variable_value) - @innodb_rows_inserted_orig 2749 -SELECT variable_value - @innodb_rows_updated_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; -variable_value - @innodb_rows_updated_orig +SELECT sum(variable_value) - @innodb_rows_updated_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_updated'; +sum(variable_value) - @innodb_rows_updated_orig 2916 SELECT variable_value - @innodb_row_lock_waits_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits'; variable_value - @innodb_row_lock_waits_orig diff --git a/mysql-test/suite/innodb/r/monitor.result b/mysql-test/suite/innodb/r/monitor.result index a9b8e7b..61dfc1d 100644 --- a/mysql-test/suite/innodb/r/monitor.result +++ b/mysql-test/suite/innodb/r/monitor.result @@ -228,6 +228,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_tables disabled ddl_online_create_index disabled ddl_pending_alter_table disabled @@ -453,6 +457,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -463,6 +471,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -473,6 +485,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -485,6 +501,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -495,6 +515,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -505,6 +529,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -515,6 +543,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -528,6 +560,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index d52b2b4..9d0b5fb 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -37,9 +37,9 @@ SET @innodb_thread_sleep_delay_orig = @@innodb_thread_sleep_delay; SET @innodb_thread_concurrency_orig = @@innodb_thread_concurrency; --disable_warnings -SET @innodb_rows_deleted_orig = (SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'); -SET @innodb_rows_inserted_orig = (SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'); -SET @innodb_rows_updated_orig = (SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'); +SET @innodb_rows_deleted_orig = (SELECT sum(variable_value) FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_deleted'); +SET @innodb_rows_inserted_orig = (SELECT sum(variable_value) FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_inserted'); +SET @innodb_rows_updated_orig = (SELECT sum(variable_value) FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_updated'); SET @innodb_row_lock_waits_orig = (SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits'); SET @innodb_row_lock_current_waits_orig = (SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits'); SET @innodb_row_lock_time_orig = (SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time'); @@ -1333,9 +1333,9 @@ drop table t1; SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total'; --replace_result 4096 {checked_valid} 8192 {checked_valid} 16384 {checked_valid} SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; -SELECT variable_value - @innodb_rows_deleted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'; -SELECT variable_value - @innodb_rows_inserted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; -SELECT variable_value - @innodb_rows_updated_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; +SELECT sum(variable_value) - @innodb_rows_deleted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_deleted'; +SELECT sum(variable_value) - @innodb_rows_inserted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_inserted'; +SELECT sum(variable_value) - @innodb_rows_updated_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_updated'; --enable_warnings # Test for row locks InnoDB status variables. diff --git a/mysql-test/suite/rpl/include/rpl_innodb_rows_counters.inc b/mysql-test/suite/rpl/include/rpl_innodb_rows_counters.inc new file mode 100644 index 0000000..b982a9f --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_innodb_rows_counters.inc @@ -0,0 +1,50 @@ +######################################### +# Author: Benjamin Renard benj@fb.com +# Date: 11/15/2013 +# Purpose: Showing the difference between current innodb rows stats and the ones recorded at the beginning of the test +# Requirements: Having @[master|slave]_[system_]rows_[read|inserted|deleted|updated] counters already created +######################################### + +--connection master +--echo ==========MASTER========== + +select variable_value into @rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @master_rows_read; +select variable_value into @rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @master_rows_updated; +select variable_value into @rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @master_rows_deleted; +select variable_value into @rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @master_rows_inserted; + +select variable_value into @system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @master_system_rows_read > 0; +select variable_value into @system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @master_system_rows_updated > 0; +select variable_value into @system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @master_system_rows_deleted > 0; +select variable_value into @system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @master_system_rows_inserted > 0; + +--sync_slave_with_master +--echo ==========SLAVE=========== + +select variable_value into @rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @slave_rows_read; +select variable_value into @rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @slave_rows_updated; +select variable_value into @rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @slave_rows_deleted; +select variable_value into @rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @slave_rows_inserted; + +select variable_value into @system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @slave_system_rows_read > 0; +select variable_value into @system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @slave_system_rows_updated > 0; +select variable_value into @system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @slave_system_rows_deleted > 0; +select variable_value into @system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @slave_system_rows_inserted > 0; + +--connection master diff --git a/mysql-test/suite/rpl/r/rpl_innodb_bug68220.result b/mysql-test/suite/rpl/r/rpl_innodb_bug68220.result new file mode 100644 index 0000000..8c8d336 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_innodb_bug68220.result @@ -0,0 +1,230 @@ +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +DROP TABLE IF EXISTS testdb.t1; +DROP DATABASE IF EXISTS testdb; +select variable_value into @master_rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select variable_value into @master_rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select variable_value into @master_rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select variable_value into @master_rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select variable_value into @master_system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select variable_value into @master_system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select variable_value into @master_system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select variable_value into @master_system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select variable_value into @slave_rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select variable_value into @slave_rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select variable_value into @slave_rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select variable_value into @slave_rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select variable_value into @slave_system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select variable_value into @slave_system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select variable_value into @slave_system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select variable_value into @slave_system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +CREATE DATABASE testdb; +USE testdb; +CREATE TABLE testdb.t1 (i int NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO testdb.t1 VALUES (1); +==========MASTER========== +select variable_value into @rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @master_rows_read; +@rows_read - @master_rows_read +0 +select variable_value into @rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @master_rows_updated; +@rows_updated - @master_rows_updated +0 +select variable_value into @rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @master_rows_deleted; +@rows_deleted - @master_rows_deleted +0 +select variable_value into @rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @master_rows_inserted; +@rows_inserted - @master_rows_inserted +1 +select variable_value into @system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @master_system_rows_read > 0; +@system_rows_read - @master_system_rows_read > 0 +1 +select variable_value into @system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @master_system_rows_updated > 0; +@system_rows_updated - @master_system_rows_updated > 0 +1 +select variable_value into @system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @master_system_rows_deleted > 0; +@system_rows_deleted - @master_system_rows_deleted > 0 +0 +select variable_value into @system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @master_system_rows_inserted > 0; +@system_rows_inserted - @master_system_rows_inserted > 0 +1 +==========SLAVE=========== +select variable_value into @rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @slave_rows_read; +@rows_read - @slave_rows_read +0 +select variable_value into @rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @slave_rows_updated; +@rows_updated - @slave_rows_updated +0 +select variable_value into @rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @slave_rows_deleted; +@rows_deleted - @slave_rows_deleted +0 +select variable_value into @rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @slave_rows_inserted; +@rows_inserted - @slave_rows_inserted +1 +select variable_value into @system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @slave_system_rows_read > 0; +@system_rows_read - @slave_system_rows_read > 0 +1 +select variable_value into @system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @slave_system_rows_updated > 0; +@system_rows_updated - @slave_system_rows_updated > 0 +1 +select variable_value into @system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @slave_system_rows_deleted > 0; +@system_rows_deleted - @slave_system_rows_deleted > 0 +0 +select variable_value into @system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @slave_system_rows_inserted > 0; +@system_rows_inserted - @slave_system_rows_inserted > 0 +1 +UPDATE t1 SET i=2 WHERE i=1; +==========MASTER========== +select variable_value into @rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @master_rows_read; +@rows_read - @master_rows_read +1 +select variable_value into @rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @master_rows_updated; +@rows_updated - @master_rows_updated +1 +select variable_value into @rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @master_rows_deleted; +@rows_deleted - @master_rows_deleted +0 +select variable_value into @rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @master_rows_inserted; +@rows_inserted - @master_rows_inserted +1 +select variable_value into @system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @master_system_rows_read > 0; +@system_rows_read - @master_system_rows_read > 0 +1 +select variable_value into @system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @master_system_rows_updated > 0; +@system_rows_updated - @master_system_rows_updated > 0 +1 +select variable_value into @system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @master_system_rows_deleted > 0; +@system_rows_deleted - @master_system_rows_deleted > 0 +0 +select variable_value into @system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @master_system_rows_inserted > 0; +@system_rows_inserted - @master_system_rows_inserted > 0 +1 +==========SLAVE=========== +select variable_value into @rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @slave_rows_read; +@rows_read - @slave_rows_read +1 +select variable_value into @rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @slave_rows_updated; +@rows_updated - @slave_rows_updated +1 +select variable_value into @rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @slave_rows_deleted; +@rows_deleted - @slave_rows_deleted +0 +select variable_value into @rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @slave_rows_inserted; +@rows_inserted - @slave_rows_inserted +1 +select variable_value into @system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @slave_system_rows_read > 0; +@system_rows_read - @slave_system_rows_read > 0 +1 +select variable_value into @system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @slave_system_rows_updated > 0; +@system_rows_updated - @slave_system_rows_updated > 0 +1 +select variable_value into @system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @slave_system_rows_deleted > 0; +@system_rows_deleted - @slave_system_rows_deleted > 0 +0 +select variable_value into @system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @slave_system_rows_inserted > 0; +@system_rows_inserted - @slave_system_rows_inserted > 0 +1 +DELETE FROM t1 WHERE i=2; +==========MASTER========== +select variable_value into @rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @master_rows_read; +@rows_read - @master_rows_read +2 +select variable_value into @rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @master_rows_updated; +@rows_updated - @master_rows_updated +1 +select variable_value into @rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @master_rows_deleted; +@rows_deleted - @master_rows_deleted +1 +select variable_value into @rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @master_rows_inserted; +@rows_inserted - @master_rows_inserted +1 +select variable_value into @system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @master_system_rows_read > 0; +@system_rows_read - @master_system_rows_read > 0 +1 +select variable_value into @system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @master_system_rows_updated > 0; +@system_rows_updated - @master_system_rows_updated > 0 +1 +select variable_value into @system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @master_system_rows_deleted > 0; +@system_rows_deleted - @master_system_rows_deleted > 0 +0 +select variable_value into @system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @master_system_rows_inserted > 0; +@system_rows_inserted - @master_system_rows_inserted > 0 +1 +==========SLAVE=========== +select variable_value into @rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @slave_rows_read; +@rows_read - @slave_rows_read +2 +select variable_value into @rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @slave_rows_updated; +@rows_updated - @slave_rows_updated +1 +select variable_value into @rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @slave_rows_deleted; +@rows_deleted - @slave_rows_deleted +1 +select variable_value into @rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @slave_rows_inserted; +@rows_inserted - @slave_rows_inserted +1 +select variable_value into @system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @slave_system_rows_read > 0; +@system_rows_read - @slave_system_rows_read > 0 +1 +select variable_value into @system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @slave_system_rows_updated > 0; +@system_rows_updated - @slave_system_rows_updated > 0 +1 +select variable_value into @system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @slave_system_rows_deleted > 0; +@system_rows_deleted - @slave_system_rows_deleted > 0 +0 +select variable_value into @system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @slave_system_rows_inserted > 0; +@system_rows_inserted - @slave_system_rows_inserted > 0 +1 +DROP TABLE t1; +DROP DATABASE testdb; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_innodb_bug68220-slave.opt b/mysql-test/suite/rpl/t/rpl_innodb_bug68220-slave.opt new file mode 100644 index 0000000..1aeaaf7 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_innodb_bug68220-slave.opt @@ -0,0 +1 @@ +--master_info_repository=TABLE --relay_log_info_repository=TABLE diff --git a/mysql-test/suite/rpl/t/rpl_innodb_bug68220.test b/mysql-test/suite/rpl/t/rpl_innodb_bug68220.test new file mode 100644 index 0000000..a353ed0 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_innodb_bug68220.test @@ -0,0 +1,58 @@ +--source include/master-slave.inc + +# +# Bug#68220: innodb_rows_updated is misleading on slave when *info_repository=TABLE +# + +# clean previous tests +--connection master +--disable_warnings +DROP TABLE IF EXISTS testdb.t1; +DROP DATABASE IF EXISTS testdb; +--enable_warnings +--sync_slave_with_master + +# created all the base variables at the beginning at the test +--connection master +select variable_value into @master_rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select variable_value into @master_rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select variable_value into @master_rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select variable_value into @master_rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select variable_value into @master_system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select variable_value into @master_system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select variable_value into @master_system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select variable_value into @master_system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; + +--connection slave +select variable_value into @slave_rows_read from performance_schema.global_status where variable_name = 'innodb_rows_read'; +select variable_value into @slave_rows_updated from performance_schema.global_status where variable_name = 'innodb_rows_updated'; +select variable_value into @slave_rows_deleted from performance_schema.global_status where variable_name = 'innodb_rows_deleted'; +select variable_value into @slave_rows_inserted from performance_schema.global_status where variable_name = 'innodb_rows_inserted'; +select variable_value into @slave_system_rows_read from performance_schema.global_status where variable_name = 'innodb_system_rows_read'; +select variable_value into @slave_system_rows_updated from performance_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select variable_value into @slave_system_rows_deleted from performance_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select variable_value into @slave_system_rows_inserted from performance_schema.global_status where variable_name = 'innodb_system_rows_inserted'; + +--connection master +CREATE DATABASE testdb; +USE testdb; +CREATE TABLE testdb.t1 (i int NOT NULL PRIMARY KEY) ENGINE=InnoDB; + +# insert a row and show counters on master and slave +INSERT INTO testdb.t1 VALUES (1); +--source suite/rpl/include/rpl_innodb_rows_counters.inc + +# update the row and show counters on master and slave +UPDATE t1 SET i=2 WHERE i=1; +--source suite/rpl/include/rpl_innodb_rows_counters.inc + +# delete the row and show counters on master and slave +DELETE FROM t1 WHERE i=2; +--source suite/rpl/include/rpl_innodb_rows_counters.inc + +# clean the test +DROP TABLE t1; +DROP DATABASE testdb; +--sync_slave_with_master + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result index 0512053..7873896 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result @@ -228,6 +228,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_tables disabled ddl_online_create_index disabled ddl_pending_alter_table disabled @@ -453,6 +457,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -463,6 +471,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -473,6 +485,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -485,6 +501,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -495,6 +515,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -505,6 +529,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -515,6 +543,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -528,6 +560,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result index 0512053..7873896 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result @@ -228,6 +228,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_tables disabled ddl_online_create_index disabled ddl_pending_alter_table disabled @@ -453,6 +457,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -463,6 +471,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -473,6 +485,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -485,6 +501,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -495,6 +515,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -505,6 +529,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -515,6 +543,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -528,6 +560,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result index 0512053..7873896 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result @@ -228,6 +228,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_tables disabled ddl_online_create_index disabled ddl_pending_alter_table disabled @@ -453,6 +457,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -463,6 +471,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -473,6 +485,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -485,6 +501,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -495,6 +515,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -505,6 +529,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -515,6 +543,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -528,6 +560,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result index 04ca443..be7d9d4 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result @@ -228,6 +228,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_tables disabled ddl_online_create_index disabled ddl_pending_alter_table disabled @@ -453,6 +457,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -463,6 +471,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -473,6 +485,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -485,6 +501,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -495,6 +515,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -505,6 +529,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -515,6 +543,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -528,6 +560,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/storage/innobase/api/api0api.cc b/storage/innobase/api/api0api.cc index 1ba6a9e..e3e90a5 100644 --- a/storage/innobase/api/api0api.cc +++ b/storage/innobase/api/api0api.cc @@ -1204,7 +1204,10 @@ 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 = ""; @@ -1564,9 +1567,15 @@ 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/mem.cc b/storage/innobase/dict/mem.cc index e31d1d1..ff9fe53 100644 --- a/storage/innobase/dict/mem.cc +++ b/storage/innobase/dict/mem.cc @@ -152,6 +152,43 @@ dict_mem_table_free( mem_heap_free(table->heap); } +/** System databases */ +static const char* innobase_system_databases[] = { + "mysql/", + "information_schema/", + "performance_schema/", + NullS +}; + +/****************************************************************//** +Determines if a table belongs to a system database +@return */ +static 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; + } +} + /**********************************************************************//** Creates a table memory object. @return own: table object */ @@ -196,6 +233,7 @@ dict_mem_table_create( table->flags = (unsigned int) flags; table->flags2 = (unsigned int) flags2; table->name.m_name = mem_strdup(name); + table->is_system_db = dict_mem_table_is_system(table->name.m_name); table->space = (unsigned int) space; table->dd_space_id = dd::INVALID_OBJECT_ID; table->n_t_cols = (unsigned int) (n_cols + table->get_n_sys_cols()); diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index e1e2cee..defae7c 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -1185,6 +1185,14 @@ static SHOW_VAR innodb_status_variables[]= { (char*) &export_vars.innodb_rows_read, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"rows_updated", (char*) &export_vars.innodb_rows_updated, SHOW_LONG, SHOW_SCOPE_GLOBAL}, + {"system_rows_deleted", + (char*) &export_vars.innodb_system_rows_deleted, SHOW_LONG, SHOW_SCOPE_GLOBAL}, + {"system_rows_inserted", + (char*) &export_vars.innodb_system_rows_inserted, SHOW_LONG, SHOW_SCOPE_GLOBAL}, + {"system_rows_read", + (char*) &export_vars.innodb_system_rows_read, SHOW_LONG, SHOW_SCOPE_GLOBAL}, + {"system_rows_updated", + (char*) &export_vars.innodb_system_rows_updated, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"num_open_files", (char*) &export_vars.innodb_num_open_files, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"truncated_status_writes", @@ -10010,8 +10018,14 @@ ha_innobase::index_read( switch (ret) { case DB_SUCCESS: error = 0; - srv_stats.n_rows_read.add( - thd_get_thread_id(m_prebuilt->trx->mysql_thd), 1); + if (m_prebuilt->table->is_system_db) + srv_stats.n_system_rows_read.add( + thd_get_thread_id(m_prebuilt->trx->mysql_thd), + 1); + else + srv_stats.n_rows_read.add( + thd_get_thread_id(m_prebuilt->trx->mysql_thd), + 1); break; case DB_RECORD_NOT_FOUND: @@ -10294,7 +10308,14 @@ ha_innobase::general_fetch( switch (ret) { case DB_SUCCESS: error = 0; - srv_stats.n_rows_read.add(thd_get_thread_id(trx->mysql_thd), 1); + if (m_prebuilt->table->is_system_db) + srv_stats.n_system_rows_read.add( + thd_get_thread_id(m_prebuilt->trx->mysql_thd), + 1); + else + srv_stats.n_rows_read.add( + thd_get_thread_id(m_prebuilt->trx->mysql_thd), + 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 2b582c8..7c06371 100644 --- a/storage/innobase/include/dict0mem.h +++ b/storage/innobase/include/dict0mem.h @@ -1703,6 +1703,10 @@ struct dict_table_t { /** Virtual column names */ const char* v_col_names; + /** True if the table belongs to a system database (mysql, + information_schema or performance_schema) */ + bool is_system_db; + /** Hash chain node. */ hash_node_t name_hash; diff --git a/storage/innobase/include/srv0mon.h b/storage/innobase/include/srv0mon.h index 2891532..998b12a 100644 --- a/storage/innobase/include/srv0mon.h +++ b/storage/innobase/include/srv0mon.h @@ -408,6 +408,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 52fa502..473e3b5 100644 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -140,6 +140,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; + /** Number of buffered aio requests submitted */ ulint_ctr_64_t n_aio_submitted; @@ -948,6 +960,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 */ #ifdef UNIV_DEBUG diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 154a38d..f44d8dd 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -1822,7 +1822,10 @@ error_exit: que_thr_stop_for_mysql_no_error(thr, trx); - 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(); /* Not protected by dict_table_stats_lock() for performance reasons, we would rather get garbage in stat_n_rows (which is @@ -2550,9 +2553,15 @@ run_again: with a latch. */ dict_table_n_rows_dec(prebuilt->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(); } /* We update table statistics only if it is a DELETE or UPDATE @@ -2836,9 +2845,15 @@ 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 4963cdc..90e0695 100644 --- a/storage/innobase/srv/srv0mon.cc +++ b/storage/innobase/srv/srv0mon.cc @@ -1313,6 +1313,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_EXISTING | MONITOR_DEFAULT_ON), + MONITOR_DEFAULT_START, MONITOR_OLVD_SYSTEM_ROW_READ}, + + {"dml_system_inserts", "dml", "Number of system rows inserted", + static_cast( + 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_EXISTING | MONITOR_DEFAULT_ON), + MONITOR_DEFAULT_START, MONITOR_OLVD_SYSTEM_ROW_DELETED}, + + {"dml_system_updates", "dml", "Number of system rows updated", + static_cast( + 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, @@ -1843,6 +1863,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 758c6fd..05ce39c 100644 --- a/storage/innobase/srv/srv0srv.cc +++ b/storage/innobase/srv/srv0srv.cc @@ -433,6 +433,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; #endif /* !UNIV_HOTBACKUP */ ulint srv_truncated_status_writes = 0; @@ -1179,6 +1183,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); } @@ -1386,11 +1395,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" @@ -1556,6 +1587,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 =