doesn't sound like a lock (MySQL is a very un-lockish database),
sounds like you have a query that occasionally returns a huge number
of rows, such as from a broken JOIN condition or similar.    The
occasional nature without any errors, as well as that the operation
you say should only be doing reads, suggests it is data and query time
related.

I'd use fine-grained logging with thread/process ids to identify slow
requests and potentially which kinds of operations are hitting the
issue.



On Fri, Sep 1, 2017 at 4:43 PM, YKdvd <davidobe...@gmail.com> wrote:
> 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.

-- 
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.

Reply via email to