Как оптимизировать MySQL? Повысить cache efficiency и уменьшить maximum possible memory usage?

Question

[German Zvonchuk]

Доброго времени суток.

Буду очень признателен вам, если поможете мне разобраться с конфигом MySQL.

[!!] Maximum possible memory usage: 67.4G (1744% of installed RAM)
На данный момент join_buffer_size = 256M, я не знаю, хорошо это или плохо, и не знаю, как мне выяснить, хватает ли этих 256M для каждого запроса, или это чрезмерно много?

max_connections = 256, кол-во максимальный соединений, я думаю, можно спокойно уменьшать до 64, ведь по статистике за 9 дней больше 47 соединений не было.

[!!] Joins performed without indexes: 142242
На данный момент я включил логирование запросов в которых не используются INDEX-ы. Есть ли какое-нибудь приложение, которое сможет проанализировать лог и выдать самый популярный запрос в котором не используется INDEX.

[!!] Query cache efficiency: 9.5% (703K cached / 7M selects)
На данный момент query_cache_limit — максимальный размер кэшируемого запроса равен 256M.
Получается, что в моем случае это мало? Нужно увеличивать?

/etc/my.cnf

read_buffer_size 128KB
sort_buffer_size 2M
read_rnd_buffer_size 256KB

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 117M (Tables: 22)
[--] Data in InnoDB tables: 1G (Tables: 277)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 29

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 0h 12m 32s (6M q [7.834 qps], 431K conn, TX: 13B, RX: 1B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 2.7G global + 258.6M per thread (256 max threads)
[!!] Maximum possible memory usage: 67.4G (1744% of installed RAM)
[OK] Slow queries: 0% (2K/6M)
[OK] Highest usage of available connections: 18% (47/256)
[OK] Key buffer size / total MyISAM indexes: 8.0M/23.0M
[OK] Key buffer hit rate: 100.0% (10M cached / 2K reads)
[!!] Query cache efficiency: 9.5% (703K cached / 7M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (214 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 142242
[OK] Temporary tables created on disk: 5% (36K on disk / 663K total)
[OK] Thread cache hit rate: 99% (47 created / 431K connections)
[!!] Table cache hit rate: 3% (400 open / 10K opened)
[OK] Open file limit used: 6% (81/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 1.3G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 256M, or use smaller result sets)
    join_buffer_size (> 256.0M, or always use indexes with joins)
    table_cache (> 400)

Answer 1

[neol]

join_buffer_size = 256M

Это сильно много. Нормально будет 512K, ну может 1М.

max_connections = 256, кол-во максимальный соединений я думаю можно спокойно уменьшать до 64, ведь по статистике за 9 дней больше 47 соединений не было.

Верно.

Есть ли какое-нибудь приложение которое сможет проанализировать лог и выдать самый популярный запрос в котором не используется INDEX.

mysqldumpslow

На данный момент query_cache_limit — максимальный размер кэшируемого запроса равен 256M.
Получается что в моем случае это мало? Нужно увеличивать?

Нет. Лучше наоборот уменьшить до 1-2М ( кешировать огромные выборки силами mysql не стоит ). А причину промахов стоит поискать в запросах. В документации есть список функций, запросы с которыми не кешируются.