Page MenuHomePhabricator
Create Task
Maniphest T263443

Evaluate the impact of changing innodb_change_buffering to inserts
Closed, ResolvedPublic
Actions

Description

Some of the crashes we've seen in 10.4 (including the almighty series of labsdb1011 crashes T249188) might be related to the fact that we have:

| innodb_change_buffering       | all   |

On the following MariaDB related bugs there is a mention that setting innodb_change_buffering = inserts or even to none might prevent this from happening again:
https://jira.mariadb.org/browse/MDEV-12463
https://jira.mariadb.org/browse/MDEV-22373 (filed by us)
https://jira.mariadb.org/browse/MDEV-22497

We should evaluate if changing this setting from all to inserts has some performance impact, and if not, we might need to switch it permanently.

So far the following hosts have it live changed:

  • db2129 s6 master
  • db2116 s1 slave

Lists of hosts as of 28th Sept 2020: T263443#6497328
More hosts added the 6th Oct on s6: db2087:3316 db2089:3316 db2076 db2097:3316 db2114
More hosts added the 6th Oct on s5: db2075 db2089:3315 db2099:3315 db2111 db2128
More hosts added the 7th Oct on pc3: pc2009

Details

SubjectRepoBranchLines +/-
mariadb: Clarify that innodb_change_buffering is noneoperations/puppetproduction+13 -1
sanitarium_multiinstance.my.cnf: Add innodb_change_bufferingoperations/puppetproduction+1 -0
production.my.cnf: Add innodb_change_buffering = noneoperations/puppetproduction+1 -0
mariadb: Set innodb_change_buffering = none on a few rolesoperations/puppetproduction+3 -0
parsercache.my.cnf: innodb_change_buffering = noneoperations/puppetproduction+1 -0
Customize query in gerrit

Related Objects

Event Timeline

• Marostegui created this task.Sep 21 2020, 11:53 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 21 2020, 11:53 AM
• Marostegui triaged this task as Medium priority.Sep 21 2020, 11:53 AM
• Marostegui moved this task from Triage to In progress on the DBA board.
Stashbot added a comment.Sep 21 2020, 12:26 PM

Mentioned in SAL (#wikimedia-operations) [2020-09-21T12:26:17Z] <marostegui> Set innodb_change_buffering = all; on db2129 (s6 master) for performance testing T263443

Stashbot added a comment.Sep 21 2020, 12:26 PM

Mentioned in SAL (#wikimedia-operations) [2020-09-21T12:26:49Z] <marostegui> Set innodb_change_buffering = all; on db2071 (s1 slave) for performance testing T263443

• Marostegui added a comment.Sep 21 2020, 12:28 PM

db2129 reverted to all
db2071 s1 slave, set to inserts

Stashbot added a comment.Sep 21 2020, 1:21 PM

Mentioned in SAL (#wikimedia-operations) [2020-09-21T13:21:07Z] <marostegui> Set innodb_change_buffering = inserts; on db2081 (s8 slave) for performance testing T263443

• Marostegui added a comment.Sep 21 2020, 1:21 PM

db2081 (wikidata) set to inserts

Stashbot added a comment.Sep 21 2020, 2:21 PM

Mentioned in SAL (#wikimedia-operations) [2020-09-21T14:21:35Z] <marostegui> Set innodb_change_buffering = inserts; on db2125 (s2 slave) for performance testing T263443

• Marostegui added a comment.Sep 21 2020, 2:22 PM

db2125 (s2) set to inserts

• Marostegui mentioned this in T263244: Reimage and reclone db2125.Sep 21 2020, 3:31 PM
Stashbot added a comment.Sep 28 2020, 6:15 AM

Mentioned in SAL (#wikimedia-operations) [2020-09-28T06:15:34Z] <marostegui> Set innodb_change_buffering = inserts; on db2089 (s5), db2106 (s4), db2108 (s2), db2085 (s1), db2085 (s8), db2087 (s7), db2087 (s6), db2109 (s3) T263443

• Marostegui added a comment.EditedSep 28 2020, 6:18 AM

Sum up of hosts with the setting changed to inserts:

s1:
db2071 db2085 db2116

s2:
db2108

s3:
db2109

s4:
db2106

s5:
db2075 db2089:3315 db2099:3315 db2111 db2128

s6:
db2087 db2089:3316 db2076 db2097:3316 db2114

s7:
db2087

s8:
db2085 db2081

pc3:
pc2009

• Marostegui updated the task description. (Show Details)Sep 28 2020, 6:21 AM
Stashbot added a comment.Oct 6 2020, 7:53 AM

Mentioned in SAL (#wikimedia-operations) [2020-10-06T07:53:13Z] <marostegui> Change innodb_change_buffering = inserts on db2087:3316 db2089:3316 db2076 db2097:3316 db2114 T263443

• Marostegui updated the task description. (Show Details)Oct 6 2020, 8:05 AM
• Marostegui added a comment.Oct 6 2020, 1:02 PM

I haven't found anything weird on this so far, so I am going to deploy it to more hosts on s5 and s2 for now after deploying it to a bunch on s6 this morning.

Stashbot added a comment.Oct 6 2020, 1:04 PM

Mentioned in SAL (#wikimedia-operations) [2020-10-06T13:04:36Z] <marostegui> Change innodb_change_buffering = inserts on db2075 db2089 db2099 db2111 db2128 T263443

• Marostegui updated the task description. (Show Details)Oct 6 2020, 1:05 PM
Stashbot added a comment.Oct 7 2020, 10:58 AM

Mentioned in SAL (#wikimedia-operations) [2020-10-07T10:58:00Z] <marostegui> Set innodb_change_buffering = inserts on pc2009 T263443

• Marostegui added a comment.Oct 7 2020, 10:58 AM

Changed it on pc2009 too

• Marostegui updated the task description. (Show Details)Oct 7 2020, 10:58 AM
LSobanski added a project: Data-Persistence.Oct 8 2020, 9:33 AM
LSobanski moved this task from Inbox to Epic on the Data-Persistence board.
LSobanski moved this task from In progress to Ready on the DBA board.Oct 8 2020, 9:54 AM
• Marostegui added a comment.Oct 8 2020, 2:21 PM

I have noticed some slight increase on InnoDB wait time on pc2009, it could be just a coincidence but I am going to revert back to innodb_change_buffering = all and check what's the pattern there.

Stashbot added a comment.Oct 8 2020, 2:21 PM

Mentioned in SAL (#wikimedia-operations) [2020-10-08T14:21:41Z] <marostegui> Set global innodb_change_buffering = all; on pc2009 T263443

• Marostegui added a comment.Oct 9 2020, 7:38 AM
In T263443#6529382, @Marostegui wrote:

I have noticed some slight increase on InnoDB wait time on pc2009, it could be just a coincidence but I am going to revert back to innodb_change_buffering = all and check what's the pattern there.

Going to leave that on pc2009 for the next few days, as the pattern seems to have come back to normal.
I will enable it back on Tuesday and see if it changes again:

Captura de pantalla 2020-10-09 a las 9.36.49.png (734×1 px, 118 KB)

This issues doesn't show on normal core hosts. pcXXX hosts have a very different write pattern from the rest of hosts, as they only receive REPLACE writes.

• Marostegui added a comment.Oct 13 2020, 5:35 AM

So far, the values haven't increased yet, so going to change it back on pc2009 to inserts to see if it goes back to higher values.

Stashbot added a comment.Oct 13 2020, 5:35 AM

Mentioned in SAL (#wikimedia-operations) [2020-10-13T05:35:22Z] <marostegui> Set global innodb_change_buffering = inserts; on pc2009 T263443

LSobanski moved this task from Ready to In progress on the DBA board.Oct 26 2020, 2:46 PM
LSobanski removed a project: Data-Persistence.Oct 26 2020, 4:03 PM
• Marostegui moved this task from In progress to Ready on the DBA board.Nov 26 2020, 2:03 PM

This is running by default on all the clouddb hosts.

LSobanski subscribed.Nov 27 2020, 1:29 PM
gerritbot added a comment.Mar 5 2021, 11:26 AM

Change 668669 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] parsercache.my.cnf: innodb_change_buffering = none

https://gerrit.wikimedia.org/r/668669

gerritbot added a project: Patch-For-Review.Mar 5 2021, 11:26 AM
Stashbot added a comment.Mar 5 2021, 11:28 AM

Mentioned in SAL (#wikimedia-operations) [2021-03-05T11:28:21Z] <marostegui> Temporarily set innodb_change_buffering = none on db1134 (s1) - T263443

• Marostegui added a comment.Mar 5 2021, 11:29 AM

I have set: innodb_change_buffering = none on pc1007 and db1134 temporarily to check their performance.

gerritbot added a comment.Mar 8 2021, 6:34 AM

Change 668669 merged by Marostegui:
[operations/puppet@production] parsercache.my.cnf: innodb_change_buffering = none

https://gerrit.wikimedia.org/r/668669

Stashbot added a comment.Mar 8 2021, 6:44 AM

Mentioned in SAL (#wikimedia-operations) [2021-03-08T06:44:43Z] <marostegui> Set innodb_change_buffering = none on all parsercache hosts T263443

• Marostegui added a comment.Mar 8 2021, 6:45 AM

All parsercache hosts have been changed to innodb_change_buffering = none

Maintenance_bot removed a project: Patch-For-Review.Mar 8 2021, 7:10 AM
gerritbot added a comment.Apr 7 2021, 6:08 AM

Change 677423 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] misc,phabricator,dbstore_multiinstance.my.cnf: Set innodb_change_buffering = none

https://gerrit.wikimedia.org/r/677423

gerritbot added a project: Patch-For-Review.Apr 7 2021, 6:08 AM
gerritbot added a comment.Apr 7 2021, 6:13 AM

Change 677423 merged by Marostegui:

[operations/puppet@production] mariadb: Set innodb_change_buffering = none on a few roles

https://gerrit.wikimedia.org/r/677423

Maintenance_bot removed a project: Patch-For-Review.Apr 7 2021, 7:10 AM
• Marostegui added a comment.Apr 7 2021, 8:51 AM

Changed this on a few roles:

  • Misc
  • Phabricator
  • dbstore_multiinstance

They'll pick up the change once mysql gets restarted

gerritbot added a comment.Apr 15 2021, 5:11 AM

Change 679609 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] production.my.cnf: Add innodb_change_buffering = none

https://gerrit.wikimedia.org/r/679609

gerritbot added a project: Patch-For-Review.Apr 15 2021, 5:11 AM

Change 679609 merged by Marostegui:

[operations/puppet@production] production.my.cnf: Add innodb_change_buffering = none

https://gerrit.wikimedia.org/r/679609

• Marostegui added a comment.Apr 15 2021, 5:13 AM

I have enabled this on production, it will be picked up once the hosts start to restart.
However, I am going to manually enable it on a few hosts per section so we can further test it even more.

• Marostegui closed this task as Resolved.Apr 29 2021, 5:34 AM

This has been enabled everywhere

gerritbot added a comment.May 4 2021, 5:19 AM

Change 684672 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] sanitarium_multiinstance.my.cnf: Add innodb_change_buffering

https://gerrit.wikimedia.org/r/684672

gerritbot added a comment.May 4 2021, 5:21 AM

Change 684672 merged by Marostegui:

[operations/puppet@production] sanitarium_multiinstance.my.cnf: Add innodb_change_buffering

https://gerrit.wikimedia.org/r/684672

gerritbot added a comment.May 10 2021, 12:03 PM

Change 688248 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] mariadb: Clarify that innodb_change_buffering is none

https://gerrit.wikimedia.org/r/688248

gerritbot added a comment.May 10 2021, 12:07 PM

Change 688248 merged by Marostegui:

[operations/puppet@production] mariadb: Clarify that innodb_change_buffering is none

https://gerrit.wikimedia.org/r/688248

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL