0

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)
0

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).

0

Connections is a counter that keeps growing.

Connections / Uptime is "connections per second". If this is less than 1, then the rate of making connections is OK.

Threads_created -- again, find out the "per second" value. It, too, should probably be under 1/sec.

The VARIABLE thread_cache_size says how many "threads" or "processes" to keep around. 20 is a reasonable value for *nix. 0 is fine for Windows.

Back to the 'real' question... If MySQL is "slow" or "high CPU" or "high I/O", then locate the query that is likely to be the villain. SHOW FULL PROCESSLIST; might catch it. The slowlog, with long_query_time=1 will catch and record it.

With the query in hand, get SHOW CREATE TABLE for the table(s) involved. And EXPLAIN SELECT ... to have some clues of how it operates. Then show us those things; we can probably help you.

0

Looks like you are at the beginning of your investigation. Probably you should dive deeper in below areas to nail the issue.

  • OS Stats (CPU, memory, DiskIO, Load Avg).

  • Slow queries

  • Application deployment since last 24 hrs from time impact and review the application to see if anything that can bring slowness, if detected try to rollback the changes.

  • Any changes to DB server like backup, gzip or some strange query running for longer time.

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.