Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to accommodate the 5G table. However, I've seen zero performance gain. I've changed the temp directory to tmpfs, but again, zero gain. In fact, regardless of the settings I change, the tmp table always seems to be written to temp files, on disk, within the database's directory.

I assume data is written to disk in a table rotation manner for safety, but I'm wondering if there is anyway to direct the optimize process to create the temp table in memory instead?

Thanks!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Please keep in mind OPTIMIZE TABLE is DDL not DML.

In terms of mechanism, this is what OPTIMIZE TABLE mydb.mytable; does under the hood

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

or

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new DISABLE KEYS;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable_new ENABLE KEYS;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Since it's DDL, mysqld has no plausible reason to copy the table to memory.

BTW for InnoDB, you could do it manually if you want to skip ANALYZE TABLE mydb.mytable;

ALTER TABLE mydb.mytable ENGINE=InnoDB;

Even if you do ANALYZE TABLE mydb.mytable;, InnoDB will do deep dives into the index anyway to do cardinality approximations (See my post From where does the MySQL Query Optimizer read index statistics?).

share|improve this answer
    
I thought running optimize on innodb tables was fine as it does the alter and analyze behind the scenes? - Hence the message - doesn't support optimize, doing stuff behind the scenes :) Is that not the case? –  user2732180 Apr 19 at 3:50
    
Yes, you are right. I updated my answer as to why you may want to skip ANALYZE TABLE on InnoDB sometimes. –  RolandoMySQLDBA Apr 19 at 4:03
    
Perfect! Thanks –  user2732180 Apr 19 at 6:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.