#1279 Huge memory usage due to "idle in transaction" with PostgreSQL

Reporter Finn Christiansen
Owner MattJ
Created
Updated
Stars ★★ (2)
Tags
  • Status-Fixed
  • Priority-Medium
  • Type-Defect
  1. Finn Christiansen on

    What steps will reproduce the problem? 1. Install Prosody 0.11 2. Use PostgreSQL as storage backend 3. wait some time and look at this command (memory usage is 4th column, it raises up to 90% after a few days, I have 6 GB memory on this server): ``` ps aux | grep 'postgres.*prosody' postgres 19597 0.2 2.9 324240 181588 ? Ss 15:25 0:02 postgres: 9.6/main: prosody prosody ::1(37262) idle in transaction ``` What is the expected output? The higher memory usage of the PostgreSQL server occurred after upgrading to Prosody 0.11. Also I can not remember seeing the database `idle in transaction` all the time. What do you see instead? I can see a higher memory usage every day. After restarting Prosody the memory usage of PostgreSQL is normal again. What version of the product are you using? On what operating system? * Prosody 0.11 * Debian 9.6 * PostgreSQL 9.6

  2. Thomas on

    Same here with Prosody 0.10.2 / Debian 9.6 / Postgres 9.6 I have 8 GB of RAM, still not enough. My server keeps crashing :-( Postgres reports: Not enough memory available. Prosody crashes at least once / day ... probably due insufficient memory.

  3. Zash on

    Hi, thanks for the report! Could you please include the version of LuaDBI? "idle in transaction" was reported before in #355 and believed to have been fixed by a LuaDBI update since nobody mentioned it in a few years.

    Changes
    • tags Status-NeedInfo
  4. Finn Christiansen on

    The version of LuaDBI is 0.7.1 and should be the newest version of Debian 9 stretch. I have looked up using dpkg: dpkg -l | grep -i lua-dbi ii lua-dbi-common 0.7.1-1~stretch3 all DBI library for the Lua language, common files ii lua-dbi-postgresql:amd64 0.7.1-1~stretch3 amd64 DBI library for the Lua language, PostgreSQL backend

  5. MattJ on

    This LuaDBI bug looks relevant: https://github.com/mwild1/luadbi/commit/616e2e26d1c4b779b6d7332308db39a1944556d7 I'll try to prepare a package with that patch applied so you folk can test it.

    Changes
    • owner MattJ
  6. MattJ on

    Hi folks, Thanks for your patience while I prepared packages for testing. I appreciate a crashing server is not what anyone wants over the holidays! Can you try installing this package? https://packages.prosody.im/debian/pool/dev/l/lua-dbi/lua-dbi-postgresql_0.7.1-1~stretch4_amd64.deb Either download the deb to your server and install it with `dpkg -i lua-dbi-postgresql_0.7.1-1~stretch4_amd64.deb` or alternatively temporarily add 'dev' to the end of your sources.list entry for the Prosody repo (i.e. after where it says 'main', so it will say 'main dev'). Then run `apt update` and `apt install lua-dbi-postgresql` (do not upgrade any other packages at this time, this is where we keep pre-release packages). After upgrade you can remove the 'dev' section from your sources.list. Let me know if this fixes the leak.

  7. MattJ on

    Hey, just checking whether anyone had a chance to test the new packages yet? We're close to a Debian freeze deadline in a few days, and it would be good to get this fix into the next stable version of Debian if it works!

  8. Finn Christiansen on

    Hi, thank you for your work but at least in my case it does not work. The Memory usage is about 780 MB after one day, the same as before. But what about you, Thomas? Here is a graph which shows you the memory usage over the last week: https://cloud.pimux.de/s/ckMTzx7yWm2ijTx

  9. MattJ on

    Thomas: you report the same issue on Prosody 0.10.2... Can you say when this started, and what version of lua-dbi you had installed? and as Finn asked, can you confirm whether the new package fixes the problem or not? Right now I want to figure out if I need to put my focus on Prosody or LuaDBI, so the answers to these questions will help!

  10. MattJ on

    Finn: Another question for you - what version of Prosody were you using before the 0.11 upgrade? 0.9.x or 0.10.x?

  11. Finn Christiansen on

    I was using 0.10.x until the beginning of december, then I upgraded to 0.11. You can see this here (3 month graph): https://cloud.pimux.de/s/ysPoEa7ksZni4Nm

  12. Thomas on

    Hey, @MattJ thanks for your work on a fix - I really appreciate it! :-) Sorry for being late. I've just installed the new package and hope it fixes the problem in my case. I'll let you know as soon as I can see any difference in my RAM statistics! This will take a few hours ... I cannot exactly tell when it started, but I guess somewhere around the new year. I remember having the first outage in January, 1st in the early morning. Best regards Thomas

  13. Thomas on

    Forgot to mention my previous lua-dbi-postgres version: It was 0.7.1-1~stretch3 before.

  14. Thomas on

    I have bad news - the new package didn't solve the problem. The steepness of the RAM usage curve is still about the same as before: https://imgur.com/a/y9rdPWt The red marker marks the time when I updates the lua-dbi-postgres package. The saw tooth pattern is because I automatically restart prosody every few hours to prevent it from eating too much memory.

  15. MattJ on

    Thanks both, the information has been really helpful! I've managed to create a small test script which reproduces the issue (small is good - less code to look through, easier to track down the bug). I can confirm that some version of LuaDBI 0.5 (the one that is packaged in stable versions of Debian and Ubuntu) works ok, but newer versions (0.6 and 0.7) are leaking prepared statements, causing postgres memory to increase with every query. I will continue to work on this today, and hopefully track down the cause and develop a fix.

  16. MattJ on

    I've pushed a fix here: https://github.com/mwild1/luadbi/pull/52 It is not yet merged, because this bug was introduced in a fix by another LuaDBI maintainer for a bug that I'm not able to reproduce. I want to give them a little time to review my fix and confirm it doesn't undo their fix. I will however now build packages for Debian stretch with this patch applied, so that you can get to testing them on your systems ASAP.

  17. MattJ on

    Ok, the new packages are available. The deb file is here: https://packages.prosody.im/debian/pool/dev/l/lua-dbi/lua-dbi-postgresql_0.7.1-1~stretch5_amd64.deb If you went for the apt installation method, just make sure you upgrade to the '0.7.1-1~stretch5' version of lua-dbi-postgresql. Eagerly awaiting feedback :)

  18. Thomas on

    I'm running the new version since a few minutes ago and I'm waiting for some statistics to be gathered. :-)

  19. Thomas on

    Tiny update: Looks much better! :-) https://screenshots.firefox.com/DRDUzD7JgHMOOTIH/monitor.650thz.de ... so then let's sit and wait for more data... but it looks promising. (Don't mind the gaps in between - my monitoring broke in the meantime ;-) )

  20. Thomas on

    Okay I consider this solved! :) https://screenshots.firefox.com/f6WSuRyq1Bix9hiF/monitor.650thz.de Thank you very much, @MattJ!

  21. Finn Christiansen on

    MattJ, thank you very much! This really seems to be solved, my memory usage is constant and I don't see "idle in transaction" anymore. https://cloud.pimux.de/s/FG4DALorpnwPaZR

  22. MattJ on

    Woohoo, thanks both! I've created a new issue to track a new LuaDBI release and packages for all distributions (not only stretch): https://issues.prosody.im/1287 I'll leave this issue open until that one is resolved.

    Changes
    • tags Status-Started
  23. Zash on

    Closing this too since LuaDBI has had a release.

    Changes
    • tags Status-Fixed

New comment

Not published. Used for spam prevention and optional update notifications.