☰

调整 Percona Server 5.7

tag-icon • tag-icon mysql percona
调整 Percona Server 5.7

我使用的是 4 核 ubuntu 云服务器，8GB 内存和 ssd。我只有一个 Magento 商店的数据库，大小约为 2GB。上个月我做了一些更改后，数据库似乎运行正常。Percona 服务器 5.7 运行 mysqltuner.pl 后，我有以下建议：

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 4.0M, or always use indexes with JOINs)
    table_open_cache (> 400)
    thread_pool_size between 16 and 36 for InnoDB usage
    key_buffer_size (> 112.5M)

My.cnf 设置如下：

[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /dev/shm
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp
skip-name-resolve=1
#skip-grant-tables
log-error    = /var/log/mysql/error.log
# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
#
# * Basic Settings
##
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address            = localhost
#bind-address            = 94.237.44.193
#
# * Fine Tuning
#
key_buffer_size         = 64M
join_buffer_size        = 4M
max_allowed_packet      = 128M
thread_stack            = 192K
thread_cache_size       = 128K
thread_pool_size        = 32M
connect_timeout=320 # default 10
table_open_cache = 256M
table_definition_cache  = 256M
sort_buffer_size        = 16M
max_heap_table_size     = 64M
tmp_table_size          = 64M
interactive_timeout     = 3600
max_connections         = 600
max_connect_errors      = 100
#skip_name_resolve
#skip_secure_auth
wait_timeout            = 28800
innodb_buffer_pool_size = 4096M
myisam_sort_buffer_size = 128M
innodb_log_file_size    = 512M
innodb_buffer_pool_instances = 4
innodb_thread_concurrency  = 0
innodb_flush_log_at_trx_commit       = 2
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
##table_open_cache       = 3000
#thread_concurrency     = 5
#
# * Query Cache Configuration
#
##query_cache_limit       = 256M
##query_cache_size        = 64M
##query_cache_type        = 1

正如你通过这些设置看到的：MySQL 的最大内存使用量非常高

有人能帮忙如何更好地调整这一点吗？

答案1

Mysqltuner 在“最大内存”方面持悲观态度。

8GB 是大概您的设置很好。但是，请注意“交换”。如果存在交换，我们需要降低一些设置。

dba.stackexchange.com（如果您需要进一步的建议，请转到。）

答案2

对 my.cnf [mysqld] 部分的建议

join_buffer_size=2M  # from 4M requested per connection
thread_cache_size=100  # from 128K for CAP suggested in 5.7 REFMAN
thread_pool_size=32  # from 32M  a major difference
table_open_cache=1024  # from 256M that would be a LOT of open tables
table_definition_cache=512  # from 256M for shared table definitions all connections
sort_buffer_size=2M  # from 16M requested per connection
max_connect_errors=10  # from 100 why give a hacker/cracker so many attempts?
innodb_lru_scan_depth=100  # from 1024 to reduce CPU cycles used every second for function

免责声明：我是我的个人资料、网络个人资料中提到的网站的内容作者，我们有免费的实用脚本，并提供更多建议。

相关内容