Как оптимизировать мускул?

Question

[Алексей Тутубалин]

У меня такая проблема с мускулом так как в часы пик процесс мускула сжирает весь проц, почитав про оптимизацию выискал mysqltunner.pl выполнив несколько раз то что он просит(поднять некоторые параметры) результата не принесли(да после выполнения мускул перезагружался) подождав ещё несколько дней опять запускал скрипт и он просил ещё выше поднять я поднимал и в результате у меня весь сервак работал только на мускул которому и этого мало было и продолжались ошибки я вернул всё до изменения подождал несколько дней вот лог

>> 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)

Посоветуйте что нужно именно поднимать и на сколько
Сервер
AMD Opteron™, 8 Cores / 24 GB DDR3-RAM / 2x2000GB SATA
Версия MySQL: 5.5.43-0+deb7u1-log MySQLi

Answer 1

[Алексей Черемисин]

А запросы оптимизировать не пробовали? индексы там строить? проверить на медленные запросы? вдруг у вас там индексы (если они есть), не работают?
habrahabr.ru/post/31072

Answer 2

[Макс]

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

- на вскидку. кеш запросов не эффективен. Наверное много апдейтов в таблицах.
в кеш попадает всего 29,6% запросов, а вот очисток кеша за день 91К.

ну и индексы - каждый третий запрос без индекса.
всего Query cache efficiency: 29.6% (17M cached / 57M selects) - 57 млн запросов
из них без индексов Joins performed without indexes: 20 729 886 - почти 21 млн.
вообще при Ваших объемах

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

и объеме памяти все должно летать - все влезает в память несколько раз.

Answer 3

[Александр Мелеховец]

Каждый четвёртый запрос - джоин без индекса. Проиндексируйте поля, по которым производятся джоины. Чтоб не гадать, можно заюзать pt-query-digest.