Mysql – Extend thesql 5.5 tablespace to another drive on windows

To begin with, storing ibdata on a separate volume from the datadir is a bad idea because of

• MetaData Issues : I wrote about this as well
  • Why does InnoDB store all databases in one file? (See WARNING)
  • How to Recover an InnoDB table whose files were moved around
  • Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
• Performance Issues : I wrote about that back in August 2011
  • MySQL InnoDB data file directly on a disk slice (fixed-size)?

Getting back to your problem, the reason LVM for ibdata1 is not good has to do with the metadata in ibdata1 at any given point-in-time. If you were to LVM Snapshot ibdata1, then create new tables, and drop some tables, somne of the corresponding .frm files within datadir would not match. The time needed to coalesce that large an ibdata would be astronomical (unless 21 hours is acceptable to you). I tried using rsync as opposed to LVM and I fared no better.

The safest and most sensible way to clear the space with innodb_file_per_table disabled is to do the following on the Slave

• Step 01) mysqldump all databases
• Step 02) shutdown mysql
• Step 03) delete ibdata1
• Step 04) startup mysql
• Step 05) reload the mysqldump

Without this method, ibdata1 can never be shrunk otherwise. Even in my post in StackOverflow on InnoDB Cleanup (using innodb_file_per_table), you have to do those same 5 steps.

You need to tune your InnoDB settings in the following areas:

• Make InnoDB access all your cores
• Increase innodb_buffer_pool_size to 12G
• Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
• Increase Log File Size (innodb_log_file_size) to 2047M
• support separate tablespace files for individual InnoDB tables (enaled innodb_file_per_table)
• support either high performance or high durability (ACID Compliance)
  • High Performance : innodb_flush_log_at_trx_commit set to 0 or 2
  • High Durability : innodb_flush_log_at_trx_commit set to 1 (Default)
  • Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
  • Your current setting for innodb_flush_log_at_trx_commit is good
  • Your current setting for innodb_flush_method is good
• Increase innodb_read_io_threads to 64
• Increase innodb_write_io_threads to 64
• Increase innodb_io_capactity to 10000

Here are my past posts on tuning the InnoDB storage engine

• Is the CPU performance relevant for a database server? (Apr 26, 2012)
• Why does InnoDB store all databases in one file? (Mar 25, 2012)
• Multi cores and MySQL Performance (Sep 20, 2011)
• Possible to make MySQL use more than one core? (Sep 12, 2011)
• About single threaded versus multithreaded databases performance (May 26, 2011)
• How to safely change MySQL innodb variable 'innodb_log_file_size'? (Feb 16, 2011)
• How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)
• Howto: Clean a mysql InnoDB storage engine? (October 29, 2010)