Package: bacula-console-qt
Version: 5.2.6+dfsg-9
Severity: important
Dear Maintainer,
For recovery of files by means of bRestore, it is necessary to execute at first
.bvfs_update.
When trying to build the PathHierarchy with .bvfs_update, the update for big
jobs take forever...
Steps to Reproduce:
- have a big dataset (> 1 million files AND a deep directory hierarchy)
- .bvfs_update job=N
- mysql used 100% CPU very mach time
- at this time no run jobs on the schedule
Additional Information:
I updated bacula from version 5.0 to 5.2 (debian 6 -> 7). In the new bat
version the "Browse Cataloged Files" function became deprecated. It is offered
to use bRestore. My bat hanged at bRestore choice. At this time mysql uses 100%
of Cpu
I enable slow query log on mysql.
The analysis of slow query is lower
TOP 1 from "#mysqldumpslow mysql-slow.log"
Count: 249 Time=243.29s (60579s) Lock=0.00s (0s) Rows=0.0 (0),
bacula[bacula]@localhost
INSERT INTO PathVisibility (PathId, JobId) SELECT a.PathId,N FROM ( SELECT
DISTINCT h.PPathId AS PathId FROM PathHierarchy AS h JOIN PathVisibility AS p
ON (h.PathId=p.PathId) WHERE p.JobId=N) AS a LEFT JOIN (SELECT PathId FROM
PathVisibility WHERE JobId=N) AS b ON (a.PathId = b.PathId) WHERE b.PathId IS
NULL
I carried out the analysis and optimization of this query.
Original query:
INSERT INTO PathVisibility (PathId, JobId)
SELECT a.PathId,N
FROM (
SELECT DISTINCT h.PPathId AS PathId
FROM PathHierarchy AS h
JOIN PathVisibility AS p
ON (h.PathId=p.PathId)
WHERE p.JobId=N) AS a
LEFT JOIN (
SELECT PathId
FROM PathVisibility
WHERE JobId=N) AS b
ON (a.PathId = b.PathId)
WHERE b.PathId IS NULL
Optimized query:
INSERT INTO PathVisibility (PathId, JobId)
SELECT a.PathId,N
FROM (
SELECT DISTINCT h.PPathId AS PathId
FROM PathHierarchy AS h
JOIN PathVisibility AS p
ON (h.PathId=p.PathId)
WHERE p.JobId=N) AS a
LEFT JOIN PathVisibility AS b
ON (b.JobId=N and a.PathId = b.PathId)
WHERE b.PathId IS NULL
Results of query are identical.
In original query in join on the right there is a temporary table (subquery
"SELECT PathId FROM PathVisibility WHERE JobId=N"). The temporary table has no
index across the field of PathId. For each record of the left table there is a
full search of the right table.
The table PathVisibility has a compound index on the fields JobId and PathId.
In the optimized query join is carried out with use of this index.
After introduction of editings and recompilation bacula of .bvfs_update it was
executed in 17 minutes. With old inquiry in 16-17 hours it didn't end and was
still very far from end.
Count: 19052 Time=0.01s (152s) Lock=0.00s (1s) Rows=0.0 (0),
bacula[bacula]@localhost
INSERT INTO PathVisibility (PathId, JobId) SELECT a.PathId,N FROM ( SELECT
DISTINCT h.PPathId AS PathId FROM PathHierarchy AS h JOIN PathVisibility AS p
ON (h.PathId=p.PathId) WHERE p.JobId=N) AS a LEFT JOIN PathVisibility AS b ON
(JobId=N and a.PathId = b.PathId) WHERE b.PathId IS NULL
The similar situation was stated in
http://sourceforge.net/mailarchive/forum.php?thread_name=3ccaa3a6fb746ee27273ef0b1db9045b%40bugs.bacula.org&forum_name=bacula-bugs
I have a base in mysql. So detailed check is necessary for other bases. I asked
the friend to check on postgresql for a lot of work. After 1,5 hours of
processing of one big job were compelled to stop postgresql.
Patch:
diff -Naur bacula-5.2.6+dfsg/src/cats/bvfs.c_orig
bacula-5.2.6+dfsg/src/cats/bvfs.c
--- bacula-5.2.6+dfsg/src/cats/bvfs.c_orig 2012-06-02 13:26:27.000000000
+0400
+++ bacula-5.2.6+dfsg/src/cats/bvfs.c 2014-02-06 10:42:34.798440879 +0400
@@ -404,10 +404,9 @@
"SELECT DISTINCT h.PPathId AS PathId "
"FROM PathHierarchy AS h "
"JOIN PathVisibility AS p ON (h.PathId=p.PathId) "
- "WHERE p.JobId=%s) AS a LEFT JOIN "
- "(SELECT PathId "
- "FROM PathVisibility "
- "WHERE JobId=%s) AS b ON (a.PathId = b.PathId) "
+ "WHERE p.JobId=%s) AS a "
+ "LEFT JOIN PathVisibility AS b "
+ "ON (b.JobId=%s and a.PathId = b.PathId) "
"WHERE b.PathId IS NULL", jobid, jobid, jobid);
}
-- System Information:
Debian Release: 7.3
APT prefers stable-updates
APT policy: (500, 'stable-updates'), (500, 'stable')
Architecture: amd64 (x86_64)
Kernel: Linux 3.2.0-4-amd64 (SMP w/4 CPU cores)
Locale: LANG=C, LC_CTYPE=C (charmap=ANSI_X3.4-1968)
Shell: /bin/sh linked to /bin/dash
Versions of packages bacula-console-qt depends on:
ii bacula-common 5.2.6+dfsg-9
ii libc6 2.13-38
ii libcap2 1:2.22-1.2
ii libgcc1 1:4.7.2-5
ii libqtcore4 4:4.8.2+dfsg-11
ii libqtgui4 4:4.8.2+dfsg-11
ii libssl1.0.0 1.0.1e-2+deb7u3
ii libstdc++6 4.7.2-5
ii libwrap0 7.6.q-24
ii zlib1g 1:1.2.7.dfsg-13
bacula-console-qt recommends no packages.
bacula-console-qt suggests no packages.
-- no debconf information
--
To UNSUBSCRIBE, email to [email protected]
with a subject of "unsubscribe". Trouble? Contact [email protected]
