key_buffer_size ? RAM size?
On huge tables, indexes, especially if they are not "appended to", cause disk hits -- whether MyISAM or InnoDB.
var0, being an AUTO_INCREMENT, will be "appended to" the end. Essentially no disk hits for it. I can't tell about the other 4 indexes. Let's say they are all very random. This implies that each row inserted will need 4 disk hits. On normal drives that means about 25 rows inserted per second.
Solutions...
RAID striping (0,5,6,10). That will give you a factor of improvement.
SSDs. $$$
PARTITIONing. This may help. It may also help with some SELECTs. To discuss further, please reveal some of the semantics of the indexed fields. (dates? md5s? names?) Also provide some of the SELECTs, so we can optimize them at the same time. The "partition key" must be part of any UNIQUE key. This is not a problem with the AUTO_INCREMENT, but it may be with the other UNIQUE key.
Nothing (yet) says whether the Engine will make any difference.
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 have deleted most of the rows from a table,
OPTIMIZE
table needs only enough room to build a new, smaller, copy. After the copy is finished, it will rename, then drop the old table.If the table had its own tablespace (built with
innodb_file_per_table=ON
), then the freed space will be returned to the OS. But... The 17GB free in the.ibd
cannot be used for the table copy. The copy will be in a separate file. So it could fail if the resulting table is more than 8GB (data+index).If
=OFF
, theOPTIMIZE
will try to use free space inibdata1
before increasing the size of that file.There is 17GB free somewhere; I can't tell where.
Also, check
TABLE STATUS
for other tables. TheData_free
for all tables in ibdata1 will be the same value. What is that value?There are tricks to work around the cramped space, but I need answers to the questions posed.