Details
-
Type:
Bug
-
Status: Stalled
-
Priority:
Critical
-
Resolution: Unresolved
-
Affects Version/s: 10.0.11
-
Fix Version/s: 10.0
-
Component/s: None
-
Labels:
-
Environment:RHEL 6.4
Description
Hi,
I'm using the Replicate_Do_DB option to replicate a DB with a very small amount of activity (less then one transaction per second). Another DB running on the master server runs many more transactions per second. In the output of 'show full processlist' on the slave, I can see the slave process constantly switching between COMMIT and 'closing tables' for the DB that should be excluded by the Replicate_Do_DB option. I did not have this issue when using MariaDB 5.5.27.
I enabled the general query log and it shows BEGIN and COMMIT repeatedly but no queries that actually alter any tables:
140527 12:13:35 1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
140527 12:13:36 1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT /* implicit, from Xid_log_event */
1721 Query BEGIN
1721 Query COMMIT /* implicit, from Xid_log_event */
1721 Query BEGIN
1721 Query COMMIT
1721 Query BEGIN
1721 Query COMMIT
This generates a lot of I/O contention that I've had to mitigate on this development server by setting innodb_flush_log_at_trx_commit to 0. I wouldn't be comfortable with that setting on a production DB server though.
Regards,
Bill
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I verified this bug with the following test case:
In the output of this test case, we can see this:
So the problem is that for the empty transaction, even though there are no
changes (and nothing logged into the binlog), we still update the gtid slave
pos, which requires an expensive commit of the mysql.gtid_slave_pos table.
It might have been deliberate to update the gtid_slave_pos, the idea being
that if the slave is stopped and then re-started, it should not try to
re-execute events that were already skipped.
But in any case, I think this bug clearly shows that such behaviour is
wrong. If user filters out events, she will not expect transaction commit of
such filtered events.
So this needs to be fixed, I think.