slow Traccar service startup

[MMartinezV]

Hi,

As long as the time passes, our traccar server needs more and more time to restart the service (now we've got 66 million rows on the positions table and growing every day).

It's very unusual for us to restart it, but sometimes is necessary.

Today I've done a little search on this question and I've found two issues at Traccar (not traccar-web) forums:

• https://github.com/tananaev/traccar/issues/2374
• https://www.traccar.org/forums/topic/traccar-start-very-slow/

Basically, they say that the nested sql query used to get the latest position for each device at startup could be easily optimized doing a simple inner join. But Tananaev concludes that with mysql 5.7 the query plan is the same so he simply recommends upgrading to mysql 5.7.

My system is built on an Ubuntu 14.06 LTS and it uses mysql 5.5, the Traccar service now is very stable and in production and I don't plan to do upgrades untill the end of October if it is not is very, very necesary. For this reason, I've decided to test the recommended query and adapt it to the traccar-web needs:

After writing it (using aliases for each field) and testing on the mysql client, I've changed the "database.selectLatestPositions" entry on the traccar.xml conf file by this one:

<entry key='database.selectLatestPositions'> SELECT p.id as id, p.protocol as protocol, p.device_id AS deviceId, p.serverTime as serverTime, p.time AS deviceTime, p.time AS fixTime, p.valid as valid, p.latitude as latitude, p.longitude as longitude, p.altitude as altitude, p.speed as speed, p.course as course, p.address as address, p.other AS attributes FROM positions p INNER JOIN devices d ON d.latestPosition_id = p.id </entry>

The result is incredible! Now the traccar service just needs a minute to start instead of 40 minutes (in fact, the query needs less than a second).

For me it's clear that it's better to change that single query, that just affects to the initial load of device's positions, rather than forcing to use a specific version of mysql.

It would be great if you can change the recommended query on your installation notes (http://traccar.litvak.su/installation.html) to avoid this problem on big databases.

Obviously, for next year I'll upgrade the OS and the mysql version, but I believe that its better to use the inner join version as it works better on more installations.

Regards,

Manuel

[vitalidze]

Thank you very much for sharing this. I appreciate that. I will try to find some time for updating installation instructions.