982312 - Server Tuning Variables for Bugzilla Slave Nodes

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Brandon Johnson [:cyborgshadow]]

We tested with some server tuning variables for the bugzilla slaves and came up with a few that worked (for physical machines only).

We can apply these on the slaves at any time but would like to apply them on the master during the downtime window in bug 982293. 

The variable we should utilize are:
flush_logs_at_trx_commit=2 


Another variable we're setting (by its absence) is flush_method, set to the default of fdatasync. (We set this by absence because it's the default, but not an option).

Comment 1

[Sheeri Cabral [:sheeri]]

key_buffer_size should be reduced as well, to the size of the mysql system tables.

Comment 2

[Sheeri Cabral [:sheeri]]

These are now set on the phx servers, and they have been restarted:

key_buffer_size is now unset (as in, set to the default).
innodb_flush_logs_at_trx_commit=2 
flush_method=fdatasync

Sheeri-Cabral:trunk scabral$ svn commit -m "fixing my.cnf for bugzilla"
Sending        manifests/nodes/databases.pp
Sending        modules/mysql2/manifests/server.pp
Sending        modules/mysql2/templates/my.cnf.erb
Transmitting file data ...
Committed revision 85170.

This was for bugzilla phx. There were some issues getting the proper syntax, but bugzilla in phx is all set.

Comment 3

[Sheeri Cabral [:sheeri]]

Sheeri-Cabral:nodes scabral$ svn commit -m "adding flush_log_at_trx_commit, MIXED binlog mode, and fdatasync flush_method to bugzilla in scl3"
Sending        databases.pp
Transmitting file data .
Committed revision 85236.

Comment 4

[Sheeri Cabral [:sheeri]]

innodb_flush_method is a static variable, so I will be restarting mysql on the slaves one at a time, and the master during the outage window in the dependent bug.

Comment 5

[Sheeri Cabral [:sheeri]]

slaves are all restarted, and bugzilla master is using MIXED binlog format. Leaving this open until we can restart, as in the dependent bug.

Comment 6

[Sheeri Cabral [:sheeri]]

The db has long since been restarted.