Noticed one problem with notifications when position table get big
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>
I don't understand the problem.
- The query in question uses primary index, which should be very fast. Check execution plan.
- That query is only executed once at the start of the service. How does it affect performance? Why do you need to restart server?
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.
Hello, I have exactly the same problem, when deleting old positions (cleaning data) the new insertions are delayed, I have not been able to solve it, I have had to empty the tc_positions table every 2 months and leave only the latest locations of the devices. I want to ask you if this solution that you propose does not affect the general operation, the events, the reports, etc.?
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:
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.