On 4/3/12 3:28 AM, Martin Simmons wrote: >>>>>> On Mon, 02 Apr 2012 15:06:31 -0700, Stephen Thompson said: >> >>>> That aside, I'm seeing something unexpected. I am now able to >>>> successfully run jobs while I use mysqldump to dump the bacula Catalog, >>>> except at the very end of the dump there is some sort of contention. A >>>> few of my jobs (3-4 out of 150) that are attempting to despool >>>> attritbutes at the tail end of the dump yield this error: >>>> >>>> Fatal error: sql_create.c:860 Fill File table Query failed: INSERT INTO >>>> File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) SELECT >>>> batch.FileIndex, batch.JobId, Path.PathId, >>>> Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch >>>> JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name = >>>> Filename.Name): ERR=Lock wait timeout exceeded; try restarting transaction >>>> >>>> I have successful jobs before and after this 'end of the dump' timeframe. >>>> >>>> It looks like I might be able to "fix" this by increasing my >>>> innodb_lock_wait_timeout, but I'd like to understand WHY I need to >>>> icnrease it. Anyone know what's happening at the end of a dump like >>>> this that would cause the above error? >>>> >>>> mysqldump -f --opt --skip-lock-tables --single-transaction bacula >>>> >>bacula.sql >>>> >>>> Is it the commit on this 'dump' transaction? >>> >>> --skip-lock-tables is referred to in the mysqldump documentation, but >>> isn't actually a valid option. This is actually an increasingly >>> horrible problem with mysqldump. It has been very poorly maintained, >>> and has barely developed at all in ten or fifteen years. >>> >> >> This has me confused. I have jobs that can run, and insert records into >> the File table, while I am dumping the Catalog. It's only at the >> tail-end that a few jobs get the error above. Wouldn't a locked File >> table cause all concurrent jobs to fail? > > Are you sure that jobs are inserting records into the File table whilst they > are running? With spooling, file records are not inserted until the end of > the job. > > Likewise, in batch mode (as above), the File table is only updated once at the > end. >
Yes, I have completed jobs before and after the problem jobs (which aren't always the same jobs, or happen at the same time, except that they seem to correlate with the end of the Catalog dump, which could also be the end of the File table dump, since it's 99% of the db). I can view the inserted records from jobs that complete while the Catalog dump is running. And I am spooling, so jobs are inserting all attrs at the end of the job. The jobs with the errors are clearly moving their records from the batch file to the File table at the conclusion of their run. I have never seen this before moving to InnoDB, but of course, I moved to InnoDB to be able to run my Catalog dump concurrently with jobs (knowing I won't capture the records from the running jobs). So at this point, I'm not sure if I'm getting the error because of something happening at the end of the dump, or if it's merely a 'collision' of jobs all wanting to insert batch records at the same time. I know that the Innodb engine has a lock wait timeout default of 50s, but I'm not sure who this was handled with MyISAM where I never saw this problem (but again, also, never ran my jobs concurrently with dump). Stephen > __Martin > > ------------------------------------------------------------------------------ > 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 -- 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
