hi,
we are using "update stats days=3" to update
the JobHisto table for bweb. as the number of jobs are
growing linear over time the query time for update stats
sql is growing exponential (mysql 5.0.77 catalog)
this is the last update from sunday, it took 6945 seconds :-(
# Query_time: 6945 Lock_time: 0 Rows_sent: 0 Rows_examined: 2128037901
INSERT INTO JobHisto (JobId, Job, Name, Type, Level, ClientId, JobStatus,
SchedTime, StartTime, EndTime, RealEndTime, JobTDate, VolSessionId,
VolSessionTime, JobFiles, JobBytes, Re
adBytes, JobErrors, JobMissingFiles, PoolId, FileSetId, PriorJobId,
PurgedFiles, HasBase, Reviewed, Comment ) SELECT JobId, Job, Name, Type, Level,
ClientId, JobStatus, SchedTime,
StartTime, EndTime, RealEndTime, JobTDate, VolSessionId, VolSessionTime,
JobFiles, JobBytes, ReadBytes, JobErrors, JobMissingFiles, PoolId, FileSetId,
PriorJobId, PurgedFiles, HasB
ase, Reviewed, Comment FROM Job WHERE JobStatus IN ('T','W','f','A','E') AND
JobId NOT IN (SELECT JobId FROM JobHisto) AND JobTDate < 1276147554;
we have about 90000 entries in JobHisto and 20000 in Job.
i would suggest creating a primary index on JobId in JobHisto.
i have done this
ALTER TABLE `JobHisto` ADD PRIMARY KEY ( `JobId` )
this gave us a major speed improvement for "update stats"
it runs now in less then one second!
best regards
ulrich
--
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
------------------------------------------------------------------------------
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
