MySQL

DBO provides connection pooling for MySQL databases through ProxySQL, a high-performance MySQL proxy. ProxySQL sits between your application and MySQL and multiplexes application connections onto a smaller pool of real database connections, reducing overhead and improving scalability.

📘

Note

This page covers connection pooling for MySQL databases. For PostgreSQL, see PostgreSQL.

Configuration

ProxySQL is enabled and configured through the castai-db-optimizer Helm chart. You can enable it by creating a custom values.yaml file and including it when you run the deployment script.

ProxySQL maintains its own user list (mysql_users) and authenticates every client connection itself. If a username is not present in the ProxySQL configuration, the connection is rejected at the proxy with a ProxySQL Error: Access denied response — it never reaches MySQL. This means the users you configure are the credentials your applications will use when connecting to the DBO endpoint, and ProxySQL will forward those same credentials to the upstream MySQL database.

⚠️

Important

If your application connects with multiple different database users, each of those users must be defined in the ProxySQL configuration. Missing users will be rejected by ProxySQL, even if they exist in the upstream MySQL database.

You can provide users in one of two ways. These options are mutually exclusive.

Recommended setup (external Secret)

Store credentials in a Kubernetes Secret and reference it from your Helm values. This is the most secure option for production.

Create the Secret with a users.json key containing a JSON array of username/password objects:

kubectl create secret generic proxysql-users \
  --namespace castai-db-optimizer \
  --from-literal=users.json='[{"username": "app_user", "password": "app_password"}, {"username": "readonly_user", "password": "readonly_password"}]'

Create a values.yaml with the ProxySQL configuration:

protocol: "MySQL"

proxySql:
  enabled: true
  usersSecretRef: "proxysql-users"

Without external secrets

If you prefer not to create a separate Secret, define the users directly inside values.yaml:

protocol: "MySQL"

proxySql:
  enabled: true
  users:
    - username: "app_user"
      password: "app_password"
    - username: "readonly_user"
      password: "readonly_password"

📘

Note

You can also provide a single user via the user and password fields instead of the users list.

Apply the configuration

Pass your custom values.yaml to the deployment script by setting the HELM_ARGS environment variable before running the script provided in the Cast AI Console:

export HELM_ARGS="-f values.yaml"

Then run the deployment script. For more details on deploying DBO, see the quick start guide.

Will your application benefit?

The main value of a pooler is multiplexing — letting many application connections share a smaller number of real database connections. Three signals indicate your app is a good candidate.

Short transactions

If your application executes queries that are fast and frequent, connections are released back to the pool quickly and can be reused by other clients. This is the core mechanic that makes pooling valuable.

If your application runs only a few slow queries, connections stay checked out for longer, leaving little opportunity for sharing. Pooling will still work, but the benefit will be minimal.

High idle connection ratio

A high ratio of idle to total connections suggests that clients are holding open connections without actively using them. A pooler can absorb these idle clients without tying up real database connections.

Connection spikes

If your connection count periodically surges well above its typical average, a pooler is particularly valuable. ProxySQL acts as a buffer: MySQL sees a stable, capped number of connections while the pooler absorbs the spike and queues clients waiting for a free slot.

Application compatibility

ProxySQL is transparent to your application — no code changes or compatibility audit required. By default, ProxySQL pins client connections to a specific backend connection, which means your application will work without any modifications out of the box.

The trade-off is that connection pinning reduces the multiplexing efficiency of the pool. If you want to maximize pooling performance, pinning can be relaxed, but that may require validating your application against the same kinds of session-state constraints that apply to PostgreSQL pooling.