First off, thanks for the response Phil.
On 04/02/2012 01:11 PM, Phil Stracchino wrote:
> On 04/02/2012 01:49 PM, Stephen Thompson wrote:
>> Well, we've made the leap from MyISAM to InnoDB, seems like we win on
>> transactions, but lose on read speed.
>
> If you're finding InnoDB slower than MyISAM on reads, your InnoDB buffer
> pool is probably too small.
This is probably true, but I have limited system resources and my File
table is almost 300Gb large.
>
>> 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?
> Table locks are the default behavior of mysqldump, as part of the
> default --opt group. To override it, you actually have to use
> --skip-opt, than add back in the rest of the options from the --opt
> group that you actually wanted. There is *no way* to get mysqldump to
> Do The Right Thing for both transactional and non-transactional tables
> in the same run. it is simply not possible.
>
> My suggestion would be to look at mydumper instead. It has been written
> by a couple of former MySQL AB support engineers who started with a
> clean sheet of paper, and it is what mysqldump should have become ten
> years ago. It dumps tables in parallel, doesn't require exclusion of
> schemas that shouldn't be dumped because it knows they shouldn't be
> dumped, doesn't require long strings of arguments to tell it how to
> correctly handle transactional and non-transactional tables because it
> understands both and just Does The Right Thing on a table-by-table
> basis, can dump tables in parallel for better speed, can dump binlogs as
> well as tables, separates the data from the schemas...
>
> Give it a try.
>
Thanks, I'll take a look at it.
> That said, I make my MySQL dump job a lower priority job and run it only
> after all other jobs have completed. This makes sure I get the most
> current possible data in my catalog dump. I just recently switched to a
> revised MySQL backup job that uses mydumper with the following simple
> shell script as a ClientRunBeforeJob on a separate host from the actual
> DB server. (Thus, if the backup client goes down, I still have the live
> DB, and if the DB server goes down, I still have the DB backups on disk.)
>
>
> #!/bin/bash
>
> RETAIN=5
> USER=xxxxxxxxxx
> PASS=xxxxxxxxxx
> DUMPDIR=/dbdumps
> HOST=babylon4
> PORT=6446
> TIMEOUT=300
> FMT='%Y%m%d-%T'
> DEST=${DUMPDIR}/${HOST}-$(date +${FMT})
>
> for dir in $(ls -r ${DUMPDIR} | tail -n +${RETAIN})
> do
> echo Deleting ${DUMPDIR}/${dir}
> rm -rf ${DUMPDIR}/${dir}
> done
>
> mydumper -Cce -h ${HOST} -p ${PORT} -u ${USER} --password=${PASS} -o
> ${DEST} -l ${TIMEOUT}
>
>
> Then my Bacula fileset for the DB-backup job just backs up the entire
> /db-dumps directory.
>
>
--
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
