Backup Central

On 02/06/2012 02:45 PM, Phil Stracchino wrote:
On 02/06/2012 05:02 PM, Stephen Thompson wrote:
So, my question is whether anyone had any ideas about the feasibility of
getting a backup of the Catalog while a single "long-running" job is
active? This could be in-band (database dump) or out-of-band (copy of
database directory on filesystem or slave database server taken
offline). We are using MySQL, but would not be opposed to switching to
PostGRES if it buys us anything in this regard.

What I wonder specifically (in creating my own solution) is:
1) If I backup the MySQL database directory, or sync to a slave server
and create a dump from that, am I simply putting the active
"long-running" job records at risk of being incoherent, or am I risking
the integrity of the whole Catalog in doing so?
2) If I attempt a dump of the MySQL catalog and lock the tables while
doing so, what will the results be to the active "long-running" job?
Will it crap out or simply pause and wait for database access when it
needs to read/write to the database? And if so, how long will it wait?

Stephen,
Three suggestions here.

Route 1:
Set up a replication slave and perform your backups from the slave. If
the slave falls behind the master while you're dumping the DB, you don't
really care all that much. It doesn't impact your production DB.

Route 2:
If you're not using InnoDB in MySQL, you should be by now. So look into
the --skip-opt and --single-transaction options to mysqldump to dump all
of the transactional tables consistently without locking them. Your
grant tables will still need a read lock, but hey, you weren't planning
on rewriting your grant tables every day, were you...?



Well, we've made the leap from MyISAM to InnoDB, seems like we win on
transactions, but lose on read speed.

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?

thanks!
Stephen





Route 3:
Look into an alternate DB backup solution like mydumper or Percona
XtraBackup.

Route 4:
Do you have the option of taking a snapshot of your MySQL datadir and
backing up the snapshot? This can be viable if you have a small DB and
fast copy-on-write snapshots. (It's the technique I'm using at the
moment, though I'm considering a switch to mydumper.)




--
Stephen Thompson Berkeley Seismological Laboratory
stephen < at > seismo.berkeley.edu 215 McCone Hall # 4760
404.538.7077 (phone) University of California, Berkeley
510.643.5811 (fax) Berkeley, CA 94720-4760

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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.

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.

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.

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.


--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
alaric < at > caerllewys.net alaric < at > metrocast.net phil < at > co.ordinate.org
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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
stephen < at > 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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On 04/02/2012 06:06 PM, Stephen Thompson wrote:


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.

Ah, well, sometimes there's only so much you can allocate.

--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?

Hmm. I stand corrected. I've never seen it listed as an option in the
man page, despite there being one reference to it, but I see that
mysqldump --help does explain it even though the man page doesn't.

In that case, the only thing I can think of is that you have multiple
jobs trying to insert attributes at the same time and the last ones in
line are timing out.

(Locking the table for batch attribute insertion actually isn't
necessary; MySQL can be configured to interleave auto_increment inserts.
However, that's the way Bacula does it.)

Don't know that I have any helpful suggestions there, then... sorry.



--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
alaric < at > caerllewys.net alaric < at > metrocast.net phil < at > co.ordinate.org
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.

------------------------------------------------------------------------------
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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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.

__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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

--
Stephen Thompson Berkeley Seismological Laboratory
stephen < at > 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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On 4/2/12 3:33 PM, Phil Stracchino wrote:
On 04/02/2012 06:06 PM, Stephen Thompson wrote:


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.

Ah, well, sometimes there's only so much you can allocate.

--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?

Hmm. I stand corrected. I've never seen it listed as an option in the
man page, despite there being one reference to it, but I see that
mysqldump --help does explain it even though the man page doesn't.

In that case, the only thing I can think of is that you have multiple
jobs trying to insert attributes at the same time and the last ones in
line are timing out.

(Locking the table for batch attribute insertion actually isn't
necessary; MySQL can be configured to interleave auto_increment inserts.
However, that's the way Bacula does it.)

Don't know that I have any helpful suggestions there, then... sorry.




Thanks again for the response, just bouncing this issue off someone is
of help.

You idea about the jobs simply running into contention for locks sounds
reasonable, though I never saw this happening with MyISAM (in the 3+
years we've run bacula, and I see it the 2nd night into running InnoDB).
If so, I wouldn't mind estimating the maximum time my jobs might have to
wait for a lock, based on their size and concurrency, but I really hate
just tweaking settings in the DB without knowing why I'm doing so, you
know. I'd like to get to the bottom of what's causing the timeout.

thanks,
Stephen
--
Stephen Thompson Berkeley Seismological Laboratory
stephen < at > 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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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.

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.


--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
alaric < at > caerllewys.net alaric < at > metrocast.net phil < at > co.ordinate.org
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.

------------------------------------------------------------------------------
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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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
stephen < at > 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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On 04/02/2012 03:33 PM, Phil Stracchino wrote:
On 04/02/2012 06:06 PM, Stephen Thompson wrote:


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.

Ah, well, sometimes there's only so much you can allocate.

--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?

Hmm. I stand corrected. I've never seen it listed as an option in the
man page, despite there being one reference to it, but I see that
mysqldump --help does explain it even though the man page doesn't.

In that case, the only thing I can think of is that you have multiple
jobs trying to insert attributes at the same time and the last ones in
line are timing out.



This appears to be the root cause. After running a few more nights, the
coincidence with the Catalog dump was not maintained. It happens for a
few jobs each night, at different times, different jobs, and sometimes
when no Catalog dump is occurring.

I think it's simply that a bunch of batch inserts wind up running at the
same time and the last in line run out of time. Rather than setting my
timeout arbitrarily large (10 minutes did not solve the problem), I am
curious about what you say below.

(Locking the table for batch attribute insertion actually isn't
necessary; MySQL can be configured to interleave auto_increment inserts.
However, that's the way Bacula does it.)

Are you saying that if I turn on auto_increment inserts in MySQL, it
won't matter whether or not bacula is asking for locks during batch
inserts? Or does bacula also need to be configured (patched) not to use
locks during batch inserts?

And lastly, why does the bacula documentation claim that locks are
'essential' for batch inserts and you claim they are not?

I'm surprised more folks running mysql InnoDB and bacula aren't having
this problem since I stumbled upon it so easily. Perhaps the trend
is MySQL MyISAM --> Postgres.



Don't know that I have any helpful suggestions there, then... sorry.




thanks!
Stephen
--
Stephen Thompson Berkeley Seismological Laboratory
stephen < at > 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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On 04/05/2012 02:41 PM, Stephen Thompson wrote:
On 04/02/2012 03:33 PM, Phil Stracchino wrote:
(Locking the table for batch attribute insertion actually isn't
necessary; MySQL can be configured to interleave auto_increment inserts.
However, that's the way Bacula does it.)

Are you saying that if I turn on auto_increment inserts in MySQL, it
won't matter whether or not bacula is asking for locks during batch
inserts? Or does bacula also need to be configured (patched) not to use
locks during batch inserts?

You would have to patch Bacula to not issue an explicit LOCK TABLE. It
does not at present contain any option to not lock the table during
inserts. This is something I've meant to experiment with myself for
some time, to compare performance, but haven't managed to get to it. It
hasn't been a high priority for me since I don't ever have more than
about five or six jobs running, and they basically never finish up at
the same time anyway.

And lastly, why does the bacula documentation claim that locks are
'essential' for batch inserts and you claim they are not?

Basically, if interleaved mode is NOT enabled, multiple batch inserts
will contend for access to the table, since even if Bacula does not lock
the table, InnoDB will set a global AUTO_INC lock on the table any time
you attempt to insert an indeterminate number of rows into a table
containing an auto_increment field.

(In this context, actually, on InnoDB, having Bacula issue a LOCK TABLE
is redundant; InnoDB is going to lock the table anyway until the thread
is done inserting rows.)

Setting innodb_autoinc_lock_mode = 2 enables multiple threads to
interleave inserts into the same table and guarantees that they will
still get unique auto_increment row IDs; it simply does not guarantee
that the IDs allocated to any given thread will be consecutive.
However, I'm pretty sure Bacula is not written sufficiently incorrectly
to care whether it gets consecutive row IDs.


--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
alaric < at > caerllewys.net alaric < at > metrocast.net phil < at > co.ordinate.org
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.

------------------------------------------------------------------------------
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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users