1.4.44 is released with this change. if you can try it out with your application and let me know if you see improvements in memory use for your memory-intensive case, that would be much appreciated! thanks
https://www.sqlalchemy.org/blog/2022/11/12/sqlalchemy-1.4.44-released/ On Fri, Nov 11, 2022, at 11:42 AM, Mike Bayer wrote: > 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 [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/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 [email protected]. >> 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/02be8a79-dca8-47e6-95a7-954f74d60f8a%40app.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/02be8a79-dca8-47e6-95a7-954f74d60f8a%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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/279befa1-fce3-4801-b29f-d64bf9cff3b3%40app.fastmail.com.
