On 04/03/2012 08:43 AM, Phil Stracchino wrote: > > Stephen, by the way, if you're not already aware of it: You probably > want to set innodb_flush_log_at_trx_commit = 0. > > The default value of this setting is 1, which causes the log buffer to > be written out to the lgo file and the logfile flushed to disk at every > transaction commit. (Which obviously has a performance impact.) With a > setting of 0, nothing is done at transaction commit, but the log buffer > is written to the log file and the log file flushed to disk once per > second. There is a potential with this setting that up to the last full > second of transactions can be list in the event of a mysqld crash, but > ... if mysqld crashes in the middle of Bacula inserting attributes, that > job is blown *anyway*, so there's really no loss.
This is an interesting suggestion. I wonder if it's possible since I'm running the dump as a single transaction if my database is becoming unavailable during this flush, such that the 50 second timeout for the locks the jobs are requesting is surpassed. I would expect writes to a database to require more flushing than read (i.e. a dump), but I wonder if this could explain the jobs failing at the tail-end of the dump. > > I also suggest innodb_autoinc_lock_mode = 2, which allows InnoDB to > interleave auto_increment inserts. This may possibly help with your > locking problem. Keep in mind though that if you use this setting and > you have replication running, your binlog_format must be set to MIXED or > ROW. > > -- Stephen Thompson Berkeley Seismological Laboratory step...@seismo.berkeley.edu 215 McCone Hall # 4760 404.538.7077 (phone) University of California, Berkeley 510.643.5811 (fax) Berkeley, CA 94720-4760 ------------------------------------------------------------------------------ Better than sec? Nothing is better than sec when it comes to monitoring Big Data applications. Try Boundary one-second resolution app monitoring today. Free. http://p.sf.net/sfu/Boundary-dev2dev _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
