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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users