Graham Keeling wrote:
>  
>> > I don't understand this at all.
>> > If you cannot trust the JobIds or FileIds in the File table, then the
>> > postgres
>> > query is also broken. The postgres query doesn't even mention JobTDate.
>> > In fact, the postgres query is using StartTime to do the ordering.
>> > 
>> 
>> And JobTDate is equivalent to StartTime (can be changed in PostgreSQL or
>> in
>> MySQL)
> 
> I still don't understand your original point.
> If you cannot trust the JobIds or FileIds in the File table, then the
> postgres
> query is also broken.
> 
> 

No, the PostgreSQL query is using StartTime to find the latest record for a
given
(FilenameId, PathId) tuple, it's not broken. see sql_cmd.c. The DISTINCT
ON() permits
to use other fields of this record directly (which seems to be the normal
way to do things)



> 
> To clarify, can you tell me which fields in the File table you can trust?
> 
> 

You don't have direct File table fields that gives you info about the backup
time. (only somewhere
in the LStat field, but it will be hard to extract, performance wouldn't be
there, and you have to
join the File table again to get MD5 and the rest of the LStat field).

The main difference between the 3.0.3 query and the 5.0.x one, is that
instead of using FileId to
join the table a second time to get all info, it uses JobId, FilenameId and
PathId. We already have an
index on those 3 criteria, and it should be a bit slower, but nothing to do
with hours or days of work.

What is possible, is that MySQL is trying to use an other index (that you
added), and the result is
worst than the original plan with the composed index.



> 
>> Even if we found a workaround for MySQL, Postgres will stay far more
>> faster
>> (For the 2M file query, postgres was about 12s, MySQL 3.0.3 at 60s and
>> MySQL
>> 5.0.1 at 90s)
>> 
>> Can you confirm that the BaseJob doesn't change your timing ?
> 
> I think that I must have made some sort of mistake when I was testing
> that. I do not believe that it changes my timing enough to help with the
> problem that I have.
> 
> I also think that there must be some kind of mistake with your test
> program
> because the slowness of the query between the 3.0.3 query and the 5.0.1
> query
> is very, very massive on my real database.
> 
> 

I'm really thinking that the problem is on the MySQL side (bad version
perhaps), or on your
modifications (my tests shows that with a FilenameId, PathId index, results
are 10 times slower than
with the default indexes)

What version of MySQL are you using ? (and on which OS)



> 
> I will attempt to prove this soon (I still haven't set up your test
> database
> properly).
> 
> 

Here, it takes less than 1 hour (maybe 20 mins), to get the job done...
(It's a Ubuntu with MySQL 5.0.51a, and i'm also using MySQL 5.1.45)

Bye
-- 
View this message in context: 
http://old.nabble.com/VirtualFull-mysql-query-blocks-other-jobs-for-a-long-time-tp28149748p28190830.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