Here at DoltHub, we've been building the world's first Git inspired SQL database. Along the way we've operated as you would expect a startup to operate - we've been iterating quickly and shipping features as soon as possible. One little gap we've been aware of is that of managing server connections. Until recently, dolt sql-server has had a simplistic approach to managing connections: Users could set the max-connections parameter and if there were more connections than that, the server would hold them in a spin loop until a connection slot was available.

No Longer! We've just shipped new options that allow you to have more granular control over your server connections. Let's jump in!

The Scenario

Up until recently, managing server connections with dolt sql-server was quite basic. Users could set the max-connections parameter, and if the number of connections exceeded this limit, the server would hold them in a spin loop sleeping for 500ms and checking again.

Ick! Imagine what this looks like as a client: You connect to your database, and then you wait. And wait. And wait some more. Worse yet, if a connection became available, you still needed to wait an additional grueling 500ms for the server to process your request. Spin loops are never a good thing. They waste time and resources.

To make matters worse, the server logs had no indication that the server was waiting for a connection slot to become available. So as an administrator you have clients waiting forever and you have no idea why. With no way to reject connections, Dolt server admins were forced to set a very high max-connections parameter. This can just make matters worse, for obvious reasons.

I've managed more than a few production SQL servers. Success looks like many users adopting your application, and that often times comes with scaling challenges. If your scaling challenges are met with hanging clients and no messaging, you've got a problem.

A Better Way

In the 1.51.0 release of Dolt, we've added two new options: back_log and max_connections_timeout_millis.

The back_log option specifies the maximum number of connections which may be blocked waiting to be accepted. If a connection request arrives when the queue is full, the connection will be immediately closed. If this parameter is not set, the default is 50. If you set it to 0, then the server will immediately reject any connections that arrive when there are already max-connections connections to the server.

And even then, no one wants to be blocked forever. There are some situations where the connections are rarely freed up and clients could block indefinitely. The max_connections_timeout_millis option has been added to avoid that situation. This option specifies the maximum number of milliseconds that a connection may wait to be accepted. If the connection is not accepted within this time, it will be closed. The default is 1 minute.

With these two options, you can now have more granular control over your server connections and ensure that your clients don't die silently waiting for attention.

And finally, we log warnings on the server when max-connections is hit, and when the back_log is full. Also the default max-connections is now 1000, so you have more capacity by default than you used to. Simple stuff to make your life easier.

An Example

Did you know that there is a config.yaml file generated by dolt sql-server that you can update to your liking? In a terminal, create a new directory and run dolt sql-server to start the server:

mkdir dolt-server-test
cd dolt-server-test
dolt sql-server

Stop the server with ^C, and then look in the config.yaml file. It has a section that looks like this:

# listener:
  # host: localhost
  # port: 3306
  # max_connections: 1000
  # back_log: 50
  # max_connections_timeout_millis: 60000
  # read_timeout_millis: 28800000
  # write_timeout_millis: 28800000
  # tls_key: key.pem
  # tls_cert: cert.pem
  # require_secure_transport: false
  # allow_cleartext_passwords: false
  # socket: /tmp/mysql.sock

These are the default values. You can see that port 3306 will allow 1000 connections. If you want to allow more, you uncomment the line and set the max_connections parameter to a higher value. Similarly, you may only want to allow 15 blocked connections, and you may only want them to wait no more that 5 seconds. Alter the section to look like this:

listener:
  # host: localhost
  # port: 3306
  max_connections: 2500
  back_log: 15
  max_connections_timeout_millis: 5000
  # read_timeout_millis: 28800000
  [snip]

Now start the server using the --config flag to point to the config.yaml file:

dolt sql-server --config config.yaml

You're all set! Now your clients won't block in despair!

What's Next?

This is the most basic level of support for server connection management. We can do more, like adjusting these parameters on the fly through the SQL interface. It's also possible to have a admin only port for system administrators to connect to the server when regular connections are exhausted. Come tell us on Discord what features make the most sense for you. We're excited to hear what you all are building with Dolt!