Non-blocking optimize table

Question

I would like to use a non-blocking optimize table (I've tried doing the normal OPTIMIZE and I notice about ~2mins of downtime. Here is the code I currently have for this:

@staticmethod
def optimize_table_nonblocking(tablename='eidrdedupe_test'):
    conn, cursor = get_new_db_conn_and_cursor()

    # populate a (potentially previously-existing) table with the data
    cursor.execute('DROP TABLE IF EXISTS %s_tmp' % tablename)
    cursor.execute("CREATE TABLE %s_tmp SELECT * FROM %s" % (tablename, tablename))

    # optimize that table
    cursor.execute("OPTIMIZE TABLE %s_tmp" % tablename)
    conn.commit()

    # swap the tables // keep the old table so we can examine it if we want
    cursor.execute('RENAME TABLE %s TO %s_tmp1' % (tablename, tablename))       # rename current table to _tmp1
    cursor.execute('RENAME TABLE %s_tmp TO %s' % (tablename, tablename))        # rename tmp table to main table
    cursor.execute('RENAME TABLE %s_tmp1 TO %s_tmp' % (tablename, tablename))   # now previous table is _tmp
    conn.commit()

    cursor.close(); conn.close()

Does this seem like a good approach, or are there things I'm missing / can improve?

[Question: How do I specify the code language in the markup?]

Answer 1

That has flaws.

• It is doing essentially what OPTIMIZE does -- copy the table over and rebuild the indexes.
• You are optimizing twice -- once with the CREATE .. SELECT, then again with the OPTIMIZE.
• Any writes during the optimize will be lost.
• Do all the RENAMEs in a single RENAME statement to avoid the brief time when no table exists: RENAME TABLE real TO old, new TO real;

All of those say that your optimize script cannot be as good as the builtin one.

pt-online-schema-change, but doing no actual schema changes, is a free tool that does better.

Anyway, why are you using OPTIMIZE? It is almost never of enough use to warrant doing it. Is it just to rebuild an InnoDB FULLTEXT index? Keep in mind that OPTIMIZE copies the table over and rebuilds all the indexes.

MySQL 8.0, I think, has an 'in-place' optimize. Upgrade to get this.

Meanwhile, consider stopping reads and writes, DROP INDEX and ADD INDEX for the one FULLTEXT index.