Hello,
Graham Keeling wrote:
>
> Hello,
>
> I now believe that the 'taking hours' problem that I was having was
> down to having additional indexes on my File table, as Eric suggested.
>
> I am using mysql-5.0.45.
>
> I had these indexes:
> JobId
> JobId, PathId, FilenameId
> PathId
> FilenameId
>
> Now I have these indexes:
> JobId
> JobId, PathId, FilenameId
>
> The queries on my 'real' database now take about a second, rather than
> half a
> day.
>
>
Nice to see that my first advise was the good one.
>
> A suggestion - perhaps the following comment in
> src/cats/make_mysql_tables.in
> could be changed to include a warning:
>
> #
> # Possibly add one or more of the following indexes
> # to the above File table if your Verifies are
> # too slow.
> #
> # INDEX (PathId),
> # INDEX (FilenameId),
> # INDEX (FilenameId, PathId)
> # INDEX (JobId),
> #
>
>
>
> However, I also tested the 3.0.3 and 5.0.1 queries using Eric's test
> script and
> the much larger database that it generates.
> I found that there is a definite slowdown.
>
> Results from do_bench("10,13", 2200000). In this case, the slowdown is
> about
> 15%.
>
> new|2200000|2200000|312
> old|2200000|2200000|268
> graham|2200000|2200000|158
>
> Result 'graham' is the time it takes to do a query that I came up with
> that
> looks similar to the postgresql query, but uses the mysql group by trick
> that
> is frowned upon:
>
> SELECT MAX(JobTDate) AS JobTDate, JobId, FileId, FileIndex, PathId,
> FilenameId, LStat, MD5 FROM
> (SELECT JobTDate, JobId, FileId, FileIndex, PathId, FilenameId, LStat,
> MD5
> FROM
> (SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5
> FROM File WHERE JobId IN ($jobid)
> UNION ALL
> SELECT File.FileId, File.JobId, PathId, FilenameId,
> File.FileIndex, LStat, MD5
> FROM BaseFiles JOIN File USING (FileId)
> WHERE BaseFiles.JobId IN ($jobid)
> ) AS T JOIN Job USING (JobId)
> ORDER BY FilenameId, PathId, JobTDate DESC ) AS U
> GROUP BY PathId, FilenameId
>
>
I don't think it will work, the documentation saids that if two records have
the same FilenameId, PathId, the value for LStat, MD5, JobId and FileIndex
will be a random one (which is not acceptable in our case).
Thanks for your feeback and your help.
Bye
--
View this message in context:
http://old.nabble.com/VirtualFull-mysql-query-blocks-other-jobs-for-a-long-time-tp28149748p28247276.html
Sent from the Bacula - Users mailing list archive at Nabble.com.
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
