MySql database connection left open
It may be that Traccar is closing the database connection but the connection itself remains in a pool ready to be reused. This is to save the overhead of establishing a new connection.
If I remember rightly this is a .net thing rather than mysql/mariadb.
Have Google!
Traccar uses connection pool, so connections are reused and that's expected that some of them remain open to be reused later for higher performance. It's pretty much an industry standard for handling database connections and there's nothing untidy about it.
Hi Anton. Having done some traces on the d/b it looks like the first position update request opens a connection which is left sleeping; subsequent position updates (from the same or other devices) use that same connection, resetting its timer. Periodically, (about every 25 minutes?) a number of other connections are opened; either simultaneously OR 1 per second for 6 seconds. Each connection that's opened does the same queries in quick succession:
SET SESSION sql_mode=''
SELECT @@session.auto_increment_increment... and a long list of other session and global variables
SET character-set-results NULL
SET autocommit 1
SET sql_mode='STRICT_TRANS_TABLES'and finally does a
SELECT 1(These queries may be generated by the database driver at connection open time; I see similar (but different) behaviour when I open a MySql.Net connection.)
Then the connection goes to sleep until it's timed out by MySql. Occasionally the above sequence of queries on a new connection is made just the once, usually (but not always?) before the batch of 6, always taking up to a maximum of 10 sleeping connections (just the first of which gets re-used for the next position update).
Maybe this is normal behaviour, and is the service setting up connections in the pool if it finds fewer than the maximum (10?)
I'd be interested if you could confirm this; just to rule out any potentially malicious behaviour from unknown source.
We use HikariCP for the database connection management. If you're interested in specific logic, I would recommend reading their documentation.
Thanks Anton, appreciated. I can't find any Hikari config file in the TracCar application so I guess you're using default poolsize, which is indeed 10. I've confirmed these unused connections are really from TracCar so there doesn't seem to be a way to reduce the poolsize. We can live with that now we better understand what and why they are. Our own app is database-heavy (with TracCar being one very small component) and I was trying to keep the connection count low.
Maybe making Maximum Connections a configurable option in future releases is something to consider?
The pool size has been configurable for a very long time.
Thanks again Anton; sorted now.
I'm running TracCar on a windows server with a MySql (actually MariaDB) database. When mobile clients report their location data, the server opens a database connection to write the data but leaves it open. Eventually the sleeping connection gets timed out and closes, but in the meantime there are multiple (one per mobile user) connections hanging around. It's not a major issue in our environment, but it just looks "untidy" and does use some resource. I'm not familiar with the database driver being used; is there a way to ensure that connections are closed when not in use, or at least shared?
From the tracar.xml configuration file:
<entry key='database.driver'>com.mysql.cj.jdbc.Driver</entry> <entry key='database.url'>jdbc:mysql://localhost:3306/tracar?serverTimezone=UTC&useSSL=false&allowMultiQueries=true&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8&sessionVariables=sql_mode=''</entry>Thanks!