| Bug #77783 | performance dropped when changing innodb_change_buffering from inserts to all | ||
|---|---|---|---|
| Submitted: | 20 Jul 2015 7:01 | Modified: | 25 Oct 2019 12:00 |
| Reporter: | zhai weixiang (OCA) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 5.7, 5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[24 Sep 2019 14:02]
MySQL Verification Team
Hi, Thank you Mr. weixiang. However, I do not see what is a bug here. When the value of that variable is set to INSERTS, then you get much higher performance with those DMLs. Hence, what is a problem. If you have an idea about the mistake in the code and how it could be mitigated, please share it with us.
[25 Oct 2019 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: while changing our default setting of innodb_change_buffering from inserts to all, I observed a very obvious tps decreasing immediately (almost from 17000 inserts/s to 6000 inserts/s). The main workload is INSERT, you can easily repeat the TPS decreasing with the following step: 1. small buffer pool size, and innodb_change_buffering=inserts 2. create table with some of secondary indexes 3. keep inserting records into the table ...after a while, set innodb_change_buffering = all This is because when innodb_change_buffering = inserts, the counter area (IBUF_REC_OFFSET_COUNTER) of ibuf entry is not set and use the old format to build the ibuf entry. If innodb_change_buffering is not equal to INSERTS or NONE, it need to read the counter value of previous ibuf entry on the same page, and check if it equals to ULINT_UNDEFINED. if yes, then consider current change buffering failed. quoted code from ibuf_insert_low： 3544 if (!no_counter) { 3545 /* Patch correct counter value to the entry to 3546 insert. This can change the insert position, which can 3547 result in the need to abort in some cases. */ 3548 ulint counter = ibuf_get_entry_counter( 3549 page_id.space(), page_id.page_no(), 3550 btr_pcur_get_rec(&pcur), &mtr, 3551 btr_pcur_get_btr_cur(&pcur)->low_match 3552 < IBUF_REC_FIELD_METADATA); 3553 dfield_t* field; 3554 3555 if (counter == ULINT_UNDEFINED) { 3556 ibuf_mtr_commit(&bitmap_mtr); 3557 goto fail_exit; 3558 } This means we can buffer new operation only after all previous ibuf entries are merged... How to repeat: described above or read the code Suggested fix: I don't know. May be we can always write the counter value even innodb_change_buffering is equal to INSERTS ?