Оптимизация mysql. Какие рекомендации mysqltuner стоит применить?

Question

[boroda32]

Добрый день.
Проект находитя на VPS(10 GB RAM, 2 CPU core, 65 GB SSD).
Поставил mysqltuner и получил рекомендации по настройке mysql.
Хочу уточнить, какие из этих рекомендаций нужно применить для получения производительности.
И какие значения поставить mysql параметрам.

Текущие настройки Mysql

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
log-error=/var/log/mysql.log
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
local-infile=0
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

log-error = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
innodb-buffer-pool-instances = 2
innodb-buffer-pool-size = 2361393152
innodb-flush-log-at-trx-commit = 2
innodb-log-file-size = 524288000
innodb-thread-sleep-delay = 0
join-buffer-size = 8388608
max-connections = 100
max-heap-table-size = 268435456
query-cache-limit = 2097152
query-cache-size = 67108864
query-cache-type = ON
skip-name-resolve = TRUE
sort-buffer-size = 8388608
symbolic-links = FALSE
tmp-table-size = 268435456

Рекомендации Mysqltuner

>> 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.6.35
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 2G (Tables: 326)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MyISAM tables: 46M (Tables: 5)
[!!] Total fragmented tables: 20

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 39d 21h 6m 2s (587M q [170.642 qps], 16M conn, TX: 1078B, RX: 358B)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 2.5G global + 16.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 4.2G (41% of installed RAM)
[OK] Slow queries: 0% (1K/587M)
[OK] Highest usage of available connections: 40% (40/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/32.6M
[OK] Key buffer hit rate: 99.5% (136M cached / 677K reads)
[OK] Query cache efficiency: 81.7% (395M cached / 484M selects)
[!!] Query cache prunes per day: 1080995
[OK] Sorts requiring temporary tables: 0% (473 temp sorts / 27M sorts)
[!!] Joins performed without indexes: 22423559
[!!] Temporary tables created on disk: 33% (8M on disk / 25M total)
[OK] Thread cache hit rate: 99% (158K created / 16M connections)
[OK] Table cache hit rate: 79% (985 open / 1K opened)
[OK] Open file limit used: 0% (62/16K)
[OK] Table locks acquired immediately: 99% (390M immediate / 390M locks)
[OK] InnoDB buffer pool / data size: 2.2G/2.2G
[!!] InnoDB log waits: 5
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 64M)
join_buffer_size (> 8.0M, or always use indexes with joins)
innodb_log_buffer_size (>= 8M)

Answer 1

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

Вообще-то тут у вас дело больше не в настройках мускула, а с организацией данных самой базы данных и вашими запросами!

1)

!!] Joins performed without indexes: 22423559

Это говорит о том, что у вас есть запросы с join, при которых не работают индексы. Создайте нужные индексы и будет щазтие.

2)

Reduce your SELECT DISTINCT queries without LIMIT clauses

Не забывайте указывать в запросах LIMIT, особенно, когда делаете DISTINCT.

3) ну и уменьшите размер временных таблиц, или запросы, которые их создают.

Comments

[Станислав Бодро́в]

Вообще-то тут у вас дело больше не в настройках мускула, а с самой базой данных и вашими запросами!

Золотые слова!

Answer 2

[Reversaidx]

innodb_log_buffer_size
query_cache_size можно вообще отключить т.к % записей большой(по ситуации смотреть)
join_buffer_size лучше не повышать