MySQL connections aborted - should I be worried?

Question

I'm tuning MySQL on a webserver with Apache and mod_php for speed so I ran mysqltuner and here's the result:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 255M (Tables: 120)
[--] Data in InnoDB tables: 5M (Tables: 2)
[!!] Total fragmented tables: 14

-------- Security Recommendations  -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user 'lovecpokladu'@'localhost' for table 'user'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9h 37m 22s (6M q [185.754 qps], 286K conn, TX: 72B, RX: 767M)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (11% of installed RAM)
[OK] Slow queries: 0% (105/6M)
[OK] Highest usage of available connections: 33% (50/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/48.9M
[OK] Key buffer hit rate: 99.9% (378M cached / 497K reads)
[OK] Query cache efficiency: 83.2% (4M cached / 5M selects)
[!!] Query cache prunes per day: 84442
[OK] Sorts requiring temporary tables: 6% (10K temp sorts / 159K sorts)
[!!] Joins performed without indexes: 403
[OK] Temporary tables created on disk: 25% (79K on disk / 312K total)
[OK] Thread cache hit rate: 99% (936 created / 286K connections)
[!!] Table cache hit rate: 0% (64 open / 102K opened)
[OK] Open file limit used: 10% (106/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] Connections aborted: 49%
[OK] InnoDB data size / buffer pool: 5.6M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 64)

My PDO init script:

$pdo = new PDO("mysql:host=localhost;dbname=...;charset=utf8", $user, $pass, array("SET NAMES utf8"));
$pdo->exec("SET CHARACTER SET utf8");
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

It says [!!] Connections aborted: 49%, should I be worried? I use PDO for non-persistent connecting to database from PHP. The connection should be closed at the end of the script so I don't get the reason for such high connection aborts. Or even if it actually matters.

Thanks a lot!

score 4 · Answer 1 · edited Jul 14 '16 at 23:00

If the mysql client (in this case your PHP script) fails to execute a mysql_close before exiting, MySQL will log an aborted connection and increase the aborted clients status variable.

From the MySQL Documentation: Communication Errors and Aborted Connections

If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log. The cause can be any of the following:
The client program did not call mysql_close() before exiting.

The client had been sleeping more than wait_timeout or    
interactive_timeout seconds without issuing any requests to the
server. See Section 5.1.4, “Server System Variables”.

I'm not a PHP programmer, but I know from working on the sysadmin side that if you don't close your MySQL connections properly, you can see these messages in the MySQL logs.

Also from the documentation: Connections and Connection management

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

Close Connections ASAP

In practice, I recommend explicitly closing MySQL connections as soon as you're done with them.

Imagine under high load conditions where you have:

PHP script starts
MySQL connection
MySQL query execution
PHP code execution
PHP script exits

In this scenario, if the PHP code execution takes 5s due to high loads, the MySQL connection is left open. Multiple this by 1000's of requests/sec and you easily exhaust your MySQL connections. By closing the connection explicitly, you can help avoid this and other issues.

MySQL connections aborted - should I be worried?

1 Answers1