#1279 Huge memory usage due to "idle in transaction" with PostgreSQL
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.
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
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?
@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.
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:
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.
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.