How to optimize muscle?

Question

MySQL

I have such a problem with the muscle, since during peak hours the muscle process devours the entire percentage, after reading about optimization, I found mysqltunner.pl, after doing what it asks several times (raise some parameters), they didn’t bring any result (yes, the muscle rebooted after execution) after waiting a few more days I ran the script again and he asked me to raise it even higher, I raised it, and as a result, my entire server worked only for the muscle, which was not enough and the errors continued, I returned everything before the change, waited a few days, here is the log

>> MySQLTuner 1.3.0 - Major Hayden
>> Bug reports, feature requests, and downloads at mysqltuner.com
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.43-0+deb7u1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics ------------- ------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[- -] Data in MyISAM tables: 838M (Tables: 1133)
[--] Data in InnoDB tables: 277M (Tables: 483)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 124K (Tables: 4)
[!!] Total fragmented tables: 484
-------- Security Recommendations ------------------------------- ------------
[OK] All database users have passwords assigned
-------- Performance Metrics ------------------- ------------------------------
[--] Up for: 49d 18h ​​57m 36s (77M q [18.013 qps], 5M conn, TX: 6805B, RX: 19B)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 1.3G global + 34.6M per thread (400 max threads)
[OK] Maximum possible memory usage : 14.8G (63% of installed RAM)
[OK] Slow queries: 0% (7K/77M)
[OK] Highest usage of available connections: 38% (154/400)
[OK] Key buffer size / total MyISAM indexes: 256.0M/225.6M
[OK] Key buffer hit rate: 99.9% (15B cached / 9M reads)
[OK] Query cache efficiency: 29.6% (17M cached / 57M selects)
[!!] Query cache prunes per day: 91403
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 21M sorts)
[!!] Joins performed without indexes: 20729886
[OK] Temporary tables created on disk: 17% (3M on disk / 22M total)
[OK] Thread cache hit rate: 99% (154 created / 5M connections)
[!!] Table cache hit rate: 0% (1K open / 3M opened)
[OK] Open file limit used: 69% (1K/2K)
[OK] Table locks acquired immediately: 99% (72M immediate / 73M locks)
[OK] InnoDB buffer pool / data size : 512.0M/277.7M
[OK] InnoDB log waits: 0
-------- Recommendations --------------------------------- --------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: bit.ly/1mi7c4C
Variables to adjust :
query_cache_size (> 256M) [see warning above]
join_buffer_size (> 32.0M, or always use indexes with joins)
table_open_cache (> 1024)

Advise what exactly needs to be upgraded and how much
Server
AMD Opteron™, 8 Cores / 24 GB DDR3-RAM / 2x2000GB SATA
MySQL Version: 5.5.43-0+deb7u1-log MySQLi

Answer 1

Have you tried optimizing queries? to build indexes there? check for slow requests? suddenly you have indexes there (if any), do not work?
habrahabr.ru/post/31072

Answer 2

[OK] Query cache efficiency: 29.6% (17M cached / 57M selects)
[!!] Query cache prunes per day: 91403

- offhand. the query cache is not efficient. Probably a lot of updates in the tables.
only 29.6% of requests get into the cache, but 91K cache flushes per day.
Well, indexes - every third request without an index.
total Query cache efficiency: 29.6% (17M cached / 57M selects) - 57M queries without
indexes Joins performed without indexes: 20 729 886

[--] Data in MyISAM tables: 838M (Tables: 1133)
[--] Data in InnoDB tables: 277M (Tables: 483)

and the amount of memory everything should fly - everything fits into memory several times.

Answer 3

Every fourth request is a join without an index. Index the fields over which joins are produced. In order not to guess, you can use pt-query-digest.