データベース接続を管理する

このページでは、Cloud SQL データベース接続を効果的に使用するアプリケーションの作成におすすめの方法と言語に固有のコードサンプルについて説明します。

これらのサンプルは、GitHub で入手できる完全な App Engine アプリケーションからの抜粋です。詳細については、こちらをご覧ください。

接続プール

接続プールは、接続レイテンシとパフォーマンスを向上させるために共有され、再利用されるデータベース接続のキャッシュです。アプリケーションでデータベース接続が必要な場合は、プールから一時的に 1 つを借ります。アプリケーションでの接続が終了すると、接続がプールに戻されます。アプリケーションで次にデータベース接続が必要とされるときに再利用できます。

Python

# The SQLAlchemy engine will help manage interactions, including automatically
# managing a pool of connections to your database
db = sqlalchemy.create_engine(
    # Equivalent URL:
    # mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=/cloudsql/<cloud_sql_instance_name>
    sqlalchemy.engine.url.URL(
        drivername='mysql+pymysql',
        username=db_user,
        password=db_pass,
        database=db_name,
        query={
            'unix_socket': '/cloudsql/{}'.format(cloud_sql_connection_name)
        }
    ),
    # ... Specify additional properties here.
    # ...
)

Java

// The configuration object specifies behaviors for the connection pool.
HikariConfig config = new HikariConfig();

// Configure which instance and what database user to connect with.
config.setJdbcUrl(String.format("jdbc:mysql:///%s", DB_NAME));
config.setUsername(DB_USER); // e.g. "root", "postgres"
config.setPassword(DB_PASS); // e.g. "my-password"

// For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections.
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.
config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);
config.addDataSourceProperty("useSSL", "false");

// ... Specify additional connection properties here.
// ...

// Initialize the connection pool using the configuration object.
DataSource pool = new HikariDataSource(config);

Node.js

let pool;
const createPool = async () => {
  pool = await mysql.createPool({
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    // If connecting via unix domain socket, specify the path
    socketPath: `/cloudsql/${process.env.CLOUD_SQL_CONNECTION_NAME}`,
    // If connecting via TCP, enter the IP and port instead
    // host: 'localhost',
    // port: 3306,

    //...
  });
};
createPool();

C#

var connectionString = new MySqlConnectionStringBuilder(
    Configuration["CloudSql:ConnectionString"])
// ConnectionString set in appsetings.json formatted as:
// "Uid=aspnetuser;Pwd=;Host=cloudsql;Database=votes"
{
    // Connecting to a local proxy that does not support ssl.
    SslMode = MySqlSslMode.None,
};
connectionString.Pooling = true;
// ...
DbConnection connection =
    new MySqlConnection(connectionString.ConnectionString);

接続の開閉

接続プールを使用する場合は、使用後に接続が必ずプールに返されるように、接続の開閉を適切に行う必要があります。返されなかった接続(「リーク」した接続)は再利用されないため、リソースが無駄に消費され、アプリケーションのパフォーマンスのボトルネックになることがあります。

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.
  PreparedStatement voteStmt = conn.prepareStatement(
      "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);");
  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.Now;
try
{
    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);
}

接続数

すべてのデータベース接続で、クライアント側とサーバー側のリソースが使用されます。さらに、Cloud SQL では、超えてはならない接続総数の制限が課されます。作成して使用する接続の数を少なくすると、オーバーヘッドが減り、接続上限を超えずに済みます。

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;

指数バックオフ

アプリケーションでデータベースに接続しようとしたものの、成功しない場合、データベースが一時的に使用できなくなる可能性があります。この場合、同時接続リクエスト数が多すぎると、追加のデータベース リソースが浪費され、復元に必要な時間が長くなります。指数バックオフを使用すると、アプリケーションでデータベースに接続できないときに、不適切な数の接続リクエストを送信できなくなります。

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#

var connection = Policy
    .HandleResult<DbConnection>(conn => conn.State != ConnectionState.Open)
    .WaitAndRetry(new[]
    {
        TimeSpan.FromSeconds(1),
        TimeSpan.FromSeconds(2),
        TimeSpan.FromSeconds(5)
    }, (result, timeSpan, retryCount, context) =>
    {
        // Log any warnings here.
    })
    .Execute(() => NewMysqlConnection());

接続タイムアウト

接続の試行は、さまざまな理由で失敗することがあります。ネットワーク通信が保証されることはなく、データベースが一時的に応答しなくなる可能性があります。アプリケーションで、切断された接続や成功しなかった接続を正常に処理する必要があります。

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;

接続の存続期間

接続の存続期間を制限することで、放棄された接続が蓄積しないようにできます。接続プールを使用すると、接続の存続期間を制限できます。

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

現在、mysql Node.js ライブラリは接続期間の制御機能を提供していません。

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

完全なアプリケーションを表示するには、下のリンクをクリックしてください。

Python

Python プログラミング言語用の完全なアプリケーションを表示します。

Java

Java プログラミング言語用の完全なアプリケーションを表示します。

Node.js

Node.js プログラミング言語用の完全なアプリケーションを表示します。

C#

C# プログラミング言語用の完全なアプリケーションを表示します。

次のステップ