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? 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> > > > -- Ulrich Leodolter <[email protected]> Oesterreichische Bibliothekenverbund und Service GmbH Bruennlbadgasse 17/2A, A-1090 Wien Fax +43 1 4035158-30 Tel +43 1 4035158-21 Web http://www.obvsg.at ------------------------------------------------------------------------------ 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
