PHP database connection monitoring: track connection usage and performance

#PHP Database Connection Monitoring: Tracking Connection Usage and Performance

Connection monitoring is critical for optimizing database performance and ensuring application stability. This article explains how to use PHP to track and monitor the usage and performance of database connections.

Install the database extension

Before you begin, you need to install the database extension. The most commonly used extensions are MySQLi and PDO. Here's how to install both extensions:

MySQLi:

sudo apt-get install php-mysqli

PDO:

sudo apt-get install php-pdo

Connect to Database

First, you need to connect to the database. You can use the following code to establish a MySQLi connection:

$mysqli = new mysqli("host", "user", "password", "database");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

For PDO, you can use the following code:

$dsn = 'mysql:host=host;dbname=database';
$user = 'user';
$password = 'password';

try {
    $conn = new PDO($dsn, $user, $password);
} catch (\PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Monitor the connection using

You can use PHP's built-in mysqli_get_connection_stats() and mysqli_get_links_stats() functions obtain information about connection usage. The following is a sample code that displays the number of connections to a MySQL database:

$connection_info = mysqli_get_connection_stats($mysqli);

echo "Number of open connections: " . $connection_info['connection_count'] . "\n";

For PDO, you can use the following code:

$stats = $conn->query("SHOW VARIABLES LIKE 'Connections'");
echo "Number of open connections: " . $stats->rowCount() . "\n";

Monitor connection performance

Connection performance can be monitored by measuring query execution time. You can use PHP's built-in microtime(true) function:

$start = microtime(true);
$query = "SELECT * FROM table";
$result = $mysqli->query($query);
$end = microtime(true);
echo "Query execution time: " . ($end - $start) . " seconds\n";

For PDO, you can use the following code:

$start = microtime(true);
$statement = $conn->prepare($query);
$statement->execute();
$end = microtime(true);
echo "Query execution time: " . ($end - $start) . " seconds\n";

Practical case: automatically close idle connections

PHP connection monitoring can be used to automatically close idle database connections. The following is a sample code using the mysqli_reap_async_connections() function:

mysqli_reap_async_connections($mysqli);

// Repeat the reaping every 60 seconds
sleep(60);

For PDO, you can use the following code:

$conn->close();

// Repeat the closing every 60 seconds
sleep(60);

The above is the detailed content of PHP database connection monitoring: track connection usage and performance. For more information, please follow other related articles on the PHP Chinese website!