Hello,
On Wed, 2010-06-16 at 10:45 +0200, Eric Bollengier wrote:
> 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.
>
simple index results in same improvement, update stats < 1 second
ALTER TABLE `JobHisto` ADD INDEX ( `JobId` )
best regards
ulrich
> > 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
>
--
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
