English
English
In daily PHP development, database performance problems are often the most troublesome, especially those intermittent timeout problems, which often make it difficult for developers to quickly locate. Fortunately, PHP's mysqli extension provides a powerful tool - mysqli::debug , which can help us record detailed database operation logs, thereby deeply analyzing slow query or connection problems and quickly finding performance bottlenecks.
mysqli::debug is a method in the mysqli class that enables debug logging. This method records related MySQL client operations into a log file, helping to troubleshoot exceptions or performance issues during connection, SQL execution.
mysqli::debug(string $debug_options): bool
This method is usually called before a database connection and requires that the mysqlnd driver is enabled at PHP compile time and that allows debugging is configured.
To use mysqli::debug , you need to make sure:
PHP uses mysqlnd driver <br> You can confirm by looking at phpinfo() :
phpinfo();
Search for mysqlnd , if you see the following words, it means you have enabled:
Client API library version => mysqlnd 8.x.x
Modify php.ini configuration to enable debugging
mysqlnd.debug = "/tmp/mysqlnd.log"
After modification, restart the PHP-FPM or Apache service.
In your PHP script, call mysqli::debug() and then create the database connection and execute the query normally. For example:
<?php
// Enable debug logging
mysqli::debug("d:t:o,/tmp/mysqlnd.log");
// Database connection
$mysqli = new mysqli("localhost", "db_user", "db_pass", "db_name");
if ($mysqli->connect_errno) {
echo "Connection failed: " . $mysqli->connect_error;
exit();
}
// Execute a query
$result = $mysqli->query("SELECT * FROM users WHERE status = 'active'");
while ($row = $result->fetch_assoc()) {
echo $row['username'] . "<br>";
}
$mysqli->close();
?>
in:
dEnable debugging
tAdd timestamp
o,/tmp/mysqlnd.log specifies the output file
The debug log will be output to the location you specified (such as /tmp/mysqlnd.log ), and you can view the content using less , tail , or any text editor.
tail -f /tmp/mysqlnd.log
Common analysis points include:
Is the connection time consuming?
Whether the query statement is executed repeatedly
Query if there is a cache hit
Slow query takes time
With the EXPLAIN analysis of SQL statements, you can more accurately determine whether you need to optimize indexes or avoid full table scanning.
Although mysqli::debug provides rich client information, deeper slow query analysis also requires MySQL's own slow query log:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
The slow query log combined with the client log of mysqli::debug can analyze it in collaboration from both ends to quickly locate bottlenecks.
For performance and security reasons, debugging should be used in development or testing environments. Remember to close it after debugging:
mysqli::debug(""); // Clear the debugging parameters equal to shutdown
Through mysqli::debug , we can accurately record and analyze the interaction process between PHP scripts and MySQL, which is of great value for handling database connection problems, intermittent timeouts, and slow query analysis. Making good use of this tool during the development stage can greatly reduce the probability of "stepping on the mine" after it is launched.
Related Tags:
mysqli
