Does mysqli::debug affect performance? Actual test data sharing

M66 2025-05-31

Mysqli::debug() is a very useful tool when debugging database connection problems or analyzing slow queries. But will it affect application performance? This is a question that developers often care about. This article will conduct a quantitative analysis of the performance impact of mysqli::debug() through actual PHP test code.

What is mysqli::debug

mysqli::debug(string $debug_options) is a debugging function provided by PHP's MySQLi extension. It can be used to enable client debugging output and write to log files. This debugging information is often very useful for development and troubleshooting, but may also affect execution efficiency due to disk writes and additional log generation.

Test environment

PHP Version: 8.2
Database: MySQL 8.0
Operating system: Ubuntu 22.04
Test method: Use the same SQL query, and execute 10,000 times with mysqli::debug enabled and not enabled respectively, which is relatively time-consuming.

Test code

 <?php
$host = 'localhost';
$user = 'root';
$password = 'your_password';
$dbname = 'test_db';

// Test function
function test_query_performance($use_debug = false) {
    global $host, $user, $password, $dbname;

    if ($use_debug) {
        mysqli::debug("d:t:o,/tmp/client.trace");
    }

    $mysqli = new mysqli($host, $user, $password, $dbname);

    if ($mysqli->connect_error) {
        die("Connection failed: " . $mysqli->connect_error);
    }

    $start = microtime(true);
    for ($i = 0; $i < 10000; $i++) {
        $result = $mysqli->query("SELECT 1");
        if (!$result) {
            die("Query failed: " . $mysqli->error);
        }
    }
    $end = microtime(true);

    $mysqli->close();
    return $end - $start;
}

// Test and output results
$time_without_debug = test_query_performance(false);
echo "Not enabled mysqli::debug Time-consuming: " . $time_without_debug . " Second\n";

$time_with_debug = test_query_performance(true);
echo "Open mysqli::debug Time-consuming: " . $time_with_debug . " Second\n";

echo "Performance differences: " . ($time_with_debug - $time_without_debug) . " Second\n";
?>

Example of test results (may be different in different environments)

 Not enabled mysqli::debug Time-consuming: 0.75 Second
Open mysqli::debug Time-consuming: 2.93 Second
Performance differences: 2.18 Second

analyze

Judging from the test results, mysqli::debug will significantly increase the time-consuming of the query. In 10,000 queries, the debugging function adds an additional overhead of about 2 seconds, indicating that its impact on performance cannot be ignored. The main reason is that after debug is enabled, each database operation will be recorded in the log file, involving I/O operations, which increases the system burden.

suggestion

Use only during the debugging phase : mysqli::debug should be turned off in production environments to avoid unnecessary performance overhead.
Conditional logs for usage : If you need to record logs, it is recommended to use the application layer log system to record specific exceptions or slow queries.
Automatic monitoring alternative debug : You can consider using professional tools such as https://m66.net/monitoring for database monitoring and analysis.

in conclusion

mysqli::debug is a powerful debugging tool, but it can also have a significant impact on performance. It should be used with caution according to the actual scenario to avoid turning on by default in production environments.

mysqli::debug