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
[email protected] [email protected] [email protected]
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-users
