Auto managed connection pooling for MySQL add-ons

Starting today, we provide ProxySQL, a MySQL proxy, on all of our managed instances for out of the box connection pooling to your MySQL add-ons.

ProxySQL acts as a local proxy between your application and your MySQL add-on. Your application will be able to connect to ProxySQL which will then forward your requests to the linked MySQL add-on.

A connection pool will cache your database connections and keep them open for future requests

Thanks to the connection pool, your database connections will be cached and your application won’t have to wait for the connection to be established to your add-on. This can lead to faster response time when a lot of requests are executed. It also solves the problem of too many opened connections to the database. You will be able to do as many queries as you need without getting a “user max connection exceeded” error. ProxySQL will queue all the requests until a connection slot is available.

Sidenote: ProxySQL is not available on Docker instances as we do not manage your Dockerfile.

Configure ProxySQL

To enable ProxySQL for your application, you first need to have a MySQL add-on linked to your application. You then have to set the CC_ENABLE_MYSQL_PROXYSQL=true environment variable.

Once enabled, the CC_MYSQL_PROXYSQL_SOCKET_PATH environment variable will be injected during the deployment phase. It provides a Unix Domain Socket path to use to connect to ProxySQL.

There are also two other environment variables you can set to configure ProxySQL to match your needs:

  • CC_MYSQL_PROXYSQL_USE_TLS: A boolean (true or false) to enable TLS between ProxySQL and your MySQL add-on. Defaults to true.
  • CC_MYSQL_PROXYSQL_MAX_CONNECTIONS: An integer which defines how many maximum connections ProxySQL will open to your MySQL add-on. Defaults to 10. You can refer to our ProxySQL documentation to learn more about how you should compute the maximum connection value when auto scalability is involved.

Examples

Here are some examples on how to connect to ProxySQL using various languages:

PHP using PDO

Using PDO, you have to use the unix_socket option in your DSN:

<?php
// This variable is injected during the deployment
$socket = getenv("CC_MYSQL_PROXYSQL_SOCKET_PATH");
// Get the database name from the environment
$db = getenv("MYSQL_ADDON_DB");
// Get the database user from the environment
$user = getenv("MYSQL_ADDON_USER");
// Get the database password from the environment
$pass = getenv("MYSQL_ADDON_PASSWORD");
$dsn = "mysql:unix_socket=$socket;dbname=$db";
try {
  $pdo = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
  throw new PDOException($e->getMessage(), (int)$e->getCode());
}

WordPress

For WordPress, you can change the DB_HOST variable in your wp-config.php:

// To connect using a unix socket, the syntax is: `localhost:/path/to/socket`
define('DB_HOST', "localhost:" . getenv("CC_MYSQL_PROXYSQL_SOCKET_PATH") );
// Get the database user from the environment
define('DB_USER', getenv("MYSQL_ADDON_USER"));
// Get the database password from the environment
define('DB_PASSWORD', getenv("MYSQL_ADDON_PASSWORD"));
// Get the database name from the environment
define('DB_NAME', getenv("MYSQL_ADDON_DB"));

Node.js

On Node.js, using the mysql npm package, you have to set the socketPath property :

const mysql      = require('mysql');
const connection = mysql.createConnection({
  // Get ProxySQL unix domain socket path from the environment
  socketPath : process.env["CC_MYSQL_PROXYSQL_SOCKET_PATH"],
  // Get the database user from the environment
  user       : process.env["MYSQL_ADDON_USER"],
  // Get the database password from the environment
  password   : process.env["MYSQL_ADDON_PASSWORD"],
  // Get the database name from the environment
  database   : process.env["MYSQL_ADDON_DB"]
});
connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

Metrics

ProxySQL exposes some metrics using the Prometheus format. Those metrics are automatically collected into our Metrics system and can be accessed in the Advanced Metrics view of your application. This allows you to track how effective your connection pool is and how many request your instances are making. You can get the list of metrics we expose in our ProxySQL documentation

Documentation

You can find more information about this feature in our ProxySQL documentation.

Conclusion

Using ProxySQL may help decrease the response time of your application if it does a lot of SQL requests. We’d love to hear from you on how ProxySQL’s connection pool affected your applications performances.

Blog

À lire également

Clever Cloud and Vates: Two French champions join forces to create comprehensive and autonomous cloud solutions

Nantes and Grenoble, May 23, 2024 - Clever Cloud and Vates are announcing today a partnership aimed at strengthening the strategic autonomy of the cloud in France and Europe.
Press

The Values and Policies of Personal Information Chair at Institut Mines-Télécom welcomes Clever Cloud among its sponsors

The Institut Mines-Télécom's Values and Policies of Personal Information (VP-IP) Chair conducts research into the issues surrounding the processing of personal data, digital identities and sovereignty, in a context of European regulation.
Press

Clever Cloud launches “UP”, a generous program dedicated to startups at VivaTech

Nantes, France - May 20, 2024 - Clever Cloud, French creator of deployment and hosting solutions in PaaS (Platform as a Service) mode, announces the launch of its new support program for startups, named “UP”. The initiative will be unveiled this Wednesday at the flagship event for technological innovation, VivaTech, in Paris.
Press