mysql - 调优mysql。 Mysqltuner 推荐

Question

下午好。该站点位于 VPS(10 GB RAM、2 个 CPU 内核、65 GB SSD)上。我想提高网站的速度。但是mysql的所有参数我都不明白。我写出了当前的 mysql 设置，以及 mysqltuner 的建议。在这个项目中，我们不分配资源用于代码中的分析和修改。我想通过设置获得速度增益。如果可能的话。谢谢。

当前的 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/
[mysqld]
key_buffer_size = 8388608
innodb_buffer_pool_size = 368M
query_cache_size = 32M
max_heap_table_size = 147M
tmp_table_size = 147M
join_buffer_size = 1887K
innodb_flush_log_at_trx_commit = 2
explicit_defaults_for_timestamp = 0
sql_mode=
query_cache_type = 1

bind-address = 127.0.0.1

Mysqltuner 引用

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://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.40
[OK] Operating on 64-bit architecture

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

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 37d 5h 54m 55s (190M q [59.283 qps], 3M conn, TX: 861B, RX: 188B)
[--] Reads / Writes: 52% / 48%
[--] Total buffers: 571.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 981.4M (9% of installed RAM)
[OK] Slow queries: 0% (846/190M)
[OK] Highest usage of available connections: 43% (66/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/4.5M
[OK] Key buffer hit rate: 100.0% (5B cached / 4K reads)
[OK] Query cache efficiency: 81.3% (120M cached / 148M selects)
[!!] Query cache prunes per day: 305162
[OK] Sorts requiring temporary tables: 0% (43K temp sorts / 6M sorts)
[!!] Joins performed without indexes: 65951
[!!] Temporary tables created on disk: 42% (4M on disk / 9M total)
[OK] Thread cache hit rate: 99% (3K created / 3M connections)
[OK] Table cache hit rate: 53% (1K open / 3K opened)
[OK] Open file limit used: 0% (61/16K)
[OK] Table locks acquired immediately: 99% (116M immediate / 116M locks)
[!!] InnoDB  buffer pool / data size: 368.0M/7.6G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 1.8M, or always use indexes with joins)
    tmp_table_size (> 147M)
    max_heap_table_size (> 147M)
    innodb_buffer_pool_size (>= 7G)

Answer 1

关于 my.cnf-ini [mysqld] 部分的建议(有些将是您的 my.cnf-ini 的新内容)

join_buffer_size=1887K # 以#开头

query_cache_min_res_unit=512  # from 4096 to reduce QC prunes per day
tmp_table_size=200M  # from 147M to reduce 42% on disk
max_heap_table_size=200M  # from 147M to keep size matched to tmp_table_size
innodb_buffer_pool_size=6G  # from 368M for 60% of RAM in VPS environment
innodb_buffer_pool_instances=6  # from 1 to reduce mutex contention
max_connections=100  # from 151 since only 66 max_used_connections in 37 days
thread_cache_size=100  # from autocalc to reduce threads_created of 3K
table_open_cache=3000  # from 1000 to avoid overhead of tables_opened
innodb_purge_threads=6  # from 1 to match # ibp instances
innodb_io_capacity_max=20000  # from 2000 to use your SSD capacity
innodb_io_capacity=10000  # from 200 to use your SSD capacity
innodb_flush_neighbors=0  # from 1  to conserve CPU, there is no rotational delay on SSD
innodb_lru_scan_depth=100  # from 1024 to save CPU cycles every SECOND

.如果允许您管理表索引，则可以将不使用索引执行的 70,000 次连接减至最少，请参阅 REFMAN 以获取有关如何完成此任务的提示。

.如果时间允许，请在至少 7 天的正常运行时间后重新发布来自 MySQL Tuner.pl 的完整新报告，让我们知道您在此实例中的进展。

如需更多帮助，请在我的个人资料中查找联系信息。