Outdated documentation

You are looking at the documentation for an older release. For the latest information, please see current release documentation.

Database Table Optimization¶

Note

This tutorial is only for on-Premise customers. The Customer Solutions team takes care of the database of managed customers.

Due to the nature of database tables they can be fragmented over the time. The fragmentation is caused by updating the values in the records. This is normal, but a fragmented table takes more storage and the queries can run slower.

This is a database operation topic and not related to OTRS strictly, but this tutorial can help to identify and solve the problem if the database is getting bigger and bigger.

To optimize the database tables:

Check the status of the tables using the SQL statement below.

SELECT
  TABLE_NAME,
  ROUND(DATA_LENGTH/1024/1024, 2) AS DATA_LENGTH,
  ROUND(INDEX_LENGTH/1024/1024, 2) AS INDEX_LENGTH,
  ROUND(DATA_FREE/1024/1024, 2) AS DATA_FREE,
  (data_free/(index_length+data_length)) AS FRAG_RATIO
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'otrs'
AND DATA_FREE > 0
ORDER BY frag_ratio DESC;

Review and analyze the output which should look like this.

+--------------------------------+-------------+--------------+-----------+------------+
| TABLE_NAME                     | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | FRAG_RATIO |
+--------------------------------+-------------+--------------+-----------+------------+
| communication_log_object_entry |        0.02 |         0.03 |     10.00 |   213.3333 |
| user_config                    |        1.19 |         0.05 |      4.00 |     3.2405 |
| web_upload_cache               |        7.31 |         0.00 |     22.00 |     3.0085 |
| article_data_mime              |        1.52 |         0.06 |      4.00 |     2.5347 |
| sysconfig_deployment           |       10.02 |         0.02 |     16.00 |     1.5950 |
| xml_storage                    |        1.50 |         1.92 |      4.00 |     1.1689 |
| mail_queue                     |        5.52 |         0.05 |      4.00 |     0.7191 |
| virtual_fs_db                  |        1.52 |         0.02 |      1.00 |     0.6531 |
| sysconfig_default              |        6.41 |         0.31 |      4.00 |     0.5953 |
| sysconfig_default_version      |       10.52 |         0.45 |      5.00 |     0.4558 |
| package_repository             |       33.52 |         0.03 |     11.00 |     0.3279 |
+--------------------------------+-------------+--------------+-----------+------------+

The output shows that the fragmentation ratio of the communication_log_object_entry table is 213 and the table contains 10 MB empty data.

If the fragmentation ratio is higher than 50% (0.5 in the FRAG_RATIO column) you should consider to optimize the table using the OPTIMIZE SQL statement.
```
OPTIMIZE TABLE otrs.communication_log_object_entry;
```

Verify if the table is not fragmented anymore. Use the same SQL statement as used in step 1.

+---------------------------+-------------+--------------+-----------+------------+
| TABLE_NAME                | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | FRAG_RATIO |
+---------------------------+-------------+--------------+-----------+------------+
| user_config               |        1.19 |         0.05 |      4.00 |     3.2405 |
| web_upload_cache          |        7.31 |         0.00 |     22.00 |     3.0085 |
| article_data_mime         |        1.52 |         0.06 |      4.00 |     2.5347 |
| sysconfig_deployment      |       10.02 |         0.02 |     16.00 |     1.5950 |
| xml_storage               |        1.50 |         1.92 |      4.00 |     1.1689 |
| mail_queue                |        5.52 |         0.05 |      4.00 |     0.7191 |
| virtual_fs_db             |        1.52 |         0.02 |      1.00 |     0.6531 |
| sysconfig_default         |        6.41 |         0.31 |      4.00 |     0.5953 |
| sysconfig_default_version |       10.52 |         0.45 |      5.00 |     0.4558 |
| package_repository        |       33.52 |         0.03 |     11.00 |     0.3279 |
+---------------------------+-------------+--------------+-----------+------------+

Repeat step 3 for the other tables if needed. You can optimize more tables at the same time.

OPTIMIZE TABLE otrs.communication_log_object_entry, otrs.web_upload_cache, otrs.package_repository;