English
English
In PHP, connect_errno is a property provided by the MySQLi extension, used to retrieve the error code that occurs during a MySQL database connection. When we call mysqli_connect() or mysqli::__construct() to establish a connection, connect_errno is automatically assigned. If the connection is successful, connect_errno will be 0; if there is a connection error, it will return the corresponding error code.
<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
<p>if ($mysqli->connect_errno) {<br>
echo "Connection failed: " . $mysqli->connect_error;<br>
} else {<br>
echo "Successfully connected to the database!";<br>
}<br>
?><br>
In the code above, if the database connection is successful, $mysqli->connect_errno will be 0. If the connection fails, $mysqli->connect_errno will return a specific error code.
It’s important to note that connect_errno only provides information when a problem occurs during the connection process, such as:
Database server unreachable
Incorrect username or password
Database does not exist
However, connect_errno does not directly help us determine whether the database is experiencing high load. A heavily loaded database typically manifests as slow query response times, exhausted connection pools, or high usage of server resources like CPU and memory. These issues cannot be detected using connect_errno.
To monitor database load, we need to use other methods, such as:
MySQL provides several system variables and commands that can help monitor load conditions:
SHOW STATUS: This command displays MySQL’s runtime status, including number of connections, query count, and slow queries.
SHOW VARIABLES: Shows MySQL configuration parameters, including max connections and memory usage.
SHOW PROCESSLIST: Displays currently running queries, which helps identify long-running queries that may be causing high load.
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Questions';
SHOW PROCESSLIST;
These commands help us monitor real-time connection and query data, which in turn helps determine if the database is under heavy load.
Use professional database monitoring tools like Prometheus, Grafana, or Percona Monitoring and Management (PMM) to analyze database load more thoroughly. These tools provide detailed charts and alert features, allowing you to detect load issues promptly and take action quickly.
Enabling the slow query log can help identify queries that consume excessive time and may be contributing to high load. You can enable it with the following MySQL configuration:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Set queries longer than 2 seconds as slow queries
The slow query log helps you identify inefficient queries, which you can then optimize or restructure to reduce database load.
If monitoring reveals high database load, you can take the following actions to optimize and resolve the issue:
Analyze the slow query log to optimize complex and time-consuming SQL queries. Ensure efficient query statements, use proper indexing, avoid full table scans, and implement pagination to reduce data load per query.
If high load is due to connection exhaustion, consider increasing the size of the connection pool to reduce the overhead of establishing connections for each request.
Under high concurrency, implement read-write splitting to distribute read and write operations across different database servers, easing the load on a single server.
For very large databases, consider horizontal sharding to spread data across multiple database instances, further distributing the load.
