search

LeventHAN / SquadStatsJSPRO

An advanced version of SquadStatJS (tracking your squad stats) with advanced discord commands to manage your discord server. (Soon with inbuild stat website)
MIT License
34 stars 29 forks source link

SQL Horrendous memory issue #10

Closed bombitmanbomb closed 3 years ago

bombitmanbomb commented 3 years ago

https://github.com/11TStudio/SquadStatsJSPRO/blob/7c4f7de4afa53efe229fc48b9d88d01409a48427/commands/Squad/profile.js#L110

Noticed after opening #9. Bot is opening a new sql connection for every command when using sql. These connections are never disposed of, and will remain open for 8+ hours. The bot will eventually run to a screeching halt.

The database connection pool is limited in size, and if it fills up, new connections will wait for old connections to be released. If you don't dispose them as soon as you're finished with them, they'll eventually get released when the finaliser runs, but that's an indeterminate amount of time in the future... So at best, you're going to see long delays when opening new connections.

At worst, they might've disabled the connection pool. Perhaps they discovered that after a while their app was returning "timeout waiting for connection" errors and disabling the connection pool "solved" that problem. However, this is much worse because it means you're creating a whole new connection every time - which is surprisingly resource intensive. If you've got hundreds of connections to the database open, it would be no wonder you're seeing performance problems.

The correct way to solve all of these problems is to dispose of your connection as soon as you're finished with it. The best idea is to hold the connection open for exactly as long as needed, and no longer.

You're code is doing the latter, Creating a new connection every time, and Never disposing. mysql connections can remain suspended for 8 hours by default.

bombitmanbomb commented 3 years ago

None of this will be freed by the garbage collector either as it's still "In Use".

LeventHAN commented 3 years ago

None of this will be freed by the garbage collector either as it's still "In Use".

Thank you for pointing it out, will fix it asap 💯

LeventHAN commented 3 years ago

This has been fixed in this PR https://github.com/11TStudio/SquadStatsJSPRO/commit/686b4d27fc6e1922a87fe6fb58d27628c1f353e9

As you mentioned from PM I will later today make this pormise based.