I am running a web.py server on my machine that continuously receives POST-data and stores some of the data in MySQL(5.7.19), and some in InfluxDB(1.3.1), both hosted on a remote server running Ubuntu(16.04.1).
I also have a Python script running locally, extracting InfluxDB-data and manipulating MySQL-data. This python script creates new connections to both databases each time its main function is run (closing them after each time as well), which can be up to 10 times per second. I am using the MySQLdb and InfluxDB Python APIs.
The problem is that after some time, I keep occasionally getting connection timeouts to both databases.
Here is how the error messages look like:
MySQL: OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')
InfluxDB: InfluxDBServerError: {"error":"timeout"}
After going through all my code carefully, I cannot find the cause of this problem. Does MySQL and Influx, or maybe even Ubuntu have a limit on connections per user and time?
When it comes to MySQL, some rows get locked using a SELECT FOR UPDATE query, but in the code, all transactions are either committed or rollbacked properly. If the connection is lost before commit or rollback, it would explain the MySQL timeouts, since it would be trying to access a locked row that does not get unlocked properly.
When it comes to InfluxDB, we are simply dealing with atomic insertions timing out, which does not make sense to me in the slightest.
If anyone has any idea what might be causing these timeouts I would be eternally grateful!
