Positions Partition
You can partition by "deviceId" or by "deviceId" and "fixTime".
Are you saying to make separate table for each device if I go with only deviceid?
Is this a good idea if I have lots of devices (say 500) ?
You asked about table partitioning, not having separate tables.
So you answered my question this - You can partition by "deviceId" or by "deviceId" and "fixTime". What does this mean?
I want to clarify my question. I have 50 devices and the positions table has 4M rows. And when I try to make trips or get the latest positions, it increase the load on the machine and it takes more time to perform this. Therefore, I was asking if there is any way to reduce the load on the machine by partitioning the positions table.
Actually, I am not confident in Partitioning and I read some partitions By RANGE or By LIST and these two are not good when no. of devices are increased. Should I use Partition By HASH or KEY?
Explanation of what partitioning means:
https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm
Hello Anton,
Thanks for the suggestion and new problems come. I tried for the partition for a demo table was working. Therefore, I tried for positions table so MySQL couldn't do the partition because there is foreign key in positions table as deviceid which refers to the column id of devices table and I find this by this documentation https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html.
And I tried to remove the AUTO_INCREMENT, PK and FK, the positions don't report to the server.
Any idea how to resolve this or any other idea except the partitioning?
Okay, I did that with positions.id by using key(id) partitioning, it did not do the key(deviceid) partitioning, I had to remove foreign key. I was thinking that positions.deviceid ----> devices.id is not being used. I think this is good?
All foreign keys are used to clean up data when you remove other data, but it's not crucial to Traccar functionality. As long as you clean references yourself, you should be fine.
Thanks. One last thing what I am doing to use the technique of partitioning to reduce the latency, RAM or CPU usage, is this good approach? Or is there any better option than this?
I've never tried it, so I'm not sure if it would improve anything. Let us know.
Okay.
Have you tried anything regarding the million of rows or bigger than million of rows to make some optimization?
Can anyone tell me how I can do the partition of the positions table to optimize the load on the machine or to reduce the time for retrieval positions data from the table and for the insertion too when I have a big data in the positions table?