[prev in list] [next in list] [prev in thread] [next in thread]
List: bacula-devel
Subject: Re: [Bacula-devel] bweb bresto mysql problem
From: Ulrich Leodolter <ulrich.leodolter () obvsg ! at>
Date: 2009-05-22 12:35:56
Message-ID: 1242995756.6261.39.camel () leodolter ! bibvb ! ac ! at
[Download RAW message or body]
Hello,
I have updated bweb + bresto to latest svn.
But there ist still a mysql performance problem/bug :-(
The following sub query from function get_media_list_with_dir()
runs very long:
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 b25952) GROUP BY
VolumeName,Enabled,InChanger;
+------------+-----------+-------------+---------+-----------+
| FirstIndex | LastIndex | VolumeName | Enabled | Inchanger |
+------------+-----------+-------------+---------+-----------+
| 1 | 412 | Backup-0169 | 1 | 0 |
| 1 | 239101 | Backup-0408 | 1 | 0 |
| 1 | 3536 | Backup-0632 | 1 | 0 |
+------------+-----------+-------------+---------+-----------+
3 rows in set (7 min 48.06 sec)
I have run the following queries while the above was running.
As u can see, without sub select, it is very fast.
mysql> SELECT DISTINCT JobId FROM b25952
-> ;
+-------+
| JobId |
+-------+
| 24089 |
| 24201 |
| 24319 |
| 24437 |
| 24551 |
+-------+
5 rows in set (0.00 sec)
mysql> SELECT MIN(FirstIndex) as FirstIndex, MAX(LastIndex) as
LastIndex, VolumeName, Enabled, Inchanger FROM JobMedia JOIN Media USING
(MediaId) WHERE JobId IN ('24089','24201','24319','24437','24551') GROUP
BY VolumeName,Enabled,InChanger;
+------------+-----------+-------------+---------+-----------+
| FirstIndex | LastIndex | VolumeName | Enabled | Inchanger |
+------------+-----------+-------------+---------+-----------+
| 1 | 412 | Backup-0169 | 1 | 0 |
| 1 | 239101 | Backup-0408 | 1 | 0 |
| 1 | 3536 | Backup-0632 | 1 | 0 |
+------------+-----------+-------------+---------+-----------+
3 rows in set (0.00 sec)
I am running mysql-5.0.45 coming with centos/rhel 5.3.
Looks like i need a mysql upgrade
Best Regards
Ulrich
On Mon, 2009-05-18 at 13:10 +0200, Eric Bollengier wrote:
> 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>
>
>
>
--
Ulrich Leodolter <ulrich.leodolter@obvsg.at>
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
------------------------------------------------------------------------------
Register Now for Creativity and Technology (CaT), June 3rd, NYC. CaT
is a gathering of tech-side developers & brand creativity professionals. Meet
the minds behind Google Creative Lab, Visual Complexity, Processing, &
iPhoneDevCamp asthey present alongside digital heavyweights like Barbarian
Group, R/GA, & Big Spaceship. http://www.creativitycat.com
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic