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