MySQL Connections slows down the website

MySQLoptimizationperformancequery-performance

I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database.

I ran these queries

show status like 'Connections';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 78    |
+---------------+-------+
1 row in set (0.01 sec)

show status like "%thread%";

+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 3     |
| Threads_connected                        | 6     |
| Threads_created                          | 17    |
| Threads_running                          | 6     |
+------------------------------------------+-------+
8 rows in set (0.01 sec)

show variables like "%max_connections%";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.02 sec)


Version :
+----------------------------------+
| version()                        |
+----------------------------------+
| 5.6.35-1+deb.sury.org~xenial+0.1 |
+----------------------------------+
1 row in set (0.00 sec)

What i found out is, when ever the # of connections above 20, the site becomes very slow to load.

I am not sure why this is happening, or whether this is normal. I tried to adjust the 'max_connections' variables but still no luck. Has anyone ever got this problem as well? How can i optimize this ?

Thanks for the help.

Here's a log related to Connections:

+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 1     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 48    |
| Max_used_connections                          | 6     |
| Performance_schema_session_connect_attrs_lost | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 6     |
+-----------------------------------------------+-------+
14 rows in set (0.01 sec)

Best Answer

You do not mention what language your application is in but if you are connecting on every page requested you could add a lot of speed simply by using a database connection pool. That way you application will not need so many connections and will not have to connect on every request, giving you extra speed simply because you don't reconnect every time and also reducing drastically the number of concurrent connections it uses, which seems to be your problem.

I have only seen problems like this you mention on Windows machines. Most of this problems are also highly attenuated simply by moving your db to GNU/Linux (or BSD).

Related Question