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