Navixy is a database-heavy application. The more devices you have - the faster the database will grow. Question of data archiving is a rather important one and it gets asked quite frequently. Here we will give some tips on the process.
From which tables should I archive the data?
Navixy has 2 databases - one for business data and one for tracking data.
From the business database the biggest tables are:
- sensor_data - sensor values for devices
- history - notifications generated by the platform
- counters_data - values for odometer/engine hours of devices
These tables are responsible for the majority of space occupied.
For tracking database - regardless of configuration - you can go though each table and archive the data past a certain point.
What do I use to archive data?
You can use any tool or utility you like or have experience with.
We, personally, use a pt-archiver tool from Percona to assist with that process.
Utility gives you one of 3 options:
- delete data
- export data to external file
- export data to a separate table
Process is configurable so we strongly recommend to consult with their official documentation before proceeding.
That way, you will be able to finetune the process to perfectly fit your business needs.
Freeing up disk space
If you want to archive data as a mean to reduce used disk space, there are a couple of things you need to check beforehand.
First, you need to check how your data is stored. MySQL has a innodb_file_per_table option.
If this option is disabled, all your data is stored in ibdata1 file. That file does not shrink, so data archival will not free up any space.
Turning the option on, archiving data and using OPTIMIZE TABLE statement will lead to occupying even more space
In this case your best bet is likely exporting, reinstalling and importing the data back.
However, starting from MySQL 5.7 is option is enabled by default.
In this case, you can proceed with archiving data.
Once that process is complete, you can run an OPTIMIZE TABLE statement.
it will copy data to anew table, drop the old one and rename the new one to its original name. Procedure can be time-consuming but will allow you to free up some disk space.