Le Monday 18 May 2009 12:55:00 Ulrich Leodolter, vous avez écrit : > Hello, > > On Mon, 2009-05-18 at 12:13 +0200, Eric Bollengier wrote: > > Le Monday 18 May 2009 11:52:26 Ulrich Leodolter, vous avez écrit : > > > On Mon, 2009-05-18 at 11:24 +0200, Eric Bollengier wrote: > > > > Hello, > > > > > > > > Le Monday 18 May 2009 10:35:35 Ulrich Leodolter, vous avez écrit : > > > > > Hello, > > > > > > > > > > after bresto client, job and file selection > > > > > and clicking on "Run restore" i tried to find > > > > > Media needed by clicking "Compute with directories". > > > > > > > > > > Result: nothing and browser timeout. > > > > > > > > > > After browser timeout i looked into mysql (see below). > > > > > > > > > > There is a subselect (SELECT FileId FROM b213335), but > > > > > temporary table b213335 has no column FileId. > > > > > > > > I should have fixed this bug yesterday, so please update your version > > > > and try again. I have also added an index to speed up the (SELECT > > > > FileId) join for mysql. > > > > > > I saw your comments in bweb/technotes-3.0 and i did an update/install > > > before trying/reporting it again. > > > > A bit strange, it works here > > > > mysql> describe b230187; > > +------------+---------------------+------+-----+---------+-------+ > > > > | Field | Type | Null | Key | Default | Extra | > > > > +------------+---------------------+------+-----+---------+-------+ > > > > | JobId | int(10) unsigned | NO | | NULL | | > > | FileIndex | int(10) unsigned | YES | | 0 | | > > | FilenameId | int(10) unsigned | NO | | NULL | | > > | PathId | int(10) unsigned | NO | | NULL | | > > | FileId | bigint(20) unsigned | NO | MUL | 0 | | > > > > +------------+---------------------+------+-----+---------+-------+ > > > > And i see in your example that your table has also a FileId field (the > > last one). The operation takes time because mysql doesn't support > > DISTINCT with criteria, maybe we can avoid the big query, but for that i > > need to take a look more deeply. > > Sorry, you right, FileId is there > > mysql> select count(FileId) FROM b217790; > +---------------+ > > | count(FileId) | > > +---------------+ > > | 832 | > > +---------------+ > 1 row in set (0.00 sec) > > But the whole mysql query keeps mysqld running at 100% > so i think its a performance problem. > > The inner select works > > mysql> SELECT MIN(FirstIndex) AS FirstIndex, MAX(LastIndex) AS > LastIndex, VolumeName, Enabled, Inchanger FROM > JobMedia JOIN Media USING (MediaId) WHERE JobId IN (SELECT > DISTINCT JobId FROM b217790) GROUP BY > VolumeName,Enabled,InChanger; > +------------+-----------+-------------+---------+-----------+ > > | FirstIndex | LastIndex | VolumeName | Enabled | Inchanger | > > +------------+-----------+-------------+---------+-----------+ > > | 1 | 17670 | Backup-0408 | 1 | 0 | > > +------------+-----------+-------------+---------+-----------+ > 1 row in set (8.76 sec) > > But the outer select on File runs very long. > My File table has 97592965 entries. > > mysql> SELECT DISTINCT VolumeName, Enabled, InChanger FROM File, > ( SELECT MIN(FirstIndex) AS FirstIndex, MAX(LastIndex) AS > LastIndex, VolumeName, Enabled, Inchanger FROM > JobMedia JOIN Media USING (MediaId) WHERE JobId IN (SELECT > DISTINCT JobId FROM b217790) GROUP BY > VolumeName,Enabled,InChanger ) AS allmedia WHERE File.FileId IN > (SELECT FileId FROM b217790) AND File.FileIndex >= > allmedia.FirstIndex AND File.FileIndex <= allmedia.LastIndex; > +-------------+---------+-----------+ > > | VolumeName | Enabled | InChanger | > > +-------------+---------+-----------+ > > | Backup-0408 | 1 | 0 | > > +-------------+---------+-----------+ > 1 row in set (3 min 49.80 sec) > > > Maybe an index on File.FileIndex would help, what do you think?
Thanks to point me this problem in this way. This feature is new, (and not well implemented) it uses a piece of code that runs fine with a file list. Now, I'm pretty sure that we don't need to join the File table to compute the list of media, since we have the fileindex and the jobid in the temporary table, we should be able to join directly the JobMedia table. It should be very fast. (at least for mysql, postgresql doesn't seems to have this problem) I will work on that soon. Bye > Regards > Ulrich > > > > Now i double checked my bweb installtion, everything is up to date > > > at revision 8838. > > > > Bye > > > > > BR > > > Ulrich > > > > > > > Bye > > > > > > > > > Looks like a bresto bug. > > > > > > > > > > Regards > > > > > Ulrich > > > > > > > > > > > > > > > > > > > > > > > > > mysql> show full processlist; > > > > > +-------+--------+-----------+--------+---------+------+----------- > > > > >+--- ---- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >+ > > > > > > > > > > | Id | User | Host | db | Command | Time | State > > > > > | | > > > > > > > > > > Info > > > > > > > > > > +-------+--------+-----------+--------+---------+------+----------- > > > > >+--- ---- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >+ > > > > > > > > > > | 29820 | bacula | localhost | bacula | Query | 25 | preparing > > > > > | | > > > > > > > > > > SELECT DISTINCT VolumeName, Enabled, InChanger > > > > > FROM File, > > > > > ( -- Get all media from this job > > > > > SELECT MIN(FirstIndex) AS FirstIndex, MAX(LastIndex) AS > > > > > LastIndex, VolumeName, Enabled, Inchanger > > > > > FROM JobMedia JOIN Media USING (MediaId) > > > > > WHERE JobId IN (SELECT DISTINCT JobId FROM b213335) > > > > > GROUP BY VolumeName,Enabled,InChanger > > > > > ) AS allmedia > > > > > WHERE File.FileId IN (SELECT FileId FROM b213335) > > > > > AND File.FileIndex >= allmedia.FirstIndex > > > > > AND File.FileIndex <= allmedia.LastIndex | > > > > > > > > > > | 29821 | root | localhost | bacula | Query | 0 | NULL > > > > > | | > > > > > > > > > > show full processlist > > > > > > > > > > +-------+--------+-----------+--------+---------+------+----------- > > > > >+--- ---- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >---- ----- > > > > > ------------------------------------------------------------------- > > > > >+ 2 rows in set (0.00 sec) > > > > > > > > > > mysql> describe b213335; > > > > > +------------+---------------------+------+-----+---------+-------+ > > > > > > > > > > | Field | Type | Null | Key | Default | Extra | > > > > > > > > > > +------------+---------------------+------+-----+---------+-------+ > > > > > > > > > > | JobId | int(10) unsigned | NO | | | | > > > > > | FileIndex | int(10) unsigned | YES | | 0 | | > > > > > | FilenameId | int(10) unsigned | NO | | | | > > > > > | PathId | int(10) unsigned | NO | | | | > > > > > | FileId | bigint(20) unsigned | NO | MUL | 0 | | > > > > > > > > > > +------------+---------------------+------+-----+---------+-------+ > > > > > 5 rows in set (0.00 sec) > > > > > > > > > > mysql> ------------------------------------------------------------------------------ Crystal Reports - New Free Runtime and 30 Day Trial Check out the new simplified licensing option that enables unlimited royalty-free distribution of the report engine for externally facing server and web deployment. http://p.sf.net/sfu/businessobjects _______________________________________________ Bacula-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/bacula-devel
