Optimize Table

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.