Hello Traccar Team, we are experiencing latency at database level. We use AWS RDS MySQL database with 2vCPU and 16GB RAM, nevertheless we are experiencing 2 second latency over queries. I believe this is not normal. Might the size of database (800GB) have something to do with this delay?
Load by waits (AAS) | SQL statements | Calls/sec | Avg latency (ms)/call
0.18 | INSERT INTO `tc_positions` ( `address` , `valid` , `fixtime` , `latitude` , `lon... | 79.27 | 2.26
0.17 | UPDATE `tc_devices` SET `positionId` = ? WHERE `id` = ? | 74.09 | 2.25 1.00
0.15 | UPDATE `tc_devices` SET STATUS = ? , `lastUpdate` = ? WHERE `id` = ? | 70.81 | 2.22
MySQL [traccar]> SHOW INDEXES FROM tc_positions;
+--------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tc_positions | 0 | PRIMARY | 1 | id | A | 647719936 | NULL | NULL | | BTREE | | | YES | NULL |
| tc_positions | 1 | position_deviceid_fixtime | 1 | deviceid | A | 166069 | NULL | NULL | | BTREE | | | YES | NULL |
| tc_positions | 1 | position_deviceid_fixtime | 2 | fixtime | A | 581361408 | NULL | NULL | | BTREE | | | YES | NULL |
+--------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)
MySQL [traccar]> SHOW INDEXES FROM tc_devices;
+------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tc_devices | 0 | PRIMARY | 1 | id | A | 976 | NULL | NULL | | BTREE | | | YES | NULL |
| tc_devices | 0 | uniqueid | 1 | uniqueid | A | 976 | NULL | NULL | | BTREE | | | YES | NULL |
| tc_devices | 1 | fk_devices_groupid | 1 | groupid | A | 23 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tc_devices | 1 | idx_devices_uniqueid | 1 | uniqueid | A | 976 | NULL | NULL | | BTREE | | | YES | NULL |
| tc_devices | 1 | idx_positionId | 1 | positionid | A | 964 | NULL | NULL | YES | BTREE | | | YES | NULL |
+------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.01 sec)
Thanks in advance for your help with this issue.
And now they will ask you what have you done so far to optimize mysql and the database..
Where do you see the 2 second latency?
Hello Anton,
In AWS RDS there is a tool for performance insights which outputs the table provided, for example:
Query INSERT INTO `tc_positions` ( `address` , `valid` , `fixtime` , `latitude` , `lon... has an average latency of 2.26 seconds
This way I am sure latency is in database.
Optimization is based on https://www.traccar.org/mysql-optimization/
innodb_buffer_pool_size = 12G
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
Yeah, sounds like the database is just slow. Is it using good SSD drives?
It's SSD, I am using a db.r6gd.large with 2 vCPU, 16 GiB RAM 1 x 118 NVMe SSD GiB of Storage, Up to 4,750 Mbps bandwith, Up to 10 Gbps Network bandwidth
Inserts into innodb tables can be remarkably slow.
MyIsam is much quicker though you lose referential integrity. You can replicate RI with triggers.
Is your suggestion to change Database Engine Richard Creer?
i don't think for traccar you can as it uses foreign keys
I'm not suggesting any particular course of action. I'm just saying that innodb inserts can be much slower than MyIsam. FYI it is possible to replace innodb with MyIsam and replicate referential integrity/foreign keys with triggers. I don't recommend that approach for a whole database but I have implemented it on occasions when innodb inserts were too slow.
Curious, how did you manage to store 800 GB of data on a db.r6gd.large with 118 NVMe SSD GiB?
I have used the same instance type db.r6gd.large with 118 NVMe SSD GiB for a customers project , total data stored is now at 80% of the total storage capacity with no noticeable latency issues.
RDS has autoscaling storage SwayDev. Can you tell me what is your average latency on insets for tc_positions on working hours? I have almost 1000 devices with 10 second fixed time transmission while in operation latency average is 2 seconds per insert but at night is lowers down.
Ahh, okay.
Average active device daily: 1025
Reporting interval: 10 seconds
Position table size: 94 GB, with insert latency of roughly 0.01 seconds.
However I am not using autoscaling and my database is relatively smaller in size compared to yours.
Hello Traccar Team, we are experiencing latency at database level. We use AWS RDS MySQL database with 2vCPU and 16GB RAM, nevertheless we are experiencing 2 second latency over queries. I believe this is not normal. Might the size of database (800GB) have something to do with this delay?
Thanks in advance for your help with this issue.