mySQL - Housekeeping basics

19.05.2021 — mysql — 1 min read

Being a Db2 DBA, i know the importance of regular housekeeping. In mySQL test tables, most of the time there won't be any maintenance scenarios due to less data. Sometimes when they do need maintaining, below are the things i do.

Run the below query to check the current sizes

1SELECT engine, table_name
2  ,ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2) "TOTAL_SIZE(MB)"
3  ,ROUND(( data_free ) / ( 1024 * 1024 ), 2) "DATA_FREE(MB)"
4  ,(data_free/(index_length+data_length)) AS FRAG_RATIO
5  ,ROUND(((Data_length - (table_rows * avg_row_length))/data_length) * 100, 2) "FRAG%"
6FROM information_schema.tables
7WHERE table_schema = DATABASE() AND data_free > 0
8ORDER BY FRAG_RATIO DESC LIMIT 10;
9
10+--------+-----------------+----------------+---------------+------------+-------+
11| ENGINE | TABLE_NAME      | TOTAL_SIZE(MB) | DATA_FREE(MB) | FRAG_RATIO | FRAG% |
12+--------+-----------------+----------------+---------------+------------+-------+
13| InnoDB | batch_status    |           0.36 |          5.00 |    13.9130 |  1.01 |
14| InnoDB | messages        |           4.20 |         12.00 |     2.8550 |  0.05 |
15| InnoDB | rating          |           1.47 |          4.00 |     2.7234 |  0.22 |
16| InnoDB | bti             |           1.52 |          4.00 |     2.6392 |  0.02 |
17| InnoDB | ntopnhigh       |           1.52 |          4.00 |     2.6392 |  0.19 |
18| InnoDB | ntopnlow        |           1.52 |          4.00 |     2.6392 |  0.16 |
19| InnoDB | dndata          |           1.52 |          4.00 |     2.6392 |  0.19 |
20| InnoDB | ntv             |           1.52 |          4.00 |     2.6392 |  0.14 |
21| InnoDB | bclose          |           1.52 |          4.00 |     2.6392 |  0.09 |
22| InnoDB | nclose          |           1.63 |          4.00 |     2.4615 |  0.15 |
23+--------+-----------------+----------------+---------------+------------+-------+
2410 rows in set (0.02 sec)

If FRAG% is > 2 then we can take below actions when its less than that you can consider the fragmentation is low.

OPTIMIZE TABLE

This reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.

1mysql> optimize table links;
2+---------------+----------+----------+-------------------------------------------------------------------+
3| Table         | Op       | Msg_type | Msg_text                                                          |
4+---------------+----------+----------+-------------------------------------------------------------------+
5| test.links    | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
6| test.links    | optimize | status   | OK                                                                |
7+---------------+----------+----------+-------------------------------------------------------------------+
82 rows in set (29.24 sec)

When looking at the docs there were other alternative options recommended like,

Use ALTER TABLE tbl_name FORCE to perform a “null” alter operation that rebuilds the table.
To speed up index scans, you can periodically perform a “null” ALTER TABLE operation, which causes MySQL to rebuild the table:
```
1ALTER TABLE tbl_name ENGINE=INNODB
```

Trying the first option, i didn't get much results. Second option worked for me. Below are the results

Test 1

1-- Before 
2+--------+---------------------------+-------------+--------------+-----------+
3| ENGINE | TABLE_NAME                | data_length | index_length | data_free |
4+--------+---------------------------+-------------+--------------+-----------+
5| InnoDB | metrics                   |          11 |           26 |        31 |
6+--------+---------------------------+-------------+--------------+-----------+
7
8alter table metrics engine=InnoDB;
9
10-- After 
11+--------+---------------------------+-------------+--------------+-----------+
12| ENGINE | TABLE_NAME                | data_length | index_length | data_free |
13+--------+---------------------------+-------------+--------------+-----------+
14| InnoDB | metrics                   |           8 |            6 |         2 |
15+--------+---------------------------+-------------+--------------+-----------+

Test 2

1-- Before 
2+-------------------+----------------+---------------+
3| TABLE_NAME        | TOTAL_SIZE(MB) | DATA_FREE(MB) |
4+-------------------+----------------+---------------+
5| links             |         156.77 |         17.00 |
6+-------------------+----------------+---------------+
710 rows in set (0.01 sec)
8
9mysql> alter table links engine=InnoDB;
10Query OK, 0 rows affected (18.20 sec)
11Records: 0  Duplicates: 0  Warnings: 0
12
13-- After 
14+-------------------+----------------+---------------+
15| TABLE_NAME        | TOTAL_SIZE(MB) | DATA_FREE(MB) |
16+-------------------+----------------+---------------+
17| links             |          84.20 |          4.00 |
18+-------------------+----------------+---------------+

ANALYZE TABLE

ANALYZE TABLE performs a key distribution analysis and stores the distribution for the named table or tables.

1mysql> analyze table links;
2+---------------+---------+----------+----------+
3| Table         | Op      | Msg_type | Msg_text |
4+---------------+---------+----------+----------+
5| test.links    | analyze | status   | OK       |
6+---------------+---------+----------+----------+
71 row in set (3.94 sec)

After the reorgs, you can run the statistics