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
  phils AT caerllewys DOT net
  phil AT co.ordinate DOT org
  Landline: 603.293.8485

------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users