MySQL server reached max user connections quickly

Appsmith Support Appsmith Cloud
1

Hi,

I am working with in App with Appsmith connected to a MySQL server. When I am running my app and perform a single query, I see that 5-10 sessions are opened in my server (due to connection pooling). The issue is that these sessions rest in sleep mode so when I perform new queries, appsmith keeps opening more sessions and I quickly max up the max user capacity of my server (currently set at 100 max user connections). When my server is maxed up and I keep using my app, some of my queries start giving errors (queries could not be executed). I have tried to increase this maximum but my server provider says he can’t do it (mentioning the 100 is already quite a lot). I am wondering if there is any workaround that - thanks!!

2

Just to clarify, as soon as I run my 2nd query, the number of sessions or connections opened with my sql database jump to 15…

3

Hi there!
I’ve registered this issue with our engineers and I’ll relay to you as soon as I get a response

4

Could you check mysql.conf and see what is the value of wait_timeout and interactive_timeout . Let us know what are the values, for the meantime you can try decreasing the value to 300 (5 minutes). This will require a db restart.

5

Thanks - I can’t change these as global variables (only session) since my db is shared with other users. Is there a way I can work around it? If I set the session variables to 300, it only affects 1 of the multiple sessions that are opened by appsmith using connection pool

Thanks!

6

Hello, could you please tell me what version of Appsmith you are using and if it is self-hosted or cloud?

7

Hi,

I am using Cloud
Version is V1.9.6

Thanks!

8

Hello, you could try adding this to the end of your MySQL queries, it could solve your problems, additionally it is recommended that you try to increase the max_connections of MySQL

SELECT concat('KILL ',id,';') as query FROM information_schema.processlist WHERE Command = 'Sleep';

9

I’m also have the same issue, it always takes up all the connections when I connect to my database. Can you guys add an option to limit the pool size?

10

Hello,

You can currently solve this problem by following these steps:

  1. Log in to your database as an administrator.
  2. Execute this query to view the current allowed number of connections.

SHOW VARIABLES LIKE 'max_connections';"

  1. Execute this command to increase the max_connections value.

SET GLOBAL max_connections=[valor];

Additionally, your idea is great, and we have opened a feature request for it. You can track the progress of the feature request at the following link: [Feature]: Option to limit connection pool size in MySQL database · Issue #22525 · appsmithorg/appsmith · GitHub

11

I have the same issue as the above user where I am using a hosted service and don’t have administrative access. So having a configurable pool connection through appsmith would be great. Thanks!

12

I understand. I have opened a feature request for this. You can try using the workaround I provided to the previous user to mitigate your problem. The team will implement this feature soon. If you have any further questions, I’m happy to help.