帮我调一下my.conf MySQL
目前，我一直在寻找想法，复制发现的一些过程和细节，并使用我的服务器，现在似乎相当不错，但在性能上有些异常(并不总是).如果您发现我当前的配置有问题，可能会降低服务器的性能，而不是改进它，请让我知道，并感谢您的阅读和帮助。
我将我的conf文件附加到
OS
虚拟化: vmware操作系统: CentOS Linux 7(核心) CPE操作系统名称:/o:centos:centos:7内核:Linux3.10.0-1127.19.1.el7.x86_64体系结构: x86-64
资源
Ram 64 16 DDR3 - CPU 2核，每核8个逻辑处理器=16-磁盘Nvme m.2 500 16三星980Pro
MySQL 8.0.22
[mysqld_safe]
log_error = /var/log/mysql_error.log
[mysqld]
# datadir = {{ mysqldir }}
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
# Datadog
general_log = ON
general_log_file = /var/log/mysqld.log
log_error = /var/log/mysql_error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql_slow.log
long_query_time = 1
log_output = File
log_queries_not_using_indexes = ON
local-infile = ON
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
default_authentication_plugin = mysql_native_password
skip-character-set-client-handshake
# current configuration
skip-name-resolve = 1
sql-mode = ALLOW_INVALID_DATES
ssl-ca = ca.pem
ssl-cert = server-cert.pem
ssl-key = server-key.pem
require_secure_transport = ON
# general
table_definition_cache = 400000
back_log = 3500
# tune
max_seeks_for_key = 32
max_connections = 100
connect_timeout = 5
wait_timeout = 600
net_read_timeout = 60
max_allowed_packet = 160M
thread_cache_size = 128
bulk_insert_buffer_size = 16M
tmp_table_size = 16M
max_heap_table_size = 16M
sort_buffer_size = 4M
read_buffer_size = 2M
join_buffer_size = 512k
read_rnd_buffer_size = 512K
key_buffer_size = 6M
myisam_recover_options = BACKUP
myisam_sort_buffer_size = 16M
table_open_cache = 400000
table_open_cache_instances = 8
concurrent_insert = 2
max_binlog_size = 100M
thread_stack = 4M
max_prepared_stmt_count = 1000000
performance_schema = ON
binlog_expire_logs_seconds = 864000
innodb_log_files_in_group = 2
innodb_undo_log_truncate = OFF
innodb_doublewrite = 1
innodb_flush_log_at_trx_commit = 0
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_page_cleaners = 4
innodb_adaptive_max_sleep_delay = 10000
innodb_lru_scan_depth = 128
innodb_io_capacity = 40000
innodb_io_capacity_max = 80000
innodb_purge_threads = 4
innodb_flushing_avg_loops = 4
innodb_thread_concurrency = 0
innodb_buffer_pool_instances = 32
innodb_log_file_size = 4G
innodb_log_buffer_size = 3G
innodb_buffer_pool_size = 30G
innodb_change_buffer_max_size = 50
innodb_buffer_pool_chunk_size = 512M
innodb_file_per_table = 1
innodb_open_files = 600
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_flush_neighbors = 0
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_use_native_aio = 1
innodb_adaptive_hash_index = 0
innodb_stats_persistent = 1
innodb_adaptive_flushing = 1
innodb_monitor_enable = '%'更新
我一直在逐步评估我的进程和设置与日志文件和大量的阅读，到目前为止，这个设置工作得非常好和更快比以前的…现在插入，删除和读取从小时到甚至1分钟，没有CPU尖峰或RAM期待着你们认为我可以调整-谢谢。
[mysqld_safe]
log_error = /var/log/mysql_error.log
[mysqld]
# datadir = {{ mysqldir }}
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
# Datadog
#general_log = ON
#general_log_file = /var/log/mysqld.log
log_error = /var/log/mysql_error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql_slow.log
long_query_time = 1
log_output = File
log_queries_not_using_indexes = ON
local-infile = ON
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
default_authentication_plugin = mysql_native_password
skip-character-set-client-handshake
# current configuration
skip-name-resolve = 1
sql-mode = ALLOW_INVALID_DATES
ssl-ca = ca.pem
ssl-cert = server-cert.pem
ssl-key = server-key.pem
require_secure_transport = ON
# general
#table_definition_cache = 400000
#back_log = 3500
# tune
#max_seeks_for_key = 32
max_connections = 100
#connect_timeout = 5
#wait_timeout = 600
#net_read_timeout = 60
#max_allowed_packet = 160M
#thread_cache_size = 128
bulk_insert_buffer_size = 256M
tmpdir = /dev/shm
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 50M
read_buffer_size = 4M
join_buffer_size = 3M
read_rnd_buffer_size = 8M
innodb_sort_buffer_size = 50M
key_buffer_size = 50M
#myisam_recover_options = BACKUP
#myisam_sort_buffer_size = 16M
table_open_cache = 40000
#table_open_cache_instances = 8
#concurrent_insert = 2
#max_binlog_size = 100M
#thread_stack = 4M
#max_prepared_stmt_count = 1000000
performance_schema = 1
binlog_expire_logs_seconds = 864000
innodb_log_files_in_group = 2
#innodb_undo_log_truncate = OFF
#innodb_doublewrite = 0
#innodb_flush_log_at_trx_commit = 0
#innodb_max_dirty_pages_pct = 90
#innodb_max_dirty_pages_pct_lwm = 10
#innodb_page_cleaners = 4
#innodb_adaptive_max_sleep_delay = 10000
innodb_lru_scan_depth = 5196
innodb_io_capacity = 1000
#innodb_io_capacity_max = 2000
#innodb_purge_threads = 4
#innodb_flushing_avg_loops = 4
innodb_thread_concurrency = 0
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M
innodb_buffer_pool_size = 2G
#innodb_change_buffer_max_size = 50
#innodb_buffer_pool_chunk_size = 512M
#innodb_file_per_table = 1
#innodb_open_files = 600
innodb_flush_method = O_DIRECT_NO_FSYNC
#innodb_flush_neighbors = 0
innodb_write_io_threads = 16
innodb_read_io_threads = 16
#innodb_use_native_aio = 1
innodb_adaptive_hash_index = 1
#innodb_stats_persistent = 1
#innodb_adaptive_flushing = 1
innodb_monitor_enable = '%'
#innodb_dedicated_server = ON回答 2
Database Administration用户
发布于 2020-10-22 16:42:16
我有个好消息要告诉你
方面#1
您可以只使用一个选项来调优MySQL 8.0的某些方面：诺姆b_专事_服务器。
诺姆b_专事_服务器选项将自动调整四(4)件事情。
- 小于1G: 128米(如果innodb_dedicated_server为OFF，默认值)
- 小于或= 4G:检测到的物理RAM * 0.5
- 大于4G:检测到的物理RAM * 0.75
- 小于1G: 48M (如果innodb_dedicated_server为OFF，默认值)
- 小于4G: 128米
- 减than<=8G: 5.12亿
- <=16G: 1024M
- 大于16G: 2G
该变量还设置了以下内容：
- 如果设置在系统上可用，则设置为O_DIRECT_NO_FSYNC。
- 如果没有，请将其设置为默认的InnoDB刷新方法。
诺姆b_日志_文件_在……里面_组 (截至MySQL 8.0.14)：日志组中的日志文件数。InnoDB以循环方式写入文件。默认值(和推荐值)为2。文件的位置由innodb_log_group_home_dir指定。日志文件(innodb_log_file_size * innodb_log_files_in_group)的合并大小可以达到512 of。
方面#2
现在，您可以将诺姆b_朗读_io_线程和诺姆b_写_io_线程降低到16。
定期运行显示引擎INNODB状态\G
检查表达式Pending normal aio，您将看到如下所示
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,如果任何一个零经常变得很高(比如16-32)，那么增加其中一个或两者。
方面#3
你的诺姆b_日志_缓冲器_大小需要3200万，而不是3G。
方面#4
应该启用您的诺姆b_自适应_哈希_索引
方面#5
所有其他调优方面都将与MySQL 5.7相同。您可以参考这个Percona博客了解这些方面-- => 安装后的MySQL5.7性能优化。您还可以参考DBA StackExchange中的其他帖子。您绝对应该从MySQL文档中阅读innodb的所有选项.。
愉快的研究！
Database Administration用户
发布于 2020-10-22 17:55:52
你太过分了。默认情况下，您不应该接触innodb_buffer_pool_size以外的其他任何东西，除非您的工作负载特别需要偏离缺省值，这对于大多数工作负载来说是合理的。
在几乎所有情况下，将sort_buffer_size和join_buffer_size从默认情况下改变是特别适得其反的。innodb_open_files应该单独使用，因为它默认为table_open_cache --您将其设置得非常高。
您的innodb_io_capacity几乎肯定比SSD实际提供的16 to块的数据库工作负载更多，特别是因为您也启用了general_log。相反，最好禁用general_log，只让slow_log启用long_query_time=0集。
https://dba.stackexchange.com/questions/278513
复制相似问题
腾讯云开发者
