1

I have a provisioned, single-master RDS DB (5.6.mysql_aurora.1.23.0) with a writer and a reader. The clients are a bunch of jetty applications with a pool of up to 250 connections each.

I have for some time been struggling with intermittent problems: Connection atttempts start to time out, mostly. It seems to correlate with high loads, and I have started copying the entire information_schema.processlist to a log table at regular intervals (with a timestamp, obviously), and it seems the problem becomes most pronounced when the number of connections reaches 2000. It never gets near max_connections or max_user_connections:

mysql> show variables like "%max%conn%";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| aurora_max_connections_limit | 16000 |
| max_connect_errors           | 100   |
| max_connections              | 3000  |
| max_user_connections         | 0     |
+------------------------------+-------+
4 rows in set (0.03 sec)

So, which parameters should I concentrate on in order to get past this problem? I haven't found anything likely looking, that explains why 2000 connections seem to cause this; of course, it may simply be a coincidence that such a round number pops up.

2
  • What is the value of SHOW STATUS LIKE 'Max_used_connections';
    – Rick James
    Jun 9, 2021 at 21:34
  • And SHOW VARIABLES LIKE 'innodb_buffer_pool_size;`
    – Rick James
    Jun 9, 2021 at 21:35

1 Answer 1

1

This is most likely going to be the result of memory exhaustion. Every connection to the database requires a bit of memory and, if each connection needs 1MB (a low number), you could be looking at about 2GB of memory used just for the connections.

This SQL query will let you know the maximum amount of memory any single connection can use:

SELECT ( @@read_buffer_size
       + @@read_rnd_buffer_size
       + @@sort_buffer_size
       + @@join_buffer_size
       + @@binlog_cache_size
       + @@thread_stack
       + @@tmp_table_size
       + 2*@@net_buffer_length
       ) / (1024 * 1024) AS MEMORY_PER_CON_MB;

Check that this number isn’t too high (which is a subjective number based in experience with your specific databases). If the database is having its memory exhausted regularly, then you may need either a memory-optimized instance or stricter limits on how many resources each connection may consume.

1
  • Not sure if it is too high, but with 2000 connections it reaches some 35GB in total, so I think you are probably right.
    – j4nd3r53n
    Jun 10, 2021 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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