694222 - sync: Test and deploy MySQL option innodb_locks_unsafe_for_binlog=ON

Status: RESOLVED WONTFIX

(Cloud Services :: Operations: Deployment Requests - DEPRECATED, task)

Description

[:Atoll]

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog

http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed

Please enable the global parameters:

SET GLOBAL innodb_locks_unsafe_for_binlog=ON;
SET GLOBAL transaction isolation level READ COMMITTED;

In staging, with load tests before and after the change, so we can confirm that the applications continues to work as expected.

Please also smoke test with a mobile and desktop client paired to the same account doing constant surfing using the latest nightlies, so that we can be sure that back-and-forth syncing works okay.

Once confirmed safe in staging, deploy to one production server and wait a day to see if any further issues crop up, then push to the rest of prod.

Comment 1

[Pete Fritchman [:petef]]

will loadtest & work with jbonacci for QA.

Comment 2

[Pete Fritchman [:petef]]

baseline (with 30 loadtest threads total):

http://pencil.scl2.stage.svc.mozilla.com/dash/stage/syncstorage?start=2011-10-13+10%3A00%3A00&duration=230+minutes&width=1000&height=400
http://pencil.scl2.stage.svc.mozilla.com/dash/stage/sync_mysql?start=2011-10-13+10%3A00%3A00&duration=230+minutes&width=1000&height=400

Comment 3

[Pete Fritchman [:petef]]

we hit a new all-time cpu usage high in stage with this tweak, worth it for production.

Comment 4

[James Bonacci [:jbonacci]]

What's the deployment plan/schedule for this one?

Comment 5

[Pete Fritchman [:petef]]

re-loadtesting, had mysql "unsafe" stage mode on.

when there's a schedule, I'll link it from here

Comment 6

[James Bonacci [:jbonacci]]

QA picks this up for testing Monday 10/31 - Wednesday 11/2.
Working with a set of current Stage accounts (all desktop to desktop)
and a new Stage account that is a mix of desktop and mobile.
Working will Firefox 7, 8, 9, and 10.

Will be testing with and without load running.

Comment 7

[James Bonacci [:jbonacci]]

Completed the first half of the testing while Pete ran load.
Used a mix of accounts with desktop and mobile devices.
Tested against Firefox 7, beta (8), aurora (9), and nightly (10).
Tested Mac, Windows, Linux, iOS, and Android.

Will continue Tue or Wed with similar tests while no load is applied.

Comment 8

[James Bonacci [:jbonacci]]

Completed all testing in Stage with and without load running.
Testing included a mix of desktop and mobile devices.
Testing included FF 7, Beta, Aurora, and Nightly.

jbonacci+mobiles1, stage-sync71
jbonacci+test11, stage-sync7
jbonacci+test22, stage-sync8
jbonacci+test3, stage-sync71
jbonacci+test4, stage-sync69
jbonacci+test5, stage-sync69
jbonacci+test6, stage-sync7
jbonacci+test7, stage-sync68

Comment 9

[:Atoll]

verbal loadtest+ from petef, next phase is deployment

Comment 10

[:Atoll]

Scheduled for production. https://intranet.mozilla.org/Services/Ops/ChangeWindow_20120124

Comment 11

[:Atoll]

Pushed the changes live in puppet-weave/files/etc/my.cnf.*percona*, updated op to stagger restarts.

Comment 12

[:Atoll]

130 hosts still require a mysql restart.  adm1.phx1.svc:~rsoderberg/694222.hosts

Comment 13

[:Atoll]

+++ This bug was cloned as Bug #753689 +++

Separating bug 694222 into two pieces.

innodb_locks_unsafe_for_binlog=ON is this bug, READ_COMMITTED is bug 753689.

Comment 14

[Chris Kolosiwsky [:ckolos] (ckolos has left the building)]

:bobm