myisam转innodb步骤

Convert your MySQL database from MyISAM to InnoDB, and get ready for Drupal 7 at the same time

http://yun.baidu.com/disk/beinvited?uk=1292220315 百度云盘

If you haven't already heard, Drupal 7 will default to using the InnoDB storage engine instead of MyISAM for MySQL (though a MyISAM database will continue to work just fine in Drupal 7). This is fairly substantial change within Drupal core, and as the thread in the issue queue I linked to shows, there were a lot of questions and apprehension about it. However...

...we are going to just skip over a lot of that apprehension and get down to point of this article - there's no good reason not to hop right into using InnoDB today on your Drupal 5 or Drupal 6 site. The rewards are; a possibly significant improvement in performance, a definite improvement in scalability (most highly trafficked Drupal sites have been using InnoDB for some time now because of this), and you'll start getting used to working with what will be more and more common in your Drupal-life, InnoDB.

My experience
I came to the conclusion about how great InnoDB is after researching the experiences of others, and after converting a large Pressflow-driven Drupal 5 site from InnoDB vs MyISAM. This change resulted in a 14% increased throughput during load tests performed in JMeter. That's a very substantial increase, and while everyone's mileage will vary based on their own site, server, and any number of variables it's clear enough to me that there's nothing to be afraid of as far as InnoDB goes (quite the contrary).

Converting your database to InnoDB
Before you go any further backup your database before doing any steps below. If you 'splode your database for any reason, you'll need it.

Here are the steps:

1. Shutdown MySQL

2. Move/copy/change the name of ib_logfile0 and ib_logfile1 files. (find where MySQL exists on your system - locations can vary greatly). MySQL will recreate these files when you start it up again. Not anytime you change the innodb_log_file_size parameter you will need to shutdown MySQL, move these files, and start up MySQL again.

3. Tune it up a bit
Based on a lot of searching around and benchmarking with JMeter I arrived at the setting below for running on my Macbook Pro. See the links at the end of this post for articles which can help you determine what to adjust these numbers to for other machines (ones with more RAM/CPU, for instance. The production server for this particular site ended up with 5000M setting for innodb_buffer_pool_size. So settings will, and should, vary greatly just depending).

With some more time spent fine tuning and benchmarking, and taking into account the specifics of your application, these setting could be improved upon I'm sure, but they're a decent starting point at least. These parameters go inside your my.cnf file. Anytime you edit your my.cnf file remember to restart my.sql in order to have the new parameters take affect. Finally, in addition to adding the settings below, I also lowered values for things that are more MyISAM specific like key_buffer_size.

Here are the relevant InnoDB related paremeters, which you should merge into your existing my.cnf. IMPORTANT - be sure to comment outskip-innodb parementer if it exists.

# skip-innodb needs to commented out if it already exists in your my.cnf - shown here as a reminder
#skip-innodb

default-storage-engine = innodb
innodb_buffer_pool_size = 200M
innodb_log_file_size = 100M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M

# num cpu's/cores *2 is a good base line for innodb_thread_concurrency
innodb_thread_concurrency = 4

#Tried the following parameters to no good effect, actually (very) small decrease in performance, thus they are commented out but document here, just because.
#innodb_data_home_dir = /path/to/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /path/to/mysql
#transaction-isolation=READ-COMMITTED

4. Time for the actual conversion of the tables
Borrowing heavily from some commandline magic from this post we'll get the database tables converted. It's worth noting that I've changed the sequence of the other steps here around because the order of them did not work for me. I've also disregarded the part about leaving certain tables MyIASM (*see notes at the end of this post for more). My database did not seem to want to be converted (via the queries found inside alter_table.sql file you'll generate) until after the my.cnf file was all setup and the steps with ib_logfile0 and ib_logfile1 were completed and MySQL had been stop and restarted.

To generate the queries you need to run the following commands:

mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql

To run these queries against your database run the following command, or else take the contents of the alter_table.sql file you generate with the instructions and just many paste it in as a query in favorite MySQL interface (Sequel Pro for me, perhaps PHPMyadmin for others)

mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

After you've ran the queries check your database to make sure that it shows InnoDB as the storage engine and not MyISAM.

5. Restart MySQL and take your site for a spin.

6. Done

Notes
There are discussions in blogs and Drupal.org about 'which tables are ok to convert, and which tables are not ok to convert' to InnoDB. The conclusion of the core issue thread was to default all tables to InnoDB, and thatthere are not downsides to table-foo being InnoDB vs MyISAM. Additionally, I can confirm that the Drupal.org site uses InnoDB for *all* of it's tables. If anyone has specific benchmarks they can share which demonstrate how running specific tables as MyISAM instead of InnoDB makes a measurable positive impact on performance please share.

Other resources

• Innodb Performance Optimization Basics
• InnoDB Performance Tuning
• MySQL > YourSQL - basic settings
• MySQL Engines: MyISAM vs. InnoDB

• Caleb G's blog
• Add new comment

Comments

I think its is a bad idea

Permalink Submitted byRichard Church on Mon, 01/18/2010 - 22:38

I think its is a bad idea.

One of the reasons for MySQL's popularity is its one file per table capability. I think the bar is being raised for hobbyists and other weekend bloggers who prefer to manage stuff themselves.

Do all the small guys want to be locked into big bad Oracle in future?

• reply

...

Permalink Submitted byCaleb G on Mon, 01/18/2010 - 23:32

Hi Richard. This change doesn't stop people from being able to run MySQL or the MyISAM storage engine for MySQL -- it's only changing what Drupal defaults to.

• reply

Nice Idea

Permalink Submitted byKarsten Frohwein on Tue, 01/19/2010 - 07:30

I like the idea of a simple script to convert all tables in one pass!

@Richard
innodb_file_per_table = 1 should solve your problem.
http://dev.mysql.com/doc/refma...

Or maybe use maria db than.

• reply

Note that you can't simply

Permalink Submitted bydalin on Mon, 01/18/2010 - 18:26

Note that you can't simply change innodb_log_file_size if you don't know what you are doing or MySQL will not start. You need to:

1. change the innodb_log_file_size in the my.cnf

2. stop the server

3. rename ib_logfile0 to ib_logfile0_old

4. rename ib_logfile1 to ib_logfile1_old

5. restart server

So it's only recommended when tuning high performance sites. Furthermore I have issue with your recommendation on something as high as 100MB:

http://www.mysqlperformanceblo...

Also I'm confused by the fact that you list several variables multiple times.

Also to clarify,

There are not downsides to table-foo being InnoDB vs MyISAM as long as your server is tuned and has enough RAM to keep your tables in memory

A big site running on either an untuned server, or a server where it swaps tables to disk will likely perform better on MyISAM than InnoDB. But if that's the case then you've got bigger issues anyway.

It should be noted that for any of these settings that have a size this is just a ballpark value and what is optimum for your site is likely something very different. Randomly increasing values will probably degrade performance.

If you are hoping to get the most out of your database performance I'd suggest contacting a Drupal performance specialist.

• reply

...

Permalink Submitted byCaleb G on Fri, 01/22/2010 - 21:28

Hi Dalin. Thanks for noting the part about some duplicate values, I have cleaned those up. Fyi, I had mentioned the part about the ib_logfile0 to ib_logfile1 process in step #2. Also, I had already noted that these are indeed ballpark figures and that for optimum performance one will need to tune things to their own application and equipment.

Regarding the innodb_log_file_size - I ended up with this value after benchmarking smaller values. I have definitely seen smaller values for it being recommended at different places though. Honestly, it wasn't much of critical value either way, but I did get a little more out of things configure this way than with the lower values.

Also, you said:

"There are not downsides to table-foo being InnoDB vs MyISAM as long as your server is tuned and has enough RAM to keep your tables in memory"

That must be only in the context of very specific tables then, because I got a 14% improvement (benchmarked by JMeter) without putting all the tables in memory. Like I said in the article, if anyone can share data on specific tables which show benchmarkable performance gains by leaving them MyISAM, then please do, but I and others have demonstrated that InnoDB does not need to be married to any fanciness like memcache to be fast.

And I guess the last part about contacting a 'Drupal performance specialist' was just supposed to be a slight, or else drumming up some business for yourself. ;-)

• reply

Tables Names With Spaces

Permalink Submitted byNate on Fri, 02/17/2012 - 03:00

You may wanted to add `'s around the table names in the scrip that creates alter_table.sql if your table names have spaces in them. It does not affect table names with no spaces:

echo "ALTER TABLE \`{}\` ENGINE=INNODB;"

**Note they are escaped with \'s