1

i have a database that thousands of users need to connect to (via ODBC) for very brief periods (it's a subscription licensing database for a win32 desktop app). They connect, get their approval to run and disconnect). max_connections is set to 1000 but am not seeing the re-use i would expect server side. i.e. server currently has about 800 processes/connections sleeping (and another 200 connected to real data in other databases on the same server) .... yet a new attempt by a client app was rejected 'too many connections'.

What am i missing?

have increased the max_connections for now to 1500 but if that just means another 500 sleeping connections it's not a long term solution. pretty sure clients are disconnecting properly but am adding some diagnostics to the win32 app just in case.

MariaDB 10.3.11

with MySQL ODBC 5.3 ANSI Driver

nigelgomm
  • 79
  • 9

1 Answers1

0

It's normal to see a lot of sessions "Sleeping". That means the client is connected, but not executing a query at this moment. The client is likely doing other tasks, before or after running an SQL query. Just like if you are logged into a server with ssh, most of the time you're just sitting at the shell prompt not running any program.

It's up to you to design your clients to wait to connect until they need data, then disconnect promptly after getting their data. It's pretty common in apps that they connect to the database at startup, and remain connected. It's also pretty common in some frameworks to make multiple connections at startup, and treat them as a pool that can be used by multiple threads of the client app. It's your app, so you should configure this as needed.

Another thing to try is to enable the thread pool in the MariaDB server. See https://mariadb.com/kb/en/thread-pool-in-mariadb/

This is different from a client-side connection pool. The thread pool allows many thousands of clients to think they're connected, without allocating a full-blown thread in the MariaDB server for every single connection. When a client has something to query, at that time it is given one of the threads. When that client is done, it may continue to maintain a connection, but the thread in the MariaDB server is reallocated to a different client's request.

This is good for "bursty" workloads by many clients, and it sounds like your case might be a good candidate.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Bill, point is the client has disconnected... if the server kept sleeping process ready for re-use by next client that would be ok; but the next client got 'too many connections'. My problem with thread pool is the server is shared between this licensing database and others with real data and "permanent" connections - don't want to affect their performance by having to reestablish a connection for every query. – nigelgomm Apr 21 '21 at 16:11
  • 1
    Obviously, the clients have _not_ disconnected. Or else they have connected early, and are sleeping until they run their query and then disconnect. But MariaDB Server (and likewise MySQL) does not keep a connection active unless the client is connected. – Bill Karwin Apr 21 '21 at 16:29
  • hmmm... clients think they have disconnected. Connection is not pooled or shared client side. I'll see if there's a known issue with that mysql odbc connector and mariadb. – nigelgomm Apr 21 '21 at 16:37
  • another answer here recently (https://stackoverflow.com/questions/49497571/kill-inactive-mysql-connections-automaically) says that mysql/mariadb will keep closed connections open ready for re-use. Was that incorrect or simply that that's not what these sleeping processes are? – nigelgomm Apr 21 '21 at 16:45
  • 1
    The answers are misleading. MySQL has a thread cache, and it will reuse threads instead of creating new threads when a client connects. But a disconnected thread doesn't show up in SHOW PROCESSLIST. Only threads that are in use by client connections are shown there. – Bill Karwin Apr 21 '21 at 17:07
  • 1
    BTW, the MySQL thread cache is a leftover from many years ago when it was costly and slow to create a new thread. It used to be important to tune the thread cache size to support the rate of new client connections. But advancements in the Linux kernel have made it a lot quicker and low-impact to just create threads when needed, so hardly anyone tunes the thread cache size in MySQL/MariaDB anymore. – Bill Karwin Apr 21 '21 at 17:10
  • ok thanks for that. My problem then is that a client issuing a disconnect is not being disconnected server side. – nigelgomm Apr 21 '21 at 17:12