3

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!

koubic
  • 131
  • 1
  • 1
  • 5
  • 1
    Joins performed without indexes: 403 => this is a problem, also 79000 temporary tables created on disk (too big for ram), same for your table_cache. Now for the aboterd connections, show us your php code managing connection closing (and opening), and extract the max_allowed_packet and connect_timeout mysql settings values. – regilero Jan 15 '14 at 18:29
  • I've got 4 GB of RAM on the server, so how to avoid temporary tables created on disk? I added PHP code for opening connection. I never close PDO connection because it should automaticaly close when script ends. max_allowed_packet = 16777216, connect_timeout = 10. Could the aborted connections be caused by scripts taking longer than 10 seconds? – koubic Jan 15 '14 at 20:16
  • This question was about connections, now from the mysqltuner report you can google for table cache settings, but, as always, start by indexation of your database, query with joins not using indexes means you absolutely need to fix the queries OR add indexes. This will maybe remove all the other problems (like temporary tables, that may be related) – regilero Jan 16 '14 at 08:37

1 Answers1

4

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:

  1. PHP script starts
  2. MySQL connection
  3. MySQL query execution
  4. PHP code execution
  5. 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.

wizurd
  • 103
  • 3
jeffatrackaid
  • 4,112
  • 18
  • 22
  • Thanks for your answer. I access the database practically for the whole lifetime of the script execution. 99,99% of PHP script executions take under 200 ms. If I closed the connection just before the script ends (after printing HTML footer), do you think it would help? I've got about 5000 pageviews daily. – koubic Jan 15 '14 at 20:33
  • 5000 page views daily should not cause any problems with most Apps unless you are on underpowered hardware. If you are trying to optimize performance, I would start by profiling your PHP code so you know where the bottleneck resides. If you do find it is in the MySQL execution phase, then tuning MySQL variables may help. Aborted errors alone do not mean a performance issue. – jeffatrackaid Jan 15 '14 at 20:37
  • Thanks. I profiled my PHP code and it's MySQL queries on certain pages that cause problems. These queries make JOINs accross 5 tables, ORDER BY, sometimes UNION etc. The problem definitely lies in tuning MySQL variables, because the same query sometimes takes 1 ms, sometimes over 1 second. So I guess there's not enough cache. As you say, I won't bother with the Aborted connections issue. Thanks. – koubic Jan 15 '14 at 21:29
  • Note that some queries will always create temporary tables on disk regardless of cache sizes. One trick is to set tmpdir=/dev/shm. Just make sure you have enough RAM. – jeffatrackaid Jan 16 '14 at 14:26