Gérer les connexions à la base de données

Vous trouverez sur cette page des bonnes pratiques et des exemples de code propres aux langages. Ils vous aideront à créer des applications qui utilisent efficacement les connexions à la base de données Cloud SQL.

Ces exemples sont des extraits d'une application App Engine complète disponible sur GitHub. En savoir plus

Pools de connexions

Un pool de connexions est un cache de connexions à la base de données, qui sont partagées et réutilisées pour améliorer la latence et les performances de connexion. Lorsque l'application a besoin d'une connexion à une base de données, elle en emprunte une temporairement dans son pool. Une fois qu'elle en a terminé avec cette connexion, elle la renvoie au pool, où elle pourra la réutiliser la prochaine fois qu'elle aura besoin d'une connexion à une base de données.

Ouvrir et fermer des connexions

Lorsque vous utilisez un pool de connexions, vous devez ouvrir et fermer les connexions correctement, de sorte qu'elles soient toujours renvoyées au pool une fois que vous avez terminé. En cas de non-renvoi ou de "fuite" des connexions, elles ne sont pas réutilisées, ce qui gaspille des ressources et peut entraîner des goulots d'étranglement affectant les performances de l'application.

Python

# Preparing a statement before hand can help protect against injections.
stmt = sqlalchemy.text(
    "INSERT INTO votes (time_cast, candidate)" " VALUES (:time_cast, :candidate)"
)
try:
    # Using a with statement ensures that the connection is always released
    # back into the pool at the end of statement (even if an error occurs)
    with db.connect() as conn:
        conn.execute(stmt, time_cast=time_cast, candidate=team)
except Exception as e:
    # If something goes wrong, handle the error in this section. This might
    # involve retrying or adjusting parameters depending on the situation.
    # ...

Java

// Using a try-with-resources statement ensures that the connection is always released back
// into the pool at the end of the statement (even if an error occurs)
try (Connection conn = pool.getConnection()) {

  // PreparedStatements can be more efficient and project against injections.
  String stmt = "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);";
  try (PreparedStatement voteStmt = conn.prepareStatement(stmt);) {
    voteStmt.setTimestamp(1, now);
    voteStmt.setString(2, team);

    // Finally, execute the statement. If it fails, an error will be thrown.
    voteStmt.execute();
  }
} catch (SQLException ex) {
  // If something goes wrong, handle the error in this section. This might involve retrying or
  // adjusting parameters depending on the situation.
  // ...
}

Node.js

try {
  const stmt = 'INSERT INTO votes (time_cast, candidate) VALUES (?, ?)';
  // Pool.query automatically checks out, uses, and releases a connection
  // back into the pool, ensuring it is always returned successfully.
  await pool.query(stmt, [timestamp, team]);
} catch (err) {
  // If something goes wrong, handle the error in this section. This might
  // involve retrying or adjusting parameters depending on the situation.
  // ...
}

C#

insertTimestamp = DateTime.UtcNow;
try
{
    using(var connection = new MySqlConnection(_connectionString.ConnectionString))
    {
        connection.OpenWithRetry();
        using (var insertVoteCommand = connection.CreateCommand())
        {
            insertVoteCommand.CommandText =
                @"INSERT INTO votes (candidate, time_cast) VALUES (@candidate, @time_cast)";
            var candidate = insertVoteCommand.CreateParameter();
            candidate.ParameterName = "@candidate";
            candidate.DbType = DbType.String;
            candidate.Value = team;
            insertVoteCommand.Parameters.Add(candidate);
            var timeCast = insertVoteCommand.CreateParameter();
            timeCast.ParameterName = "@time_cast";
            timeCast.DbType = DbType.DateTime;
            timeCast.Value = insertTimestamp;
            insertVoteCommand.Parameters.Add(timeCast);
            await insertVoteCommand.ExecuteNonQueryAsync();
        }
    }
    return Content($"Vote successfully cast for '{team}' at time {insertTimestamp}!");
}
catch (Exception ex)
{
    // If something goes wrong, handle the error in this
    // section. This might involve retrying or adjusting
    // parameters depending on the situation.
    return StatusCode((int)HttpStatusCode.InternalServerError, ex);
}

Go

sqlInsert := "INSERT INTO votes(candidate, created_at, updated_at) VALUES(?, NOW(), NOW())"
if team == "TABS" || team == "SPACES" {
	if _, err := app.db.Exec(sqlInsert, team); err != nil {
		fmt.Fprintf(w, "unable to save vote: %s", err)
		return fmt.Errorf("DB.Exec: %v", err)
	}
	fmt.Fprintf(w, "Vote successfully cast for %s!\n", team)
}
return nil

Ruby

@vote = Vote.new candidate: candidate

# ActiveRecord creates and executes your SQL and automatically
# handles the opening and closing of the database connection.
if @vote.save
  render json: "Vote successfully cast for \"#{@vote.candidate}\" at #{@vote.time_cast} PST!"
else
  render json: @vote.errors, status: :unprocessable_entity
end

PHP

// Use prepared statements to guard against SQL injection.
$sql = 'INSERT INTO votes (time_cast, candidate) VALUES (NOW(), :voteValue)';

try {
    $statement = $conn->prepare($sql);
    $statement->bindParam('voteValue', $value);

    $res = $statement->execute();
} catch (PDOException $e) {
    throw new RuntimeException(
        'Could not insert vote into database. The PDO exception was ' .
        $e->getMessage(),
        $e->getCode(),
        $e
    );
}

Nombre de connexions

Chaque connexion à la base de données emploie des ressources côté client et côté serveur. De plus, Cloud SQL impose des limites globales de connexions qui ne peuvent pas être dépassées. Créer et utiliser moins de connexions permet de réduire les coûts et de rester sous la limite de connexions.

Python

# Pool size is the maximum number of permanent connections to keep.
"pool_size": 5,
# Temporarily exceeds the set pool_size if no connections are available.
"max_overflow": 2,
# The total number of concurrent connections for your application will be
# a total of pool_size and max_overflow.

Java

// maximumPoolSize limits the total number of concurrent connections this pool will keep. Ideal
// values for this setting are highly variable on app design, infrastructure, and database.
config.setMaximumPoolSize(5);
// minimumIdle is the minimum number of idle connections Hikari maintains in the pool.
// Additional connections will be established to meet this value unless the pool is full.
config.setMinimumIdle(5);

Node.js

// 'connectionLimit' is the maximum number of connections the pool is allowed
// to keep at once.
connectionLimit: 5,

C#

// MaximumPoolSize sets maximum number of connections allowed in the pool.
connectionString.MaximumPoolSize = 5;
// MinimumPoolSize sets the minimum number of connections in the pool.
connectionString.MinimumPoolSize = 0;

Go


// Set maximum number of connections in idle connection pool.
dbPool.SetMaxIdleConns(5)

// Set maximum number of open connections to the database.
dbPool.SetMaxOpenConns(7)

Ruby

# 'pool' is the maximum number of permanent connections to keep.
pool: 5

PHP

PDO ne propose actuellement aucune fonctionnalité permettant de configurer des limites de connexion.

Intervalle exponentiel entre les tentatives

Si l'application tente sans succès de se connecter à la base de données, il se peut que cette dernière soit temporairement indisponible. Dans ce cas, l'envoi d'un trop grand nombre de requêtes de connexion simultanées risque de gaspiller des ressources de base de données supplémentaires et d'augmenter le temps nécessaire à la récupération. La mise en œuvre de l'intervalle exponentiel entre les tentatives empêche l'application d'envoyer un nombre peu élevé de requêtes de connexion lorsqu'elle ne peut pas se connecter à la base de données.

Une nouvelle tentative n'a alors de sens que lors de la première connexion, ou lors de la première obtention d'une connexion à partir du pool. Si des erreurs se produisent au milieu d'une transaction, l'application doit effectuer la nouvelle tentative, puis recommencer depuis le début d'une transaction. Ainsi, même si votre pool est correctement configuré, l'application peut toujours afficher des erreurs en cas de perte de connexion.

Python

# SQLAlchemy automatically uses delays between failed connection attempts,
# but provides no arguments for configuration.

Java

// Hikari automatically delays between failed connection attempts, eventually reaching a
// maximum delay of `connectionTimeout / 2` between attempts.

Node.js

// The mysql module automatically uses exponential delays between failed
// connection attempts.

C#

Policy
    .Handle<MySqlException>()
    .WaitAndRetry(new[]
    {
        TimeSpan.FromSeconds(1),
        TimeSpan.FromSeconds(2),
        TimeSpan.FromSeconds(5)
    })
    .Execute(() => connection.Open());

Go

Le package database/sql ne propose actuellement aucune fonctionnalité permettant de configurer l'intervalle exponentiel entre les tentatives.

Ruby

# ActiveRecord automatically uses delays between failed connection attempts,
# but provides no arguments for configuration.

PHP

PDO ne propose actuellement aucune fonctionnalité permettant de configurer un intervalle exponentiel entre les tentatives.

Délai de connexion

De nombreuses raisons peuvent expliquer l'échec d'une tentative de connexion. La communication réseau n'est jamais garantie, et la base de données peut être temporairement dans l'impossibilité de répondre. Assurez-vous que votre application gère de manière optimale les connexions interrompues ou infructueuses.

Python

# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
# new connection from the pool. After the specified amount of time, an
# exception will be thrown.
"pool_timeout": 30,  # 30 seconds

Java

// setConnectionTimeout is the maximum number of milliseconds to wait for a connection checkout.
// Any attempt to retrieve a connection from this pool that exceeds the set limit will throw an
// SQLException.
config.setConnectionTimeout(10000); // 10 seconds
// idleTimeout is the maximum amount of time a connection can sit in the pool. Connections that
// sit idle for this many milliseconds are retried if minimumIdle is exceeded.
config.setIdleTimeout(600000); // 10 minutes

Node.js

// 'connectTimeout' is the maximum number of milliseconds before a timeout
// occurs during the initial connection to the database.
connectTimeout: 10000, // 10 seconds
// 'acquireTimeout' is the maximum number of milliseconds to wait when
// checking out a connection from the pool before a timeout error occurs.
acquireTimeout: 10000, // 10 seconds
// 'waitForConnections' determines the pool's action when no connections are
// free. If true, the request will queued and a connection will be presented
// when ready. If false, the pool will call back with an error.
waitForConnections: true, // Default: true
// 'queueLimit' is the maximum number of requests for connections the pool
// will queue at once before returning an error. If 0, there is no limit.
queueLimit: 0, // Default: 0

C#

// ConnectionTimeout sets the time to wait (in seconds) while
// trying to establish a connection before terminating the attempt.
connectionString.ConnectionTimeout = 15;

Go

Le package database/sql ne propose actuellement aucune fonctionnalité permettant de configurer le délai avant expiration de la connexion. Le délai avant expiration est configuré au niveau du pilote.

Ruby

# 'timeout' is the maximum number of seconds to wait when retrieving a
# new connection from the pool. After the specified amount of time, an
# ActiveRecord::ConnectionTimeoutError will be raised.
timeout: 5000

PHP

// Here we set the connection timeout to five seconds and ask PDO to
// throw an exception if any errors occur.
$connConfig = [
    PDO::ATTR_TIMEOUT => 5,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];

Mettre fin à une connexion

Un utilisateur mysql disposant du privilège PROCESS dans Cloud SQL peut exécuter une instruction KILL sur une connexion d'un autre utilisateur mysql (à l'exception des administrateurs Cloud SQL).

Vous pouvez afficher les connexions à une instance à l'aide du client mysql, en exécutant la commande SHOW PROCESSLIST. Utilisez Id pour mettre fin à la connexion. Exemple :

mysql> SHOW PROCESSLIST;
mysql> KILL 6;

Durée de la connexion

Limiter la durée de vie d'une connexion peut aider à empêcher l'accumulation de connexions abandonnées. Pour ce faire, vous pouvez utiliser le pool de connexions.

Python

# 'pool_recycle' is the maximum number of seconds a connection can persist.
# Connections that live longer than the specified amount of time will be
# reestablished
"pool_recycle": 1800,  # 30 minutes

Java

// maxLifetime is the maximum possible lifetime of a connection in the pool. Connections that
// live longer than this many milliseconds will be closed and reestablished between uses. This
// value should be several minutes shorter than the database's timeout value to avoid unexpected
// terminations.
config.setMaxLifetime(1800000); // 30 minutes

Node.js

La bibliothèque Node.js mysql ne propose actuellement aucune fonctionnalité permettant de contrôler la durée d'une connexion.

C#

// ConnectionLifeTime sets the lifetime of a pooled connection
// (in seconds) that a connection lives before it is destroyed
// and recreated. Connections that are returned to the pool are
// destroyed if it's been more than the number of seconds
// specified by ConnectionLifeTime since the connection was
// created. The default value is zero (0) which means the
// connection always returns to pool.
connectionString.ConnectionLifeTime = 1800; // 30 minutes

Go


// Set Maximum time (in seconds) that a connection can remain open.
dbPool.SetConnMaxLifetime(1800)

Ruby

ActiveRecord ne propose actuellement aucune fonctionnalité permettant de contrôler la durée d'une connexion.

PHP

PDO ne propose actuellement aucune fonctionnalité permettant de contrôler la durée d'une connexion.

Pour voir l'application complète, cliquez sur le lien ci-dessous.

Python

Affichez l'application complète pour le langage de programmation Python.

Java

Affichez l'application complète pour le langage de programmation Java.

Node.js

Affichez l'application complète pour le langage de programmation Node.js.

C#

Affichez l'application complète pour le langage de programmation C#.

Go

Affichez l'application complète pour le langage de programmation Go.

Ruby

Affichez l'application complète pour le langage de programmation Ruby.

PHP

Affichez l'application complète pour le langage de programmation PHP.

Étape suivante