Hello,
Le mercredi 16 juin 2010 10:08:30, Ulrich Leodolter a écrit :
> 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.
It's a little bit more complex as each database should be optimized
differently, but if this index resolve the performance problem for mysql, we
can plan to add it in the next main release (quite hard to ask for catalog
schema update between two minor release).
(FYI, Sqlite is able to reuse JobId sequence numbers and this index doesn't
improve PostgreSQL performance.)
> i have done this
>
> ALTER TABLE `JobHisto` ADD PRIMARY KEY ( `JobId` )
Could you try by adding just a simple index instead of the primary key ? I
don't remember exactly why I didn't use a unique index on this column, but I
think it was a problem with SQLite that is able to reuse JobIds.
> this gave us a major speed improvement for "update stats"
> it runs now in less then one second!
Thanks for the tips, I think that I will take a look to optimize also other
engines.
Bye
> best regards
> ulrich
--
Need professional help and support for Bacula ?
Visit http://www.baculasystems.com
------------------------------------------------------------------------------
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
