Hey, sorry for the crazy delay on this! We ended up turning off the cache in everything via query_cache_size and memory usage returned to previous levels. We also didn't see any noticeable change in CPU usage in our web servers.
We did see a pretty noticable perf regression in a worker job that is also very query heavy. For that we turned the cache back on and CPU usage ended up being lower than previous levels (kind of as expected given the caching gains). I think for our web servers, because of the number of processes + engines, we ended up with a very noticeable jump in memory usage. Additionally, I think when we did have aching turned on, we never really noticed any CPU usage improvements. My guess around this is because the cache might have been thrashing a lot - it's a fairly large code base so it might not have been very effective. Hope this helps for anyone else that runs into these kinds of issues. Thanks again Mike for the helpful response! Tony On Friday, October 21, 2022 at 8:08:32 PM UTC+8 Tony Cosentini wrote: > 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 sqlalchemy+...@googlegroups.com. >> 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f82e64c2-7f78-456d-8d91-182b8b706037n%40googlegroups.com.
