- The database was set up behind behind an AWS ELB and HAProxy.
- The idle connection timeout on the ELB was set to 60 mins.
- All the relevant timeouts on HAProxy seemed to be set to 60 mins too.
- The pool_recycle in SQLAlchemy was set to 30 mins.
- I was still seeing the occasional "Lost connection to MySQL server during query" when small queries were running after the connection had some time to sit around.
The relevant section of the docs for session.commit():
By default, the Session also expires all database loaded state on all ORM-managed attributes after transaction commit. This so that subsequent operations load the most recent data from the database. This behavior can be disabled using the expire_on_commit=False option to sessionmaker or the Session constructor.
In Python 2.7, it knows to switch to different thread whenever it starts waiting on IO. And, it will check if it needs to switch threads every 100 bytecode instructions.
The docs for session.execute say:
Execute a SQL expression construct or string statement within the current transaction.
So, the answer is yes, you need to issue a commit after running session.execute().
Here’s a code example along with the output from “echo=True” showing it begins the session but doesn’t end it unless you run session.commit():
If you're using scoped_session and not explicitly running session.remove(), then your connections will only be returned to the pool after your thread finishes and garbage collection occurs. It's not a good idea to leave this to garbage collection, because you can't guarantee your connections will be returned to the pool when your application is busy. This will often lead to errors like this: "TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30"
The solution is to make sure session.remove() runs when the work finishes (like flask-sqlalchemy does), as described here: http://docs.sqlalchemy.org/en/latest/orm/contextual.html#using-thread-local-scope-with-web-applications
Another alternative is not making the session global and using a context manager, as described at the bottom of this section: http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it However, this can make things difficult when you need to access the query results outside of the context manager. If you access the query results outside of the context manager without running session.expunge_all(), you'll see all kinds of errors like this: "DetachedInstanceError: Instance <ReportingJob at 0xa41cd8c> is not bound to a Session; attribute refresh operation cannot proceed"
Another option is setting autocommit to True, but that has quite a few gotchas. Turning on autocommit will acquire connections from the engine on an as-needed basis and return them immediately after their use. You will have to explicitly start transactions with session.begin() if you enable it. It seems like this setting should be called "autotransaction" instead. It's also not very efficient, because it needs to get and return a connection each time you run a query.
PHP docs have this to say about max_execution_time:
"The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. This is not true on Windows where the measured time is real."
The solution ended up being setting request_terminate_timeout in php-fpm.
Here's an example of the error:
And an example of the fix: https://bitbucket.org/zzzeek/dogpile.cache/pull-requests/32/add-a-cachetools-lru-lfu-in-memory-backend/diff#comment-22242704
PHP (using the default PHP_ROUND_HALF_UP)
php > echo round(-1.5);"Round val up to precision decimal places away from zero, when it is half way there. Making 1.5 into 2 and -1.5 into -2."
Math.round(-1.5);"For negative numbers, if the decimal portion is exactly -0.5, the return value is the smallest integer that is greater than the number."