Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Optimize Table

mmuslehmmusleh EntrantInactive User Role Beginner
We have a table that contains 19 million records, 11 indexes + primary key, 16 columns

I'm trying to optimize it but I'm getting an error

I do
OPTIMIZE NO_WRITE_TO_BINLOG TABLE mytable;
OR
OPTIMIZE TABLE mytable;

it runs for 15-20 minutes then and I get
ERROR 2013 (HY000): Lost connection to MySQL server during query

when I check the optimization result, I see nothing has changed

they way I check the optimization result is by checking the cardinality of all indexes before and after and I expect them to change after the optimization is done.

any ideas why I get that error? how can I optimize the table without getting that error msg?
any help would be appreciated

Thanks

Comments

  • sterin71sterin71 Advisor Inactive User Role Beginner
    Have you checked the error-log?

    Do you have enough disk space on the data partition?
    An optimize table essentially creates a full ordered copy of the table during the optimization before throwing away the old unoptimized copy of the table.
    So if you run out of disk the optimization will not go through but MySQL will usually clean up the temporary tables.

    Either way if there isn't anything in the error-log you will have to monitor the server when you are running an optimize table to see what actually happens.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.