Turned on the slow query log, doesn't seem to be that. The fact that it isn't consistently bad for similar hits, and also seems to need some load to be exposed makes me thing either some contention while inside the request in Python, including lazy loads. I have been able to get some timings on a somewhat (but not hugely) slow hit on one of the specific routes while running the Flask app in standalone debug mode, and it seems to indicate that the time is being taken inside Python, no problem with a lack of Apache or WSGI threads causing the delay, and before doing any rendering of the HTML template for return. It certainly seems to be related to DB stuff, rather than any CPU/Python issues.
I'm willing to believe that this is something that was present in our old MySQL 5.5 setup at a lower frequency or shorter delays, but I'm not sure what in the new setup would have caused a sudden exacerbation of the problem. The old 5.5 MySQL had a query cache of either 8M or 16M, the new 5.7 MySQL started with a 64M query_cache. I've now disabled the query_cache, and while overall performance seems a little better, the odd spikes are still there. All tables of interest should be InnoDB, and there's a 2GB innodb_buffer_pool_size, which should be well in excess of actual data. The specific request route I've been trying to look at should only be doing reads, no writes or updates, and I wouldn't think any of the little one-to-one relationships lazyloads it might indirectly trigger would be locked by a different thread, certainly not for appreciable time. I'm going to try debugging with the pools "timeout" set to a very low value (it defaults to 30 seconds) to see if it ever hits max_overflow and fails to instantly get a connection when needed, but out WSGI processes have 10 threads and the pool shouldn't hit this until 5+10=15 connections are active, and unless I'm missing something I should not be using more than one connection per thread - this was why I was checking for a built-in high-water counter for pool connections. I'll keep checking my understanding of MySQL and SQLAlchemy for anything that could be holding a row locked that I'm not realizing, or something similar. SQLAlchemy isn't throwing any exceptions related to deadlocks, connections exhaused, etc, as these would bubble up and abort the Flask request. On Friday, September 1, 2017 at 4:51:08 PM UTC-3, Jonathan Vanasco wrote: > > what do your database and server logs show? are there slow query > timeouts, deadlocks, etc? are these happening during peak load? 10-30 > seconds looks more like an issue with competing locks or available workers, > than potentially pool checkouts or lazy loading. > > aside from db logs, i've found statsd very useful for debugging these > sorts of things. we log both requests count/timing and hundreds of points > in our codebase as well. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
