Database optimization - tips?
First rule of thumb in DB tuning is to tune the application and data access strategies.
One key to this is to have indexed on large tables that can be utilised by the queries being made to the DB.
I haven't looked in the relevant java code to see how Traccar queries locations, but it's very likely it's doing a full table scan on tc_positions.
There are no indexes on tc_positions on the default postgresql installation (other than the primary key), which I assume is the same as in MySQL. You could try putting an index on latitude and / or longitude columns to see if that helps.
In my custom reporting DB we use PostGIS indexed geography datatypes to store locations and shapes such as geofences, and there the response is very fast.
I've turned on slow query detections, this is what is used for generating reports (at least for Routes):
SET timestamp=1566278342;
SELECT * FROM tc_positions WHERE deviceId = 263 AND fixTime BETWEEN '2019-08-19 22:00:00.0' AND '2019-08-20 22:00:00.0' ORDER BY fixTime;In basic MySQL installation, only deviceid is indexed - do you think anything else should be indexed? fixtime seems to be an obvious choice.
Adding an index to fixtime, using this query:
ALTER TABLE `traccar`.`tc_positions` DROP INDEX `fk_positions_deviceid`, ADD INDEX `fk_positions_deviceid` (`deviceid`, `fixtime`) USING BTREE;results in the following error:#1067 - Invalid default value for 'devicetime'
Devicetime default value is 0000-00-00 00:00:00.
Creating a completely new index also results in this error.
Ok, nevermind, I googled the answer. It's actually pretty obvious - answer is here: https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at
Strange, that is different to my Postgresql installation, I had no index at all... Useful info though, I had my custom index set to deviceid / devicetime, which is what our other (non_traccar) system uses. Made the change :)
Indexing fixtime improved report generating times, but it's still not where I would like it to be. I think this tip should be added to "Optimization" in the documentation though.
Hola podrias explicar el procedimiento para poder implementarlo , me gustaria tambien probarlo a ver cuanto mejora la generacion de informes
Thanks. I just indexed fixtime and it increased a lot the performance.
I have over 10 milion entries in tc_positions. Report generation takes a lot of time (over 30 seconds) and I think it's related to how many entries are in said table.
I realize that stock MySQL settings are unlikely to be good enough for Traccar, but I'm a total amateur when it comes to databases. Does anyone know what I could do to improve report generation times? I didn't want to mess with indexes, I don't want to strain my machine's RAM too much. I did read "Optimization" section in documentation, yet it doesn't give any specifics. Anyone got similar experience with such things?