Traccar 3.3 and mySQL, long-term performance implications

I have Traccar 3.3 set up with mySQL. It's only for personal use, a few devices at most, but the hardware is also pretty lightweight (a cheap VPS). Are there any long-term maintenance tasks that I need to perform to keep everything running smoothly? Will accumulation of database entries conceivably slow down the interface?

Also, I noticed earlier discussions about a database.checkTable entry in traccar.xml. My config doesn't have this entry and it's not in the mySQL setup instructions. What does it do and is it necessary to include?

Thanks!

I would recommend to clear old data to make database work faster. For example, you can clean data older than 3 months or some other period.

Version 3.3 doesn't use checkTable, so you can ignore those comments. They apply to older versions.

Dear Anton,

I would like to add in a related question, usually the position row referring to the latest position of a device needs to be skipped during cleaning/backup, so as to avoid deleting its last known location in-case the said position was last active 3 month back.

How can that be handled best?

What would be the mySQL command to do this?

Would this work? It's from another forum post.

DELETE * from position WHERE deviceTime < (NOW() – INTERVAL 90 day);

Here is the script for MySQL that I use:

SET foreign_key_checks = 0;
DELETE FROM positions WHERE serverTime < DATE_ADD(NOW(), INTERVAL -3 MONTH);
UPDATE devices SET positionId = NULL WHERE positionId NOT IN (SELECT id FROM positions);
SET foreign_key_checks = 1;

Thanks, I'll use that.

Dear Anton,

Digging up an old post, relevant to my query.

1. I am using mysql for database with around 20gb data in position table.
2. I do not want to delete data, but move them to another db on the same server. without affecting the device (latest position ID).
3. That is move all data, older than lets say 90 days, however if last position for a particular device is older than 90 days, leave that entry without deleting. (So that the last known position is still the same) even if data is moved.

I'm sure it's possible to write a query that would do it, but I think it will be pretty slow.

I am trying to think of ways to handle this scenario, if you know of any pleas do advice.

My primary goal is to backup old data, and retain only 3 months data at any given point of time on the live server. Without breaking the foreign key link for last position.

Something like this (not real SQL):

DELETE FROM positions WHERE [>3 months] AND id NOT IN (SELECT positionid FROM devices);