AppDynamics for Databases
2.9.x Documentation
The MySQL Optimize Table command will effectively defragment a mysql table and is very useful for tables which are frequently updated and/or deleted.
There is a table called articles which has many thousands of rows which are often inserted, updated and deleted. The table contains variable length column data types:
Code Block |
---|
mysql> desc articles;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| content | text | NO | | NULL | |
| author_id | int(11) | YES | | NULL | |
| article_title | varchar(120) | YES | | NULL | |
| article_hash | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
|
The size of the table on disk is about 190MB. Query the table on a column that is indexed to see the average query response time:
Code Block |
---|
mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
| 15830 |
+----------+
1 row in set (0.63 sec)
|
Optimize the table with the following command:
Code Block |
---|
mysql> optimize table articles;
+-----------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| books.articles | optimize | status | OK |
+-----------------------+----------+----------+----------+
1 row in set (6.27 sec)
|
...
Note: The MySqQL query cache was turned off to demonstrate.
Code Block |
---|
mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
| 15830 |
+----------+
1 row in set (0.39 sec)
|