I already answered this in StackOverflow : https://stackoverflow.com/a/11636341/491757
If you want to defrag all your MyISAM tables, here is a shell script to do so...
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='MyISAM' AND table_schema NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GlobalMyISAMOptmizeTable.sql
less GlobalMyISAMOptmizeTable.sql
Once you trust the script visually, just run it
mysql ${MYSQL_CONN} < GlobalMyISAMOptmizeTable.sql
Give it a Try !!!
Symlinking InnoDB is definitely not a good idea for regular maintenance of InnoDB.
I wrote posts on why not to do this:
However, if this strictly for the purpose of shrinking a large table, I guess you can symlink the whole data directory where the temp table is to go, but you will have to personally script the shrinkage.
For example
- Suppose the table to shrink is
mydata.mytable
datsdir
is /var/lib/mysql
Your procedure would be
STEP 01: You should create a database called mydatatemp
. Do not put any data in it just yet.
STEP 02: Move the /var/lib/mysql/mydatatemp
over to another disk.
STEP 03: Create a Symlink /var/lib/mysql/mydatatemp
over to the folder on the other disk
STEP 04: chown -R mysql:mysql
against the external folder and against the symlink.
STEP 05: Now perform the data shrinkage as follows:
CREATE TABLE mydatatemp.mytable LIKE mydata.mytable;
INSERT INTO mydatatemp.mytable SELECT * FROM mydata.mytable;
DROP TABLE mydata.mytable;
ALTER TABLE mydatatemp.mytable RENAME mydata.mytable;
To recap, do the following (assuming /tmpdata
is the external disk)
STEPS 01-04
chown mysql:mysql /tmpdata
ln -s /tmpdata /var/lib/mysql/mydatatemp
chown -R mysql:mysql /var/lib/mysql/mydatatemp
then run STEP 05
CAVEAT
This would be rather high risk, but it should work. Please test this with an empty table first like this:
USE mydatatemp
CREATE TABLE mrnorm (id int not null, name varchar(20) primary key (id));
INSERT INTO mrnorm (name) VALUES ('one'),('two'),('three');
SELECT * FROM mrnorm;
If these SQL command worked, go to OS and do this:
cd /tmpdata
ls -l
You should see two files:
Also, test if mrnorm can be moved
CREATE DATABASE helloworld;
ALTER TABLE mydatatemp.mrnorm RENAME helloworld.mrnorm;
Go to the OS and run
cd /tmpdata
ls -l
The table should be gone. Now, do this:
cd /var/lib/mysql/helloworld
ls -l
The table should be there
END OF TEST
Keep in mind that the rename of the table would essentially be controlled copy of the table from one disk to another disk with a proper adjusting of the data dictionary section of the system tablespace in ibdata1. This could take some time.
Best Answer
If you are worried about table locks caused by DDL operations, like
ALTER TABLE... ENGINE=InnoDB
(what optimize does for InnodB) you must know that in 5.6, that process can be done fully online, and alternatively, and for lower versions, you can use an online alter table tool like pt-online-schema-change. With proper care, the process can be done fully online and with minimal load increase (although you will need double the table size). Make sure also you are using innodb_file_per_table.Assuming the table has been optimized and all rows purged, yes, you can obtain an improvement due to the several reasons:
innodb_buffer_pool_read_requests
andinnodb_buffer_pool_reads
So the actual improvement may depend a lot on the actual queries executed, usage or not of hashed searches, memory state, penalty of random IO reads, etc. But in general, working with smaller data sets is easier.
Beware also of the purge process, which can bite your performance if not done well.
Some of this advantages could be lower if there is a lot of fragmentation, but that depends a lot on the actual state of the table. You can see some thoughts about optimize on InnoDB here (more on comments):