Noticed one problem with notifications when position table get big

Ahmad Bassel3 years ago

I am testing the latest traccar server for 2 months now and I faced one problem with the notifications, suddenly it stops working.

I investigate more one our database and find the problem was with the table tc_positions because it got filled with millions of rows and the query below takes a long time to execute which delays the notifications and events:

[SELECT tc_positions.* FROM tc_positions INNER JOIN tc_devices ON tc_positions.id = tc_devices.positionId;]

The solution I made is to rename the table tc_positions to tc_positions_all and create a duplicate table tc_positions which solved the problem and changed the default.xml to the code below:

<entry key='database.selectPosition'>
SELECT * FROM tc_positions_all WHERE id = :id
</entry>

<entry key='database.selectPositions'>
SELECT * FROM tc_positions_all WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to ORDER BY fixTime
</entry>

<entry key='database.insertPosition'>
INSERT INTO tc_positions (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes, accuracy, network) VALUES (:deviceId, :protocol, :serverTime, :deviceTime, :fixTime, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes, :accuracy, :network);

INSERT INTO tc_positions_all (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes, accuracy, network) VALUES (:deviceId, :protocol, :serverTime, :deviceTime, :fixTime, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes, :accuracy, :network);
</entry>

My question is there any better solution for this problem? because I am doing a manual delete for all rows (which is not on tc_devices) inside the table tc_positions every hour to reduce its size and so far it is working without any issue.

I am sharing my experience with you because we are testing the server in a real-world with a big number of devices and a big number of new entries to the positions every day hope this will enhance the performance and eliminate all the problems with this product.

Ahmad Bassel3 years ago

I want to add one more point to my previous post, using the new config below have one catch, both tables should have same Auto_Increament value for id or this will affect all database tables like tc_devices and tc_events.

<entry key='database.insertPosition'>
INSERT INTO tc_positions (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes, accuracy, network) VALUES (:deviceId, :protocol, :serverTime, :deviceTime, :fixTime, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes, :accuracy, :network);
INSERT INTO tc_positions_all (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes, accuracy, network) VALUES (:deviceId, :protocol, :serverTime, :deviceTime, :fixTime, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes, :accuracy, :network);
</entry>
Anton Tananaev3 years ago

I don't understand the problem.

  1. The query in question uses primary index, which should be very fast. Check execution plan.
  2. That query is only executed once at the start of the service. How does it affect performance? Why do you need to restart server?
Ahmad Bassel3 years ago

I am not sure if the problem from this query or other issue:

[SELECT tc_positions.* FROM tc_positions INNER JOIN tc_devices ON tc_positions.id = tc_devices.positionId;]

But I solve this issue by the following steps and I found the perfect way to keep only the current positions inside table tc_positions, the history positions saved on another table for other use like reporting.

#1- Stop the server completely to do some changes on server config and MySQL database
#2- Duplicate/Copy table [tc_position] to [tc_position_copy] without data
#3- Rename [tc_position] to [tc_position_all]
#4- Copy only the latest position from [tc_position_all] to [tc_position_copy], you can use the query below
INSERT INTO tc_position_copy SELECT * FROM tc_position_all WHERE id IN (SELECT positionId FROM tc_devices WHERE positionId IS NOT NULL);
#5- Rename [tc_position_copy] to [tc_position]
#6- Add a new Key for (deviceid) type (Unique) to [tc_position] under

#7- Alter the default.xml, just modify the below tags only with the new values:

<entry key='database.selectPosition'>
    SELECT * FROM tc_positions_all WHERE id = :id
</entry>

<entry key='database.selectPositions'>
    SELECT * FROM tc_positions_all WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to ORDER BY fixTime
</entry>

<entry key='database.updateLatestPosition'>
    UPDATE tc_devices SET positionId = :id WHERE id = :deviceId LIMIT 1;
    UPDATE tc_positions SET id = :id WHERE deviceId = :deviceId LIMIT 1;
</entry>

<entry key='database.insertPosition'>
    INSERT INTO tc_positions_all (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes, accuracy, network)
    VALUES (:deviceId, :protocol, :serverTime, :deviceTime, :fixTime, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes, :accuracy, :network);

    INSERT INTO tc_positions (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes, accuracy, network)
    VALUES (:deviceId, :protocol, :serverTime, :deviceTime, :fixTime, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes, :accuracy, :network)
    ON DUPLICATE KEY UPDATE protocol = :protocol, serverTime = :serverTime, deviceTime = :deviceTime, fixTime = :fixTime, valid = :valid, latitude = :latitude, longitude = :longitude, altitude = :altitude, speed = :speed, course = :course, address = :address, attributes = :attributes, accuracy = :accuracy, network = :network;
</entry>

<entry key='database.deletePositions'>
    DELETE FROM tc_positions_all WHERE serverTime < :serverTime AND id NOT IN (SELECT positionId FROM tc_devices WHERE positionId IS NOT NULL) AND id NOT IN (SELECT positionId FROM tc_events WHERE positionId IS NOT NULL)
</entry>

#8- Before running traccar server again, make sure that both tables tc_positions and tc_positions_all have the same Auto_Increament value for id.

These are the steps I did after long testing to make sure that the latest positions are kept in tc_positions and all other positions will be kept in tc_positions_all for reporting and other events searching.

Again, I did these modifications because we running the traccar server under a big number of devices [+5000] and we receiving a big value of positions every day [2 to 3 million records].

Hope this can explain it.