sorry for necromancing an old thread, but i just setup a new server with
bacula 5.0.1 and mysql catalog and i ran into the same problem
catalog db is new, created with an unmodified make_mysql_tables.
two days ago I started backing it up.
Full backup was 539,971 files, 75.62GB, so i would not call it big
in the Fileset i had set
signature = SHA1
accurate = mcs1
this morning i found the first incremental was still busy running an sql
query (which alocated a whole core for 8 hours).
i killed that, tried removing "accurate = mcs1" from the fileset,
i ran the incremental again, but mysql is still taking a long time:
| 133 | bacula | localhost | bacula | Query | 8114 | executing |
SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId,
LStat, MD5 FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex,
File.PathId AS PathId, File.FilenameId AS FilenameId, LStat, MD5 FROM
Job, File, ( SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId FROM (
SELECT JobTDate, PathId, FilenameId FROM File JOIN Job USING (JobId)
WHERE File.JobId IN (2) UNION ALL SELECT JobTDate, PathId, FilenameId
FROM BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId =
Job.JobId) WHERE BaseFiles.JobId IN (2) ) AS tmp GROUP BY PathId,
FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM
BaseFiles WHERE JobId IN (2)) OR Job.JobId IN (2)) AND T1.JobTDate =
Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = File.PathId AND
T1.FilenameId = File.FilenameId ) AS Temp JOIN Filename ON
(Filename.FilenameId = Temp.FilenameId) JOIN Path ON (Path.PathId =
Temp.PathId) WHERE FileIndex > 0 ORDER BY Temp.JobId, FileIndex ASC |
I understand Eric concerns about the (FilenameId, PathId) index, but the
above situation is absolutely unbearable.
any clues on how to make it work?
thanks in advance,
L.
On Sun, Feb 21, 2010 at 10:51:02PM +0100, Eric Bollengier wrote:
>Unfortunately, this index creates lots of performance problem during
>batch insert session due to index bloat. This is not a good idea to
>advise it :(
>
>http://sourceforge.net/apps/wordpress/bacula/2009/09/28/performance-issue-with-a-useless-index-on-postgresql/
>
>
>Le Samedi 20 Février 2010 23:30:45, Dan Langille a écrit :
>> issue. make_mysql_tables suggests to add INDEX (FilenameId, PathId) on
>> the File table if verifies are too slow--it also recommends several
>> other indices, all of which I already had (PathId, FilenameId and
>> JobId). I ran this sql query:
>> CREATE INDEX FilenameId_2 ON File (FilenameId, PathId);
>> Which took quite a while (maybe 20-30 minutes?)....
--
Luca Berra -- [email protected]
Communication Media & Services S.r.l.
/"\
\ / ASCII RIBBON CAMPAIGN
X AGAINST HTML MAIL
/ \
------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the
lucky parental unit. See the prize list and enter to win:
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
Bacula-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
