MySQL Database Optimization

For details on how to configure Traccar to work with MySQL database please check MySQL config.

Default MySQL configuration might be enough for a small setup, but if you have a large number of devices and/or high frequency of location reporting, you might want to adjust the database configuration. Optimal parameters depend on many factors, so following are just our preferences when running Traccar on DigitalOcean VPS servers with Ubuntu operating system.

Add custom configuration file:

nano /etc/mysql/mysql.conf.d/custom.cnf

An example for a server with 3GB of RAM:

[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0

• innodb_buffer_pool_size - buffer pool size; recommended to be about 70% of total available RAM
• innodb_log_file_size - log file size; bigger log file improves performance, but also increases recovery time if server crashes
• innodb_flush_method - defines the method used to flush data; O_DIRECT option improves write performance by avoiding double data caching
• innodb_flush_log_at_trx_commit - with a setting of 0, logs are written and flushed to disk once per second; improves performance, but potentially some data can be lost if server crashes

Restart MySQL service and Traccar service for new parameters to take effect:

sudo systemctl stop traccar
sudo systemctl restart mysql
sudo systemctl start traccar