Home Ask Login Register

Developers Planet

Your answer is one click away!

Andy February 2016

Figuring out mysql max connection limit

Having performance issues at peak times where MYSQL would go unresponsive. Trying to figure out if I am using the right value for max connections. The server has 64GB of RAM.

+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| bulk_insert_buffer_size      | 8388608    |
| innodb_buffer_pool_instances | 1          |
| innodb_buffer_pool_size      | 134217728  |
| innodb_change_buffering      | all        |
| innodb_log_buffer_size       | 8388608    |
| join_buffer_size             | 67108864   |
| key_buffer_size              | 2147483648 |
| myisam_sort_buffer_size      | 8388608    |
| net_buffer_length            | 16384      |
| preload_buffer_size          | 32768      |
| read_buffer_size             | 67108864   |
| read_rnd_buffer_size         | 2097152    |
| sort_buffer_size             | 2097152    |
| sql_buffer_result            | OFF        |
+------------------------------+------------+

lsof | wc -l
124101

lsof -a -p MYSQLDPID | wc -l
518

mysql> SHOW STATUS LIKE '%connection%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| Connections          | 25019772 |
| Max_used_connections | 451      |
+----------------------+----------+

Currently the connection limit is set to 350. Which other variables would I need to calculate correct connection limit?

Answers


Bernd Buffen February 2016

Look an the STATUS Vatiables. Connections and Max_used_connections will say you how many connections the Server has used.

SHOW STATUS LIKE '%connection%';

Variable_name   Value
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 13
Max_used_connections    7

Post Status

Asked in February 2016
Viewed 1,931 times
Voted 14
Answered 1 times

Search




Leave an answer


Quote of the day: live life