I'm really only interested in the connection pooling functionality of Vitess, is there a minimal example on how to set this up myself anywhere?
Hello! Many things are going on in your post.
Is Connection Pooling Required?
You said that your app works well for most of the day, but a couple times a day, there are exhausted connections. This sounds like a connection-leak type problem, where connections made for a given request are not cleaned up properly, leading to the DB being saturated and inaccessible.
There are three MySQL variables which might be the problem here:
- wait_timeout
- max_connections
- max_user_connections
wait_timeout is the most important one here: It is the amount of time that the MySQL server waits for a connection to be inactive before closing it. The default value is 28800 seconds (= 8 hours). You can reduce this value significantly. If your application has relatively low traffic (<100 QPS), then this alone should be enough: Reducing this value to something like 10 minutes should ensure that connections which are made by your server are cleaned up and new connectinos can be opened later in the day when required.
If you have set max_connections or max_user_connections to a non-default value, then the next time that you run into this problem, I would suggest getting a MySQL shell and running the command SHOW PROCESSLIST;. If the output shows a lot of connections with the Command column set to Sleep and the Time column set to a very large number, then the problem could be that the connections that are opened by the application are not being closed properly after processing a request; which leads to MySQL holding on to the connection forever and eventually reaching the max_connections limit.
On Connection Pooling
Your app is running in a Serverless environment. This probably means that the request that goes from your app to the DB originates from a "new" process each time a DB connection is required. So, application-side connection pooling is a no go in this use-case. (If it possible to use application-side connection pooling, that is the easiest solution. Many DB drivers have support for this, such as Golang).
In your case, my preferred solution would be to put a TCP proxy between your application and your DB. This proxy can open a set of connections to the DB. When the application opens a new connection to your proxy, it will use one of the existing connections, to connect to the DB and execute the SQL query. There are many solutions for this. In increasing complexity:
- https://github.com/kazeburo/chocon: Simple Golang based connection pooling
- ProxySQL: Dedicated SQL proxy
- HAProxy in TCP mode: High performance proxy for generic TCP traffic
All the above solutions are fairly easy to deploy. You can also learn more about this concept and the various ways in which connection pooling is implemented from this blog post.
On Vitess
If you want to use connection pooling only, you should not use Vitess. Vitess is a set of many components, but you want to use only the connection pooling feature of the VT Gate component. Personally, I don't think it is worth the effort of managing all the components.
But if you are set on using Vitess, you can. There is no minimal example. You will have to understand the Vitess architecture to some extent in order to use it.
For your case, you must start a Topology server (etcd) and a vtgate (proxy server supporting SQL and the component which implements connection pooling).
In addition to this, for every MySQL server that you have, you need to run a single vttablet process. Each vttablet process will use your MySQL server as its backend for data. This mode is known as the "Unmanaged Keyspace" in Vitess terminology. (docs here)
The commands for these components can be found from the following script 101_initial_cluster.sh. Inside this repository, you will find the various scripts which have the actual commands (such as vtgate-up.sh or etcd-up.sh).
- Topology server
- VT Gate
- VT Tablet for each MySQL server and this command
As I said, I don't think you should spend your time doing this unless you're super interested in Vitess itself. It's very cool, but not worth using for this particular case.
My Interest
I arrived at this question searching for a solution to the following set of logs output by a vttablet process:
I0524 07:54:46.128575 86458 tablegc.go:209] TableGC - started execution of Close. Acquiring initMutex lock I0524 07:54:46.128713 86458 tablegc.go:212] TableGC - acquired lock I0524 07:54:46.128729 86458 tablegc.go:214] TableGC - no collector is open I0524 07:54:46.128738 86458 engine.go:92] messager Engine - started execution of Close. Acquiring mu lock I0524 07:54:46.128746 86458 engine.go:94] messager Engine - acquired mu lock I0524 07:54:46.128761 86458 engine.go:97] messager Engine is not open
(The leading I in those lines indicates that these are INFO logs and don't warrant much attention! However, they are printed constantly and I did not notice this I until after I solved the problem.)
After some testing and poking around, I found the problem: It was because was only ONE VTTablet running. VT Orchestrator will promote one of the tablets for a given keyspace to primary only after it finds at least 2 tablets running for that keyspace! Vitess' guides always start 3 Tablets, but they don't mention that 1 tablet is insufficient.
I ran into this issue recently while trying to start the various processes required for a Vitess cluster manually. I started everything in the following order:
vtctld- Use
vtctldclient to add a new Cell and a new Keyspace - Start a
mysqlctld and vttablet process on a VM. This VM was able to connect to the topology server, but it kept printing the error logs above, such as I0524 07:54:46.128761 86458 engine.go:97] messager Engine is not open - Started
vtorc (Orchestrator) which can handle MySQL primary failovers. Errors on the VT Tablet remained unchanged. - Start another
mysqlctld and vttablet process. FIXED! Errors on VTTablet went away. VTOrc initialized a shard primary and created the required database in that MySQL instance.
This problem was resolved as soon as I started a second vttablet process! Once the second vttablet process registered itself with the Topology server, then, Orchestrator was able to promote one of them as the primary for that Keyspace and the other Tablet was used as the replica. It looks like vtorc expects at least 2 tablets to be up in order to promote one to primary. This is not clear from the Vitess documentation. All the documentation says is that multiple tablet-MySQL pairs should be brought up:
The next step is to bring up the rest of the vttablet-MySQL pairs on other machines or different ports of the same machine.
-- https://vitess.io/docs/19.0/user-guides/configuration-basic/vttablet-mysql/
... and ...
A new primary is elected automatically by VTOrc and no user action is required.
-- https://vitess.io/docs/19.0/user-guides/configuration-basic/initialize-shard-primary/
