Re: [Bacula-devel] small mysql patch

[Eric B. <er...@eb...>]

Hello Yuri,

Adding indexes will probably speed up dbcheck, but it will slow down the 
attribute insertion process and grow up the database.

The dbcheck operation have to be run once per month, maybe twice per year.
So, we can say that speed of attribute insertion is much more important than 
dbcheck performance.

If you want to use new indexes with dbcheck, you can modify dbcheck.c in this 
way :
 - ask to the user if he wants to add them (think about disk space)
 - add them + run analyse
 - run the cleanup operation
 - remove them

I'm not sure that all your indexes are useful, for example (FileId, JobId) is 
probably used to see if you have orphan jobids in File table. The Job table 
is very small compare to others, so the database engine won't use it.

Here, it's not the type of your index that will be important, it's much more 
the size of the File table (hundred of million in my case) that will change 
the cost of your index.

FYI, postgresql can use composite indexes, so it will require less indexes 
than mysql.

Bye

Le Sunday 31 August 2008 19:59:03 Yuri Timofeev, vous avez écrit :
> Hi,
>
> I believe that the results of this discussion concerns
>
> "[Bacula-users] Bacula 2.2.8, dbcheck never completes"
> http://sourceforge.net/mailarchive/forum.php?thread_name=48A984BF.4050206%4
>0umdnj.edu&forum_name=bacula-users
>
> requires a small patch, see attached file.
>
> Since all fields (which fall in the indices) type INTEGER  the size of
> the indices will be low and increase speed dbcheck will be very
> substantial.
>
> As you think?
>
> ps. I think the same indices are needed for DB Postgresql.