I have a medium sized database (7 GB) with around 200 concurrent users. I am getting some database lag issues, suddenly my node-mysql client freezes during selects and inserts.

As a process of troubleshooting I checked SHOW STATUS on the DB. Everything seemed to be okay, but the connections attribute has a value of 262050.

I want to understand if this number is okay or if the figure is exorbitant?

  • No, you should not be having 262050 connections to your database. Even 2620 would be too many in my view. – mustaccio Oct 11 at 13:10
  • 1
    Server Status Variables. Connections - The number of connection attempts (successful or not) to the MySQL server. – Akina Oct 11 at 13:44
  • @Akina you should post that as an answer, and indicate not to confuse it with threads_connected – jynus Oct 11 at 19:55

Server Status Variables

Connections

The number of connection attempts (successful or not) to the MySQL server.

I.e. it is a counter which can increase only. Each attempt to connect to server, both successful and falling, increase it. Each connection, both existing and already broken, is taken into account. So this variable value can be used only for to calculate the average number of connections per some time period.

Threads_connected

The number of currently open connections.

It is the amount of real external connections in any state. Do not include inner server services connections (sheduler connection, for example).

Threads_running

The number of threads that are not sleeping.

It is the amount of real connections not in sleeping state, including inner server services connections (sheduler connection, for example).

You can see a correlation between last 2 variables while compare the output of the commands

SHOW GLOBAL STATUS 
    WHERE Variable_name IN ( /* 'Connections', */
                            'Threads_connected',
                            'Threads_running');

and

SHOW PROCESSLIST;

262050 Connections per year -- Yawn.
262050 Connections per hour -- That is very high!

What is the value of max_connections? max_used_connections? Threads_connected during a spike? Etc.

Divide by Uptime to get connections per second:
10th percentile: 1 connection every 2 minutes
50th percentile: 1/second
90th percentile: 25/second

Are you using InnoDB? (MyISAM has more ways that it can apparently "freeze".)

The slowlog will help determine the villain in this drama.

See this for how to tackle the slowlog and the settings, including Connections.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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