0

I have a registration application. 10000 people want to access the server but only 3000 quotas available. 3000 users registered successfully online within 3 minutes on the registration day. However, some users encounter 504 error page (Gateway time out). I checked the max_used_connections variable, the value is 10 only. The max_connections value is 151.

The max_used_connections is defined as

the maximum number of connections that have been in use simultaneously since the server started.

I wonder why the value is only 10. Is it too small compared with 3000 users registered successfully online within 3 minutes?

Improve this question
3
  • Thank you all. I am using MySQL Server. The max_user_connections value is 0. The Max_used_connections value is 10. The max_connections value is 151. Since the Max_used_connections value is only 10 (not reaching 151) , I don't need to increase the max_connections value. Am I right? – Muffin Oct 29 '20 at 6:45
  • Someone suggest me to edit Max_used_connections to max_used_connections. But I just copy it in MySQL Workbench within the Status and System Variables column. It displays the Capital "M". – Muffin Oct 29 '20 at 9:18
  • @Muffin - STATUS values are readonly. What it displays (cap or lower case) is what you must use. – Rick James Oct 29 '20 at 15:35
1

Change the configuration file to increase max_connections. But be aware that that can increase RAM usage. How much RAM do you have? What is the value of innodb_buffer_pool_size?

You must streamline the "registration process" as much as possible. Please provide all the SQL that happens for registration so we can help optimize it.

Max_used_connections is a "high water mark". You never had more than 10 connections at the same time, at least not since the last restart. 10 is fine. And it cannot be changed. Since it is much smaller than Max_connections (151), there does not seem to be an issue. 10 connections at a time is fine.

Look at the STATUS Connections. That is a "counter" that has been running since restart. It will probably say more than 3000. 3000 in 3 minutes is only 17/second, not difficult if the registration process is simple.

The timeout probably indicates something else -- such as too much data flowing across the network. Does registration involve showing some biggish images on the registration page?

Instrument your registration process to see how many milliseconds the whole process takes, and how long each SQL statement takes.

What was the value of max_user_connections?

Improve this answer
2
  • Thank you very much. On the registration day, the CPU and RAM usage is only 18% and 19% respectively. We have strong enough hardware. The innodb_buffer_pool_size is 134217728. The STATUS Connections is 648002. The max_user_connections value is 0. For the registration process, it involves too many SQL statements. It does not involve showing some biggish images. For the most time-consuming stage, it involves select around 10000 in a SQL table for checking the time capacity. – Muffin Oct 29 '20 at 7:00
  • @Muffin - The 128M default for the buffer_pool is an old default -- and inadequate for most users; increase it (but not so much that it causes swapping). Let's try to optimize that "time capacity" query; start a new Question to focus on optimizing it; include EXPLAIN, CREATE TABLE, etc. – Rick James Oct 29 '20 at 15:33
1

I checked the max_used_connections variable, the value is 10 only.

I wonder why the value is only 10. Is it too small compared with 3000 users registered successfully online within 3 minutes?

Two words: Connection Pooling.

Your Application will be using this technique to reuse database connections between User requests. It's [much] faster than opening brand new connections for each and every request and allows a far smaller number of connections to support the same number of User requests.

I think the confusion is that the database refers to them as "user" connections - because they come from some "user" of the database, as opposed to the 3000 Users (/"Clients" /"Customers" /"People") that have made use of your Application.

Improve this answer
-1

Have you tried below commands on MySQL:

SET GLOBAL max_connections = 10000;
SET GLOBAL max_user_connections=10000;
Improve this answer
1
  • Those will go away when the next restart occurs. – Rick James Oct 29 '20 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.