GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Optimize Table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimize Table

    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.

    Comment

    Working...
    X