On 02/20/13 04:17, Uwe Schuerkamp wrote:
> On Wed, Feb 20, 2013 at 12:17:30AM -0500, Michael Stauffer _g wrote:
>>
>> How do I setup the DB using InnoDB? I'm not seeing that in the docs. (I'm
>> sticking with bacula 3.0 for now, until I can get the catalog back and do
>> another full backup). 
>>
> 
> Hello Michael, 
> 
> I can only answer this question: Create a dump of your old mysql
> database using the -d option, this will only dump the table
> definitions, then search replace 
> 
> Engine=MyISAM 
> 
> with 
> 
> Engine=InnoDB 
> 
> or do it in one fell swoop: 
> 
> mysqldump -u root -pXXXX -d bacula | sed
> 's/Engine=MyISAM/Engine=InnoDB/g' > dump.sql 

This is really kinda the hard way to do it.  Here's a much better way:

$  for tablename in $(mysql -BNe 'SELECT table_name FROM
information_schema.tables WHERE table_schema = "bacula"' ; do mysql -BNe
"alter table bacula.${table} engine=InnoDB" ; done


> Also make sure you have the innodb_tables_as_files option set,
> otherwise you db will be one huge unmanageable blob that can't be
> reduced in size after a while by dumping / importing again.

Whoa there, Nellie.  Make sure you understand the implications of doing
that before you do it.

Pros for innodb_file_per_table:
— You can reclaim the space used by an InnoDB table if you drop the
table (but only if you drop it altogether).

Cons for innodb_file_per_table:
- Increases total MySQL disk consumption
- Increases MySQL memory consumption
- Increases the number of files MySQL has to hold open
- Impairs MySQL's ability to reuse free space in the InnoDB tablespace
- Contrary to popular belief, NOT separately portable

About 95% of people who turn on innodb_file_per_table turn it on for the
wrong reasons based on an incomplete understanding of its effects and an
incorrect understanding of what they'll gain from it.


>> What does it mean to retune MySQL for InnoDB?

tl;dr version:

Transfer as much memory as you can from MyISAM-specific buffers, such as
the key buffer and the read_random_buffer, to the InnoDB buffer pool.
Ideally, for OPTIMAL performance, you want the InnoDB buffer pool to be
20%-30% larger than total InnoDB data, but if your DB is large that's
obviously not possible.  On MySQL 5.5 or later you probably want to set
innodb_buffer_pool_instances (which should be called
innodb_buffer_pool_partitions) to partition the buffer pool into 3GB-4GB
chunks for best InnoDB performance.  Also look into the
innodb_io_capacity settings; the default is 100 IOPS, but many modern
disk subsystems are capable of considerably more than that.



-- 
  Phil Stracchino, CDK#2     DoD#299792458     ICBM: 43.5607, -71.355
  alaric AT caerllewys DOT net   alaric AT metrocast DOT net   phil AT 
co.ordinate DOT org
  Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
                 It's not the years, it's the mileage.

------------------------------------------------------------------------------
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