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

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.

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.

• tags Status-NeedInfo

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

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.

• owner MattJ

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.

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!

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

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!

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

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

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

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

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.

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.

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.

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 :)

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

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 ;-) )

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

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

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.

• tags Status-Started

Closing this too since LuaDBI has had a release.

• tags Status-Fixed