What should my "max user connections" for MYSQL database be set at?

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Anti-Spam Guide
WWu777
Registered User
Posts: 554
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

What should my "max user connections" for MYSQL database be set at?

Post by WWu777 » Fri Dec 15, 2017 3:48 am

Dear support,
My webhost says that it can set my max user connections for MYSQL databases at between 100 and 500. I set it at 500 before, but then my server kept crashing for some unknown reason. No one could explain it and my webhost could not determine why from server logs. So I asked them to lower the max user connections to 300 in case that was the cause. They said the default was 100 so I tried to find a middle ground.

So I was wondering, what is the ideal number it should be set at? Does 100 max user connections mean that only 100 users can view the forum, or that only 100 users can be logged in and posting? What does it mean exactly?

Will 500 max user connections overload a VPS server? What's the safe limit that won't overload a server?

What's the optimum it should be set at? Any idea?

Doesn't this forum on phpbb.com have a very high max user connections? What is it set at? Must be in the thousands since at any given time, thousands of people are on this forum right? How is that possible?

Thanks.

User avatar
AmigoJack
Registered User
Posts: 5361
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: What should my "max user connections" for MYSQL database be set at?

Post by AmigoJack » Fri Dec 15, 2017 8:35 am

WWu777 wrote:
Fri Dec 15, 2017 3:48 am
my server kept crashing for some unknown reason. No one could explain it and my webhost could not determine why from server logs.
Consider your hoster to be an idiot and move to one who understands what he's doing.

WWu777 wrote:
Fri Dec 15, 2017 3:48 am
what is the ideal number it should be set at
That's like asking for an ideal number of people for a town.

WWu777 wrote:
Fri Dec 15, 2017 3:48 am
Does 100 max user connections mean that only 100 users can view the forum, or that only 100 users can be logged in and posting?
No, connections and users are two things. Calling one page of your board creates 1 to 3 connections to your database, unbound to if that's a member of your board or a guest or a bot. So if one person is calling 5 topics of your board at once (speak: in his internet browser he opens 5 tabs) 5 to 15 connections to your database can be concurrent already.

WWu777 wrote:
Fri Dec 15, 2017 3:48 am
Will 500 max user connections overload a VPS server? What's the safe limit that won't overload a server?
That's like asking if 500 people will overload a train and asking for a safe limit for "a" train.

WWu777 wrote:
Fri Dec 15, 2017 3:48 am
Must be in the thousands since at any given time, thousands of people are on this forum right? How is that possible?
You do realize that "server" is also just a computer? And that it is also only limited by its hardware?
The worst thing about censorship is ███████████

Gr8Falcon
Registered User
Posts: 14
Joined: Thu Dec 14, 2017 5:58 pm

Re: What should my "max user connections" for MYSQL database be set at?

Post by Gr8Falcon » Fri Dec 15, 2017 7:35 pm

You should use the default settings. If your board has a lot of users those figures might have to be altered, but if your server is not capable of running with default settings, I suggest you say your server goodbye.

What happens if there are users that generates more connections then lets say 100 connections, is that the greater part of the 100 "calls" is qued, and are connected as the other connections has finished. Eaven if you set it on 100, your sql-server is still able to handle more connections, but just not at the exact same time.

WWu777
Registered User
Posts: 554
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: What should my "max user connections" for MYSQL database be set at?

Post by WWu777 » Tue Dec 19, 2017 12:03 am

AmigoJack wrote:
Fri Dec 15, 2017 8:35 am
WWu777 wrote:
Fri Dec 15, 2017 3:48 am
my server kept crashing for some unknown reason. No one could explain it and my webhost could not determine why from server logs.
Consider your hoster to be an idiot and move to one who understands what he's doing.
WWu777 wrote:
Fri Dec 15, 2017 3:48 am
what is the ideal number it should be set at
That's like asking for an ideal number of people for a town.
WWu777 wrote:
Fri Dec 15, 2017 3:48 am
Does 100 max user connections mean that only 100 users can view the forum, or that only 100 users can be logged in and posting?
No, connections and users are two things. Calling one page of your board creates 1 to 3 connections to your database, unbound to if that's a member of your board or a guest or a bot. So if one person is calling 5 topics of your board at once (speak: in his internet browser he opens 5 tabs) 5 to 15 connections to your database can be concurrent already.
WWu777 wrote:
Fri Dec 15, 2017 3:48 am
Will 500 max user connections overload a VPS server? What's the safe limit that won't overload a server?
That's like asking if 500 people will overload a train and asking for a safe limit for "a" train.
WWu777 wrote:
Fri Dec 15, 2017 3:48 am
Must be in the thousands since at any given time, thousands of people are on this forum right? How is that possible?
You do realize that "server" is also just a computer? And that it is also only limited by its hardware?
Well my webhost is recommended on phpbb on the webhost page as a recommended webhost, so it can't be that bad. See here:

https://www.phpbb.com/hosting/

So if each user who views a topic page creates 1 to 3 database connections, even if the user is just a guest, that means if my max data connections are set at 300, then only 100 users max can visit my forum? What if more than that come? What happens to them? Do they get a time out error?

What about this phpbb site? It has like 500 or more people online during peak hours, so its max user connections must be set at 1500 or more? Isn't that impossible on a server?

Well my VPS server has 2 GB RAM and 2 CPU cores. Does that tell you anything about the hardware?

WWu777
Registered User
Posts: 554
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: What should my "max user connections" for MYSQL database be set at?

Post by WWu777 » Tue Dec 19, 2017 12:07 am

Gr8Falcon wrote:
Fri Dec 15, 2017 7:35 pm
You should use the default settings. If your board has a lot of users those figures might have to be altered, but if your server is not capable of running with default settings, I suggest you say your server goodbye.

What happens if there are users that generates more connections then lets say 100 connections, is that the greater part of the 100 "calls" is qued, and are connected as the other connections has finished. Eaven if you set it on 100, your sql-server is still able to handle more connections, but just not at the exact same time.
Well I was told by my webhost that the default user connections for MYSQL is 100. Is that usual?

So what happens if a user or call is queued? Will they get a message that they are queued? Or to come back later? Or some error message like a time out error?

What's the optimal setting for a VPS server with 2 GB RAM and 2 CPU cores?

User avatar
Mick
Support Team Member
Support Team Member
Posts: 20237
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: What should my "max user connections" for MYSQL database be set at?

Post by Mick » Tue Dec 19, 2017 1:17 pm

WWu777 wrote:
Fri Dec 15, 2017 3:48 am
What's the optimum it should be set at? Any idea?
WWu777 wrote:
Fri Dec 15, 2017 3:48 am
Will 500 max user connections overload a VPS server? What's the safe limit that won't overload a server?
Who knows, you need to experiment, there isn't any one size fits all. Part of your job as a VPS owner is to make the settings to suit your purpose. Also note, server set up is beyond the scope of these support forums.
"The more connected we get the more alone we become" - Kyle Broflovski

There are no ‘threads’ in phpBB, they are topics.

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51402
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: What should my "max user connections" for MYSQL database be set at?

Post by Brf » Tue Dec 19, 2017 2:24 pm

WWu777 wrote:
Tue Dec 19, 2017 12:03 am
What about this phpbb site? It has like 500 or more people online during peak hours, so its max user connections must be set at 1500 or more? Isn't that impossible on a server?
That would only be necessary if all 500 clicked at the same instant. We have seen hosts for phpBB boards that had the maximum connections set at 15.

WWu777
Registered User
Posts: 554
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: What should my "max user connections" for MYSQL database be set at?

Post by WWu777 » Fri Dec 22, 2017 10:00 am

Ok well I have it set at 300 now for max connections. But yesterday my server crashed again suddenly. The server showed an "out of memory" error. But did not pinpoint any exact cause. I talked to Bluehost support for a long time and they could not give me an exact cause either. They only suggested I upgrade my RAM and pay more. However, when you Google "Bluehost sucks" you find that this has been happening to many sites, including simple Wordpress blogs that should not overload any server. I gotta wonder if this is a scam to try to get people to upgrade by deliberately crashing sites hosted on Bluehost. If so, that would be unusual behavior for a Mormon company.

I asked my forum about this issue and one of my members suggested that perhaps some of the extensions I installed in phpbb 3.2 is causing a memory leak. Is that possible? If so, which extension might possibly cause memory on a server to overload? Any idea?

These are the extensions I currently have installed on my forum.

EXTENSION NAME CURRENT VERSION OPTIONS ACTIONS
Enabled Extensions
Advertisement Management 1.0.3 Details Disable
Ajax Shoutbox 1.0.4 Details Disable
Authorized for urls 1.0.3 Details Disable
External Links Open in New Window 1.0.6 Details Disable
Google Search & Site Verification 3.2.3 Details Disable
Large Font 3.2.0-RC1 Details Disable
phpBB Media Embed PlugIn 1.0.1 Details Disable
Precise Similar Topics 1.4.2 Details Disable
Profile side switcher 1.0.0 Details Disable
Simple mentions 1.0.2 Details Disable
VigLink 1.0.2 Details Disable

Any idea?

Could it be the profile side switcher? One of my forum members said that every time he came here, he would get a message saying a script was running here that switched the location of the profiles from right to left, which came from an extension/plug in here. If that script runs every time a topic page is viewed here, then could that be the one that's causing the memory drain? What do you think?

User avatar
Mick
Support Team Member
Support Team Member
Posts: 20237
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: What should my "max user connections" for MYSQL database be set at?

Post by Mick » Fri Dec 22, 2017 11:41 am

Disable it/them and see.

Extensions are supported by their authors.
"The more connected we get the more alone we become" - Kyle Broflovski

There are no ‘threads’ in phpBB, they are topics.

sakm
Registered User
Posts: 510
Joined: Sun Jan 21, 2007 8:14 pm
Location: Hull, uk
Name: Stu
Contact:

Re: What should my "max user connections" for MYSQL database be set at?

Post by sakm » Sat Dec 23, 2017 6:23 pm

On average how many users do you have online at any one time?

You maybe getting a DDOS attack at the time of the crash or back ups could be running too if you are getting out of memory messages

I would lower the max connections to 100 to be honest

What web server are you using? If apache then I would look at tweaking this for better performance and make sure you are using php7 with fastCGI enabled

2gb ram could be low but it's certainly use able with the right setup

User avatar
WelshPaul
Registered User
Posts: 251
Joined: Tue Aug 19, 2014 2:09 pm

Re: What should my "max user connections" for MYSQL database be set at?

Post by WelshPaul » Sat Dec 23, 2017 9:15 pm

I have max connections set at 500. Had it set at this for over a year on a 1GB VPS without issue!

Here is part of my configuration:

Code: Select all

# GENERAL #
default_storage_engine          = InnoDB
explicit_defaults_for_timestamp = 1
interactive_timeout             = 180
pid_file                        = /var/run/mysqld/mysqld.pid
socket                          = /var/lib/mysql/mysql.sock
symbolic-links                  = 0
user                            = mysql
wait_timeout                    = 180

# MyISAM #
key-buffer-size                 = 32M

# SAFETY #
max_allowed_packet              = 16M

# DATA STORAGE #
datadir                         = /var/lib/mysql

# CACHES AND LIMITS #
max_connections                 = 500
max_heap_table_size             = 32M
query_cache_size                = 0
query_cache_type                = 0
table_definition_cache          = 1024
table_open_cache                = 2048
thread_cache_size               = 50
tmp_table_size                  = 32M

# INNODB #
innodb_buffer_pool_size         = 1024M
innodb_flush_method             = O_DIRECT
Your max_user_connections should always be lower than max_connections. An example:

max_connections = 10
max_user_connections = 5

I don't have any value set for max_user_connections but if I did, i'd start at 50 and adjust as necessary. Using a low value is usually best, this normally isn't a problem because MySQL connections are made and destroyed within a fraction of a second. If you are getting a max_user_connections error:
  1. Your code is opening the connection to the database, but not closing it.
  2. Your software is configured to use pconnect / Persistent Connections.
Max Connections is the total connections allowed overall and Max User Connections is the total connections allowed per user.

User avatar
JoshyPHP
Code Contributor
Posts: 964
Joined: Mon Jul 11, 2011 12:28 am

Re: What should my "max user connections" for MYSQL database be set at?

Post by JoshyPHP » Sun Dec 24, 2017 1:05 am

WelshPaul wrote:
Sat Dec 23, 2017 9:15 pm
I have max connections set at 500. Had it set at this for over a year on a 1GB VPS without issue!
That's because you've never used it. It takes thousands of concurrent users to to reach 500 concurrent connections.

1 GB of RAM split into 500 concurrent connections would leave at most 1-2 MB of RAM per connection. Even if the machine had enough CPU to handle that level of concurrency, it would have to use a lot of swap and slow down to a crawl. Or it would exceed the available swap and the kernel would just kill processes.
WWu777 wrote:
Fri Dec 22, 2017 10:00 am
Ajax Shoutbox 1.0.4 Details Disable
In my experience, typical "shoutbox" extensions are extremely inefficient by nature. Most likely culprit here.
I wrote the thing that does BBCodes in 3.2.

User avatar
WelshPaul
Registered User
Posts: 251
Joined: Tue Aug 19, 2014 2:09 pm

Re: What should my "max user connections" for MYSQL database be set at?

Post by WelshPaul » Sun Dec 24, 2017 10:29 am

JoshyPHP wrote:
Sun Dec 24, 2017 1:05 am
WelshPaul wrote:
Sat Dec 23, 2017 9:15 pm
I have max connections set at 500. Had it set at this for over a year on a 1GB VPS without issue!
That's because you've never used it. It takes thousands of concurrent users to to reach 500 concurrent connections.

1 GB of RAM split into 500 concurrent connections would leave at most 1-2 MB of RAM per connection. Even if the machine had enough CPU to handle that level of concurrency, it would have to use a lot of swap and slow down to a crawl. Or it would exceed the available swap and the kernel would just kill processes.
Correct! But when that time comes I would simply add more hardware to my server. Anyway, I wasn't trying to say that a 1GB VPS could handle that many connections just that it works when configured to.

WWu777
Registered User
Posts: 554
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: What should my "max user connections" for MYSQL database be set at?

Post by WWu777 » Fri Dec 29, 2017 11:04 am

sakm wrote:
Sat Dec 23, 2017 6:23 pm
On average how many users do you have online at any one time?

You maybe getting a DDOS attack at the time of the crash or back ups could be running too if you are getting out of memory messages

I would lower the max connections to 100 to be honest

What web server are you using? If apache then I would look at tweaking this for better performance and make sure you are using php7 with fastCGI enabled

2gb ram could be low but it's certainly use able with the right setup
During peak hours I get about 80 to 100 visitors in the forum. During non-peak hours, less. Why would you lower the max connections to 100? Any reason?

I think I have an apache server. How do I tweak apache? I think I can access apache via web host management. But I have no idea what settings to change or what is ideal. I think I saw that I had php7 in my apache server.

My smart phone has 2GB RAM and it slows down a lot so I'm surprised a VPS server could run a whole site and forum with only that.

WWu777
Registered User
Posts: 554
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: What should my "max user connections" for MYSQL database be set at?

Post by WWu777 » Fri Dec 29, 2017 11:08 am

WelshPaul wrote:
Sat Dec 23, 2017 9:15 pm
I have max connections set at 500. Had it set at this for over a year on a 1GB VPS without issue!

Here is part of my configuration:

Code: Select all

# GENERAL #
default_storage_engine          = InnoDB
explicit_defaults_for_timestamp = 1
interactive_timeout             = 180
pid_file                        = /var/run/mysqld/mysqld.pid
socket                          = /var/lib/mysql/mysql.sock
symbolic-links                  = 0
user                            = mysql
wait_timeout                    = 180

# MyISAM #
key-buffer-size                 = 32M

# SAFETY #
max_allowed_packet              = 16M

# DATA STORAGE #
datadir                         = /var/lib/mysql

# CACHES AND LIMITS #
max_connections                 = 500
max_heap_table_size             = 32M
query_cache_size                = 0
query_cache_type                = 0
table_definition_cache          = 1024
table_open_cache                = 2048
thread_cache_size               = 50
tmp_table_size                  = 32M

# INNODB #
innodb_buffer_pool_size         = 1024M
innodb_flush_method             = O_DIRECT
Your max_user_connections should always be lower than max_connections. An example:

max_connections = 10
max_user_connections = 5

I don't have any value set for max_user_connections but if I did, i'd start at 50 and adjust as necessary. Using a low value is usually best, this normally isn't a problem because MySQL connections are made and destroyed within a fraction of a second. If you are getting a max_user_connections error:
  1. Your code is opening the connection to the database, but not closing it.
  2. Your software is configured to use pconnect / Persistent Connections.
Max Connections is the total connections allowed overall and Max User Connections is the total connections allowed per user.
Just to be safe I had my webhost lower the max connections to 300. What's the difference between max connections and max user connections? I thought they were the same thing. And I thought one user or guest = one user connection? This is confusing.

How do I set my configuration like in the code box above? What admin panel is all that on? I have no idea what the terms in it even mean.

Post Reply

Return to “phpBB Discussion”

Who is online

Users browsing this forum: koraldon and 78 guests