Archiving data
Navixy is a database-heavy application, and the more devices you have, the faster the database will grow. As a result, data archiving becomes an essential consideration for system administrators, and it is a question that gets asked quite frequently. In this article, we will provide some valuable tips on the data archiving process.
Data-intensive tables
It is essential to know which tables contain the most significant amount of data to manage disk space effectively. Navixy has two databases: for Business data and for IoT data.
In the Business database, the most substantial tables include:
sensor_data– stores sensor values for devices;history– stores notifications generated by the platform;counters_data– which stores values for odometer, engine hours and similar.
When it comes to the IoT database, the administrator can review each table and archive the data that exceeds a certain point, regardless of its configuration. The selection of this point will depend on the organization's specific needs and requirements for data retention.
Tools for archiving data
As a system administrator, you can use any tool or utility that you prefer or have experience with to archive data in Navixy. However, we recommend using the pt-archiver tool from Percona as it provides three convenient options:
deleting data
exporting data to an external file
exporting data to a separate table
The process is pretty configurable, so we recommend consulting with Percona's official documentation before proceeding. This will allow you to fine-tune the archiving process to perfectly fit your business needs, ensuring that it is optimized and effective.
Freeing up the disk space
After you have completed the data archiving process, it is crucial to run an OPTIMIZE TABLE statement to further manage disk space and optimize the database. This statement will copy the archived data to a new table, drop the old table, and rename the new one to its original name. Although this procedure can be time-consuming, it is an effective way to free up disk space, improve system performance, and ensure that your Navixy instance is running smoothly.
Before proceeding with the OPTIMIZE TABLE process, it is essential to ensure that the innodb_file_per_table option was enabled from the beginning. Starting from MySQL 5.7, this option is enabled by default, but for earlier versions, it might not be enabled. If this option is disabled, enabling it at this point may result in a larger file size for the ibdata1 file, further complicating disk space management.
If this is the case, the best option would be to export the data, reinstall Navixy, and import the data back into the database. This approach will allow you to enable the innodb_file_per_table option and create a more manageable database size, allowing for efficient data archiving and optimized performance. By following these steps, you can ensure that your Navixy instance is optimized for effective data management and improved system performance.