[Krydos] Reducing mysql wait_timeout

[infinit5]

Hi,

i'm using Johnny Heliohost remote mysql and codeigniter 4 for development. After a few requests from the frontend, I'm seeing this error on mysql that user has more than max_user_connections (code 1203). While connecting db from backend, codeigniter seems to automatically close db connections. Even when I'm checking through workbench show processlist shows only 1 sleeping process. But then, suddenly max_user_connections exceeds the limit for the same request which was working a moment before.

So, my asks are jotted below:
- Can the mysql wait_timeout parameter be redued to 180 secs?
- From mysql documentation it seems the max_user_connections are set to 150 and another for super user, is there any additional limit from heliohost's end?

If there's any better way of dealing with this problem I'm all ears.

regards
inifinit5

Edited June 6, 2021 by infinit5

[wolstech]

It’s likely concurrent connections causing this. I would bet your app opens more than one when running, even if it closes them when finished.

 

I’m not sure of what the per-user limit currently is, but I think it was somewhere around 5 connections. Krydos would know.

[Krydos]

Johnny's max_user_connection is 4.

Ricky's max_user_connection is 6.

Tommy's max_user_connection is unlimited. (But if someone starts to abuse this it will be limited as well.)

 

 

i'm using Johnny Heliohost remote mysql and codeigniter 4 for development. After a few requests from the frontend, I'm seeing this error on mysql that user has more than max_user_connections (code 1203).

That means your code is either trying to open more than 4 connections at once, or isn't closing connections properly. A lot of the time you can change the number of max connections in your configuration. If you're writing your own code make sure each connection is closed immediately after it is done being used.

 

While connecting db from backend, codeigniter seems to automatically close db connections. Even when I'm checking through workbench show processlist shows only 1 sleeping process. But then, suddenly max_user_connections exceeds the limit for the same request which was working a moment before.

Most software is written for performance, not for behaving well on a shared host. If you have a vps and you're the only website on the server then it will increase performance slightly to open 100 mysql connections at once. Also there is a slight overhead when you open and close connections so you can get a little bit more performance by not closing them and using the same connection over and over. Obviously this isn't fair to the thousands of other users on your shared server who won't be able to connect to mysql at all because of your overuse. Therefore shared hosts have to have limits.

 

- Can the mysql wait_timeout parameter be redued to 180 secs?

That's actually an interesting idea that I've never considered. Personally I like to do mysql queries on the command line, and it would be annoying as hell for me to have my connection constantly being closed over and over. For instance I might create a new table on the command line, and then write some code, and then realize I need to add another column so I'll switch back to the mysql command line and run an alter table command. I also like to test mysql queries on the command line too before adding them to my code. If I had to reconnect every single time I wanted to run another query it would drive me insane. I'm not sure if many people code like this though.

 

- From mysql documentation it seems the max_user_connections are set to 150

Johnny's max_connections is set to 150. That means all the users on the whole server added together cannot exceed 150. This isn't the error you're seeing though. You're seeing max_user_connections which is just you, not affected by everyone else. The reason max_connections has to be set to a sane level is each mysql connection increases the memory required by mysql. With the current settings mysql uses about 3 GB of memory out of the 16 GB on the server. This is already a pretty sizable amount considering you need a ton of memory for apache processes and tomcat and everything else.

 

If there's any better way of dealing with this problem I'm all ears.

You could switch to Ricky or Tommy, but like I said above if you abuse the limit on Tommy you'll either be suspended or you'll ruin it for everyone else because I'll have to set a limit.