Hi folks,

for the record I'd like to give you some stats from our recent myisam
-> innodb conversion. 

The server in question runs mariadb 5.5.27 on centos 6 64bit (latest
patches), has 18GB of RAM with a RAID5 built over 8 internal SAS disks
(7 + one spare) which stores the mysql tables. 

Our file table has 1,1 billion entries (mostly due to copy job
madness) and is about 160gb in size (myisam). 

Converting this table to innodb with the server on the following
settings 

key_buffer = 1G
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=12G
innodb_log_buffer_size=4M 
innodb_thread_concurrency=8

and the machine mostly idle otherwise took 2 days and 55 seconds. 

For this process, mysql's tmpdir was set to our online backup storage
device (HP MSA60) to be able to spread the load over several disks (2
storage boxes acting as one large RAID6 40TB device on 24 2TB SAS disks). 
 
We'll now do some further testing to see if dump speed has improved
and how writing during backups and volume expiry performs. 

The File table space has grown from 160GB in MyISAM to a whopping
294GB on InnoDB, what could be the reason for this apart from the
indices now being stored within the IBD file? Index size was around
40G on MyISAM before. 

Would you recommend compiling bacula (5.2.13) with batch insert
enabled? We're currently disabling batch insert on 5.2.12 compiled
from source, but it might make sense to re-activate it now wie run on
a "modern" db storage backend.

I hope you find this information useful in planning / sizing your own
myisam -> innodb migration if you haven't already done so. I'd also be
grateful if you'd point out any obvious flaws or improvments to the
settings noted above. I wonder if dumping the file table and then
re-importing it to an innodb replacement would have been quicker? Time
for more testing I guess ;) 


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