On Tue, Feb 26, 2013 at 04:24:02PM +0000, Alan Brown wrote:
> On 26/02/13 09:42, Uwe Schuerkamp wrote:
> 
> >I wonder if dumping the file table and then
> >re-importing it to an innodb replacement would have been quicker?
> 
> In general: Yes.
> 
> 

Hi folks,

it looks like moving over to InnoDB has cut our volume recycle times
at least in half. Performance still isn't exactly stellar, but a lot
better than before:

MyISAM: 
# Time: 130213 13:15:05
# User@Host: bacula[bacula] @ localhost []
# Thread_id: 4806  Schema: bacula  QC_hit: No
# Query_time: 6645.842037  Lock_time: 0.000054  Rows_sent: 0
Rows_examined: 23357041
use bacula;
SET timestamp=1360757705;
DELETE FROM File WHERE JobId IN 
(47054,47056,47058,47060,47062,47064,47066,47068,47070,47072,47074,47076,47078,47080,47082,47084,47086,47088,47089,47090,47092,47095,47222,47353,47355,47357,47359,47361,47363,47365,47367,4739,47371,47374,47376,47378,47380,47382,47384,47386,47388,47390,47392,47394,47396,47398,47400,47402,47403,47404,47408,47412,47421,47546,47548,47550,47551,47552,47677,47679,47681,47683,47685,47687,47689,47691,47693,47695,47697,47699,47701,47703,47705,47707,47709,47711,47713,47715,47717,47719,47721,47723,47725,47727,47729,47730,47731,47975,47977,47979,47981,47983,47985,47987,47989,47991,47993,47995,47997,47999,48001,48003,48005,48007,48009,48011,48013,48015,48017,48019,48021,48023,48024,48025,48032);

InnoDB: 
# Time: 130301 11:51:28
# User@Host: bacula[bacula] @ localhost []
# Thread_id: 3947  Schema: bacula  QC_hit: No
# Query_time: 2233.146560  Lock_time: 0.007595  Rows_sent: 0
# Rows_examined: 25094775
SET timestamp=1362135088;

DELETE FROM File WHERE JobId IN 
(50798,50933,50935,50937,50939,50941,50943,50945,50947,50948,51075,51215,51217,51223,51225,51227,51229,51231,51233,51235,51237,51239,51240,51241,51372,51374,51376,51378,51380,51382,51384,51390,51394,51396,51400,51404,51406,51410,51414,51416,51418,51420,51564,51566,51570,51574,51576,51578,51580,51582,51586,51588,51590,51592,51594,51596,51598,51600,51602,51604,51606,51609,51614,51652,51654,51662,51668,51670,51678,51680,51684,51937,51941,51945);


Granted those jobs could have contained a varying number of files, but
the numbers still look quite impressive ;) 

Is the "rows examined" value equivalent to the number of rows actually
purged from the File table? 

All the best, Uwe 

-- 
NIONEX --- Ein Unternehmen der Bertelsmann SE & Co. KGaA



------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users