Transition from mysqli::debug to more powerful debugging tools (such as Xdebug + MySQL Slow Query Log)

In daily PHP development, mysqli::debug is usually used to simply view database connection process and execution details. It may be sufficient in the early stage of debugging, but when the project gradually becomes more complicated, relying solely on mysqli::debug often cannot meet the in-depth analysis needs of performance bottlenecks or complex queries. Therefore, this article will take you through how to smoothly transition from mysqli::debug to a more powerful debugging tool - Xdebug and MySQL slow query logs, thereby systematically improving database debugging efficiency.

1. Why is mysqli::debug not enough?

The function of mysqli::debug() is relatively simple. It only enables debug information output at the MySQL client library level, which is used to record low-level information such as connection and execution, but:

• SQL execution time is not provided ;

• Not easy to correspond to business logic ;

• The output content is messy and difficult to analyze ;

• The call stack or memory usage cannot be tracked automatically .

Sample code:

 mysqli::debug("d:t:o,/tmp/client.trace");
$db = new mysqli("localhost", "user", "password", "dbname");

Although it can be output to /tmp/client.trace , reading and analysis are inefficient.

2. Xdebug: Code-level debugging + performance analysis

Xdebug is one of PHP's most powerful debugging extensions, which can implement advanced functions such as breakpoint debugging, function call tracing, and performance analysis.

Install Xdebug (taking PHP 8 as an example):

 pecl install xdebug

Then add in php.ini :

 zend_extension=xdebug
xdebug.mode=develop,trace,profile
xdebug.output_dir=/tmp

Profiling

After turning on xdebug.mode=profile , each request will generate a .cachegrind file. You can use tools such as QCacheGrind or Webgrind to open the analysis function execution time, including database query functions.

 $mysqli = new mysqli("localhost", "user", "password", "dbname");
$result = $mysqli->query("SELECT * FROM users WHERE email LIKE '%@m66.net'");

You can clearly see the time-consuming, call location and other information of query() , which is very suitable for troubleshooting slow queries that are frequently executed.

3. MySQL slow query log: Focus on SQL execution efficiency

MySQL Slow Query Log is a database native function that records SQL statements whose execution time exceeds a specified threshold, which is ideal for analyzing database bottlenecks.

Enable slow query logs (my.cnf example):

 [mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

After setting up, restart MySQL service:

 sudo systemctl restart mysql

Recommended analysis tools:

• mysqldumpslow : Quickly view slow query distribution on the command line;

• pt-query-digest : Advanced analytical tools provided by Percona Toolkit;

• Web Tools : You can also upload logs to https://m66.net/analyze-log (for example, the tool page you built yourself) for visual analysis.

4. Best practices for combined use

1. Development stage : Use Xdebug to view call relationships and database calls;

2. Testing phase : Turn on Xdebug profiling mode for performance sampling;

3. Production stage : Turn on MySQL slow query logs and periodic analysis;