We've identified a significant source of memory over-use in the 1.4 caching system, on the particular environment where it was discovered, an extremely long and complex query nonetheless created a cache key that used orders of magnitude more memory than the statement itself. A fix for this issue will be released in version 1.4.44, however if you have the ability to test ahead of time to see if it resolves your issues, let me know. I am attempting to improve upon the patch to reduce memory use further. issue is tracked at https://github.com/sqlalchemy/sqlalchemy/issues/8790.
On Thu, Nov 3, 2022, at 8:33 AM, Mike Bayer wrote: > > > On Thu, Nov 3, 2022, at 3:11 AM, 'Tony Cosentini' via sqlalchemy wrote: >> 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. > > > OK it's too bad because we'd like to know what might be going on, the cache > should not really "thrash" unless you have elements that are not being cached > properly. it defaults to 500 which will grow as large as 750. It's > difficult for your application to have 750 individually different SQL > statements, all of which are in constant flow, unless you have some areas > where there are perhaps very custom query building mechanisms where query > structure is highly variable based on user input (like a search page). you'd > get better performance if you could restore the cache on and just locate > those specific queries which have too much variability in structure, and just > disable the cache for those queries specifically using the compiled_cache > execution option > (https://docs.sqlalchemy.org/en/14/core/connections.html#disabling-or-using-an-alternate-dictionary-to-cache-some-or-all-statements) > > > > >> >> 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: >>>>> 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 >> >> <https://groups.google.com/d/msgid/sqlalchemy/f82e64c2-7f78-456d-8d91-182b8b706037n%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/dfe718af-7d33-46e0-a15d-d36f31ac36e8%40app.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/dfe718af-7d33-46e0-a15d-d36f31ac36e8%40app.fastmail.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/02be8a79-dca8-47e6-95a7-954f74d60f8a%40app.fastmail.com.
