Should I defragment InnoDB table with fixed-length fields?

0 votes

2 answers

488 views

                          I am running MariaDB 10.2.13 on Linux (Debian).

I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain constant.

I should expect on this table:

 - many INSERTs
 - many UPDATEs
 - some DELETE (rows > 1 hour) from time to time

#Example:

Table is defined with **fixed-length fields only** and some indexes.

    CREATE TABLE tbl_log (
      ip int(4) unsigned NOT NULL,
      date datetime NOT NULL,
      external_id smallint(6) unsigned NOT NULL,
      counter smallint(6) unsigned NOT NULL DEFAULT 0,
      PRIMARY KEY (ip,external_id),
      KEY external_id (external_id),
      KEY counter (counter),
      KEY date_idx (date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Inserts (and updates, through on duplicate key) may look like this (*ip* and *external_id* will vary):

    INSERT INTO tbl_log
      SET ip = INET_ATON('192.168.1.1'),
          date = now(),
          external_id = 123,
          counter = 0
      ON DUPLICATE KEY UPDATE counter=counter+1;

Finally, deleting old rows will be done with a query:

    DELETE FROM tbl_log WHERE date < DATE_SUB(NOW(), INTERVAL 1 HOUR);

#Question:

**Will such a table fragment over time?**

If so, I think I should defragment it. If necessary, I planned to run OPTIMIZE TABLE tbl_log; (with option innodb-defragment=1) right after delete...
                        

Asked by Nicolas Payart (2508 rep)

Mar 20, 2018, 02:35 PM
Last activity: Mar 14, 2022, 01:01 PM

Should I defragment InnoDB table with fixed-length fields?

Related Questions