On 10/23/15 14:47, Craig Shiroma wrote: > Hello, > > We are experiencing database performance problems with locks occurring. > We think it is because jobs that require a huge amount of inserts is > holding on to innodb_autoinc. We are thinking of changing the following > to see if it helps. > > innodb_autoinc_lock_mode = 1 > > to: > > innodb_autoinc_lock_mode = 2 > > I think the change causes a problem with database replication. However, > we currently are not doing DB replication so we don't think that will be > a problem. Does anyone know if this change will cause other problems?
In my professional capacity, I recommend this tuning change to all of our customers by default. It can make a big difference. There is basically one caveat: If you use innodb_autoinc_lock_mode = 2 in a replicated environment, you MUST set binlog_format to either ROW or MIXED, because it is unsafe in STATEMENT mode. (If set to MIXED, MySQL will know that it is an unsafe operation and automatically record it as ROW.) Provided that is done, this is 100% safe unless you are using an application which can insert multiple rows in a single transaction and expects them to have strictly consecutive auto-increment IDs, but does not actually check that they do. (In which case, the application developer is doing it wrong.) -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: 603.293.8485 ------------------------------------------------------------------------------ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
