mysql optimization config

# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
skip_name_resolve=OFF
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet              = 256M
max_connect_errors              = 1000000
tmpdir                          = /tmp
# === InnoDB Settings ===
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1     # 4GB内存用1个实例即可
innodb_buffer_pool_size         = 1536M # 1.5GB，为连接缓冲留出空间
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 512M
innodb_stats_on_metadata        = 0
innodb_temp_data_file_path     = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
innodb_thread_concurrency      = 4     # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64
# === Query Cache ===
# MySQL 5.7 已废弃 Query Cache，游戏服写多读少场景下反而因全局锁拖慢性能
query_cache_limit              = 0
query_cache_size               = 0
query_cache_type               = 0     # 关闭
key_buffer_size                 = 8M    # MyISAM用，InnoDB为主不需要大
low_priority_updates            = 1
concurrent_insert               = 2
# === Connection Settings ===
max_connections                 = 1000  # 多台游戏服共用，需要大容错
back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K
interactive_timeout             = 120   # 2分钟，快速回收空闲连接
wait_timeout                    = 120   # 2分钟，插件侧已有自动重连
# === Buffer Settings ===
# 游戏插件查询均为简单单表 SELECT/UPDATE (WHERE steamid='xxx')
# 不需要大的 JOIN/SORT 缓冲，缩小以支持更多并发连接
# 每连接约 1.5M，1000连接全活跃也仅 1.5GB（实际活跃连接远低于此）
join_buffer_size                = 256K  # 默认值，游戏查询无复杂 JOIN
read_buffer_size                = 256K  # 简单查询够用
read_rnd_buffer_size            = 512K  # 随机读缓冲
sort_buffer_size                = 512K  # ORDER BY 缓冲
# === Table Settings ===
# 游戏数据库总共约30-50张表，不需要万级缓存
table_definition_cache          = 4000
table_open_cache                = 4000
open_files_limit                = 10000
max_heap_table_size             = 64M   # 内存临时表上限
tmp_table_size                  = 64M   # 与 max_heap_table_size 保持一致
# === Search Settings ===
ft_min_word_len                 = 3     # Minimum length of words to be indexed for search results
# === Binary Logging ===
disable_log_bin                 = 1     # Binary logging disabled by default
#log_bin                                # To enable binary logging, uncomment this line & only one of the following 2 lines
                                        # that corresponds to your actual MySQL/MariaDB version.
                                        # Remember to comment out the line with "disable_log_bin".
#expire_logs_days               = 1     # Keep logs for 1 day - For MySQL 5.x & MariaDB before 10.6 only
#binlog_expire_logs_seconds     = 86400 # Keep logs for 1 day (in seconds) - For MySQL 8+ & MariaDB 10.6+ only
# Logging
log_error                       = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes   = 1
long_query_time                 = 5
slow_query_log                  = 0     # Disabled for production
slow_query_log_file             = /var/lib/mysql/mysql_slow.log
[mysqldump]
# Variable reference
# For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB:   https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet              = 64M
column-statistics               = 0