Xrhrft

This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again.

When it happens, I can use a client to login but when I run simple queries they run forever and don't return, or would eventually return but I dont know after how much time.

mysqld.log shows nothing important and when I kill my SQL query I get: 160712 7:41:36 [ERROR] /usr/libexec/mysqld: Sort aborted, which I think is normal.

Disk space is fine, plenty of space. CPUs are fine too.

Basic mysqlcheck on all databases all returned OK.

mysqltuner results are the following one hour after being restarted:

[OK] Currently running supported MySQL version 5.1.71-log

[OK] Operating on 64-bit architecture



-------- Storage Engine Statistics -------------------------------------------

[--] Status: +CSV +InnoDB +MRG_MYISAM

[--] Data in MyISAM tables: 4K (Tables: 2)

[--] Data in InnoDB tables: 327M (Tables: 222)

[!!] Total fragmented tables: 12



-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned



-------- Performance Metrics -------------------------------------------------

[--] Up for: 1h 8m 4s (12K q [2.990 qps], 2K conn, TX: 4M, RX: 842K)

[--] Reads / Writes: 55% / 45%

[--] Total buffers: 6.2G global + 14.2M per thread (500 max threads)

[OK] Maximum possible memory usage: 13.2G (84% of installed RAM)

[OK] Slow queries: 0% (0/12K)

[OK] Highest usage of available connections: 1% (8/500)

[OK] Key buffer size / total MyISAM indexes: 128.0M/102.0K

[!!] Key buffer hit rate: 81.2% (48 cached / 9 reads)

[OK] Query cache efficiency: 91.0% (4K cached / 5K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 139 sorts)

[!!] Joins performed without indexes: 16

[OK] Temporary tables created on disk: 0% (0 on disk / 9 total)

[OK] Thread cache hit rate: 99% (8 created / 2K connections)

[OK] Table cache hit rate: 97% (230 open / 237 opened)

[OK] Open file limit used: 0% (25/65K)

[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)

[OK] InnoDB buffer pool / data size: 6.0G/327.7M

[OK] InnoDB log waits: 0

-------- Recommendations -----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    MySQL started within last 24 hours - recommendations may be inaccurate

    Adjust your join queries to always utilize indexes

Variables to adjust:

    join_buffer_size (> 2.0M, or always use indexes with joins)

I see some tables are fragmented, is it something I should be checking?
What else should I be doing/looking for?

Thank you in advance

You are hardly using MyISAM, so ignore the issues about MyISAM.

Ignore the recommendation to OPTIMIZE and 'fragmented'; that tool always says that, even if it is of no use (which is usually the case).

Joins without indexes -- you are better off looking at any 'slow' queries.

Find the slow queries. First try SHOW FULL PROCESSLIST when it "hangs" to see if you can catch a query. Another thing to do is to turn on the Slowlog, run for a day, then use pt-query-digest to summarize the slowlog. Let's look at the first couple of queries.

"Sort aborted" sounds like a poorly written query; the PROCESSLIST should show it before killing it. Perhaps it is a JOIN without an ON.