Hi,
On 1/27/2006 6:15 PM, deann corum wrote:
Hello,
I'm wondering what the largest known Bacula database in existence is.
This is mostly a curiosity point. The actual problem follows.
My catalog DB is much smaller, but it's running on a slow machine.
dbcheck needs hours, not days.
Our Bacula db is 13 GB and we recently tried to run (Bacula's) dbcheck
on it. However, it ran for 2+ days and meanwhile, our backups weren't
running meanwhile so we had to stop (abort, whatever terminology you
prefer) the process.
We're running MySQL 3.23 and Bacula 1.36.3
We weren't originally having any issues with the database or Bacula, but
I've read that Bacula dbcheck should be run from time to time (every 6
months is recommended for some of the checks).
Hmm. As you mention below: If it ain't broke...
dbcheck is, assuming the catalog itself is in a coherent state, mainly
useful to remove records you don't need anymore. This might be entries
in the File and Path tables (although I wouldn't expect to have many
orphaned entries there unless you remove a whole class of similar
machines from backups) and job records from restore jobs.
I had cases where there was something to be cheked in the catalog, but
that always had two reasons: Either a database server crash while a
backup job was running (not very common, no worries) or when I played
around in the catalog manually ;-)
It didn't appear from the output (ie: "no orphaned or duplicate records
found") that the database had been modified up to the point it was stopped.
Good.
Of course we did a dump of the db before starting dbcheck but it took an
equally long time to restore (ie: mysql bacula < baculabackup.sql).
Hmm. I never tried such a database reload with a really big DB, but I
think there are some hints in the MySQL manual. IIRC, you can turn of
indexing operations during such an operation, and only later rebuild the
indexes, which is said to improve performance a lot. Of course, there's
a downside: uniqueness checking might not work in that case, so you
better only try it when you know your data to be ok.
There might be other options, but I'm not sure if they were implemented
in the version you run: --extended-insert is one of those possibly
helpful options, and -disable-keys is disabling indexes during reloading
the database, but requires MySQL 4 and works with MyISAM tables only.
--add-locks might also do some good.
Can anyone provide more information about maximum bacula db sizes,
No idea. All the ones I know from experience are smaller than yours is.
exactly what Bacula's dbcheck does,
Not more information than what I wrote above, and what is in the manual.
how often (or if at all) it should
be run for maintenance purposes, particularly if there are no issues
with the database to begin with (if it ain't broke don't fix it),
I don't see any real reason for it except that it might free up some
unused space. But hard disks are cheap today :-)
and
what condition it is likely to leave the db in if it is aborted?
It shouldn't do any harm. After all, it *should* only touch records for
deletion which aren't referenced in the catalog anymore, which wouldn't
be missed. It doesn't renumber any IDs (which might break the relations
in the catalog in case it crashed), and it only uses the database API,
so I'd say there's nothing to fear.
Is it better to just use just the standard mysql utilities to maintain
the db? Is there a difference?
There is at least one difference: The database tools can't decide if any
row of data in the catalog database is still necessary or should be
deleted, and it can't show inconsistencies there.
On the other hand, with the database tools (mainly mysqldump) and
perhaps a little scripting it should be possible to re-number all IDs.
Well, coming to the point: It might be possible that you're missing
indexes you need for faster database operation. There were versions
where not everything that is considered now was created, and it's even
possible that you removed some index to allow greater day-to-day speed.
You might compare with what I have in the File table (usually, the
biggest table in the catalog):
mysql> show index from File;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| File | 0 | PRIMARY | 1 | FileId | A |
12523154 | NULL | NULL | | BTREE | |
| File | 1 | JobId | 1 | JobId | A |
398 | NULL | NULL | | BTREE | |
| File | 1 | PathId | 1 | PathId | A |
115955 | NULL | NULL | | BTREE | |
| File | 1 | FilenameId | 1 | FilenameId | A |
695730 | NULL | NULL | | BTREE | |
| File | 1 | JobId_2 | 1 | JobId | A |
398 | NULL | NULL | | BTREE | |
| File | 1 | JobId_2 | 2 | PathId | A |
659113 | NULL | NULL | | BTREE | |
| File | 1 | JobId_2 | 3 | FilenameId | A |
12523154 | NULL | NULL | | BTREE | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)
Also, during the everlasting dbcheck you can issue the shell command
'mysqladmin processlist' which would tell you what's going on. If some
query takes very long, you can then use the explain SQL command to see
what's takeing so long. (You'd best get your local SQL guru for support
if there's a problem, I suspect...).
Hope this helps a bit,
Arno
--
IT-Service Lehmann [EMAIL PROTECTED]
Arno Lehmann http://www.its-lehmann.de
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
