Hi Mike, Thanks for such a fast reply! We tried setting query_cache_size on a canary environment today, will be rolling it out widely on servers on Monday and can report back on if it has a noticeable impact.
After thinking about this more, I think our situation might exacerbate things a bit, in particular because: - We have many engines (about 4 of them are used heavily, but there are like 9 total). Some are for different databases, others have different configurations for a database (for example, one has a more aggressive statement timeout). - We're running behind a Gunicorn server which has 17 worker processes. Each of these workers processes will have their own caches. - It's a fairly sizable app (at least for the engines that have a lot of throughput) so we might be constantly adding keys to the cache and evicting stale ones (this one is more of a theory though). I'll report back if we see any changes. Thanks again for the fast reply (and for building such a useful + well documented library), Tony On Friday, October 21, 2022 at 12:20:07 PM UTC+8 Mike Bayer wrote: > > > On Fri, Oct 21, 2022, at 12:00 AM, 'Tony Cosentini' via sqlalchemy wrote: > > Hi, > > We recently upgraded our application (a Flask web app) from SQLAlchemy > 1.3.19 to 1.4.41. > > Overall things are stable, but we have noticed a very large increase in > memory use: > [image: Screen Shot 2022-10-21 at 11.26.18 AM.png] > > Is this from the new query caching feature? I'm planning on getting some > heap dumps to see if there is something obvious, but thought I'd ask here > as well. > > > you would be able to tell if you set query_cache_size=0 which then > resolves the memory issue. > > The cache itself uses memory, which can cause memory increases. However > we have a slight concern for the case of extremely large and highly nested > SQL constructs that might be generating unreasonably large cache keys. We > had one user with this problem some months ago and they were not able to > give us details in order to reproduce the problem. query_cache_size=0 > would prevent this problem also, but if you have very nested queries, > particularly with CTEs, we'd be curious if you can isolate particular > queries that might have that issue. > > > > The application is using the Postgres dialect. Nothing else was changed > besides the SQLAlchemy version. It's running in a Docker container with 8 > GB of RAM allocated to it. > > Anyway, I'll continue digging in more, but just asking in case there is > something obvious, > Tony > > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/33cc0c20-64b7-4624-af68-737347f18c13n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/33cc0c20-64b7-4624-af68-737347f18c13n%40googlegroups.com?utm_medium=email&utm_source=footer>. > > > > > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/940dba20-74ef-4401-aa18-a68866105316n%40googlegroups.com.
