On Tue, Apr 27, 2010 at 02:15:35PM +0200, Foo wrote:
> On Mon, 26 Apr 2010 15:57:53 +0200, Matija Nalis <mnalis+bacula AT carnet DOT
> hr>
> >> Also, can I expect problems if I added some extra indexes for speed and
> >> whatever tables/indexes were added by webacula?
> >
> > yes, you can expect problems :)
> >
> > For example,
> > http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog
> > http://wiki.bacula.org/doku.php?id=faq#jobs_with_accurate_filesets_take_forever_deprecated
> > http://bugs.bacula.org/view.php?id=1472
>
> Thanks, I'll see what I can remove. Can't access the last link though.
You may need to create account in Mantis for that last one (there is
a link below login form to which you get redirected)
> The indexes did help a lot with dbcheck though, without them it takes days
> to complete instead of minutes, will this speed up in 3.x/5.x or has
> dbcheck been updated, is it not necessary anymore, or is this something
> that needs to be fixed in a future release?
The issue is in MySQL, as its algorithm sometimes chooses terribly
wrong index, so SELECT times go up easily from 5 minutes to more than
20 hours. If they make MySQL choose good index every time, the
problem will go away.
Nowdays it is recommended to go with PostgreSQL instead AFAICT.
As for dbcheck(8), if I recall correctly if indexes are not present,
it will create indexes on start and drop them on finish (after
asking).
You can automate it with:
"yes no | dbcheck -b -f -c /etc/bacula/bacula-dir.conf"
if you do not want it to create indexes...
(if you want it to create indexes on the fly, you modify that...
probably something like "yes yes" instead of "yes no", but I haven't
checked it)
> I have the following indexes on File currently:
>
> Non_unique Seq_in_index
> || Key_name | | | Column_name | Cardinality
> |+----------------------------+ | +-------------+------------
> 0| PRIMARY | 1 | FileId | 6387461
> 1| JobId | 1 | JobId | 2750
> 1| JobId | 2 | PathId | 638746
> 1| JobId | 3 | FilenameId | 6387461
> 1| File_JobId_idx | 1 | JobId | 2750
> 1| File_PathId_idx | 1 | PathId | 206047
> 1| File_FilenameId_idx | 1 | FilenameId | 912494
> 1| File_FilenameId_PathId_idx | 1 | FilenameId | 912494
> 1| File_FilenameId_PathId_idx | 2 | PathId | 2129153
>
> Collation are all A, Sub_part, Packed are all NULL, Null and Comment are
> all empty, Index_type is all BTREE
You need to retaint only primary key, and two indexes: one on column
"JobId" and one on composite "JobId,PathId,FilenameId". All other
indexes should be dropped on MySQL table File.
> Which of these can be deleted exactly? (and how, I'm not a DBA :)
drop index File_PathId_idx on File;
drop index File_FilenameId_idx on File;
drop index File_FilenameId_PathId_idx on File;
I'm not sure about other tables; if you modified "Job" table that
might also slow you down, but probably much less (it needs only
primary key, and index on "Name").
------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
