服务器配置优化

1.基础配置文件

[mysqld]
# GENERAL #
character_set_server           = utf8mb4
collation_server               = utf8mb4_general_ci
user                           = mysql
default_storage_engine         = InnoDB
port                           = 3306
socket                         = /var/lib/mysql/socket/mysql.sock
pid_file                       = /var/lib/mysql/pid/mysql.pid
default_time_zone              = +08:00

# MyISAM #
key_buffer_size                = 128M
myisam_sort_buffer_size        = 8M
read_rnd_buffer_size           = 262144

# SAFETY #
back_log                       = 50
bind-address                   = 0.0.0.0
innodb_strict_mode             = 1
max_allowed_packet             = 32M
max_connect_errors             = 1000000
interactive_timeout            = 900
wait_timeout                   = 900
skip_grant_tables              = 0
skip_name_resolve              = 1
host_cache_size                = 0
sql_mode                       = NO_AUTO_CREATE_USER
sysdate_is_now                 = 1
transaction_isolation          = REPEATABLE-READ
explicit_defaults_for_timestamp = 0

# DATA STORAGE #
basedir                        = /var/lib/mysql/private
datadir                        = /var/lib/mysql/data
tmpdir                         = /var/lib/mysql/tmp

# BINARY LOGGING #
max_binlog_files               = 100
binlog_cache_size              = 1M
binlog_format                  = ROW
binlog_checksum = NONE
expire_logs_days               = 1
max_binlog_size                = 512M
max_binlog_cache_size          = 4G
log_bin                        = /var/lib/mysql/binlog/mysql-bin
log_bin_index                  = /var/lib/mysql/binlog/mysql-bin.index
sync_binlog                    = 0
innodb_support_xa              = 1
master-info-repository         = TABLE

# REPLICATION #
log_slave_updates              = 0
read_only                      = 0
relay_log                      = /var/lib/mysql/binlog/relay-bin
relay_log_index                = /var/lib/mysql/binlog/relay-bin.index
server_id                      = 7901
slave_skip_errors              = ALL

slave_net_timeout              = 60
relay-log-info-repository      = TABLE
slave_compressed_protocol      = ON

# CACHES AND LIMITS #
max_connections                = 5000
max_heap_table_size            = 128M
tmp_table_size                 = 128M
open_files_limit               = 65535
query_cache_limit              = 1M
query_cache_min_res_unit       = 4096
query_cache_size               = 0
query_cache_type               = 0
join_buffer_size               = 1M
read_buffer_size               = 1M
sort_buffer_size               = 1M
table_definition_cache         = 4096
table_open_cache               = 4000
thread_cache_size              = 1000

# INNODB #
innodb_autoextend_increment    = 8
innodb_buffer_pool_instances   = 8
innodb_buffer_pool_size        = 1G
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_data_file_path          = ibdata1:256M:autoextend
innodb_fast_shutdown           = 1
innodb_file_format             = Barracuda
innodb_file_format_max         = Barracuda
innodb_file_per_table          = 1
innodb_force_recovery          = 0
innodb_flush_log_at_trx_commit = 0
innodb_flush_method            = O_DIRECT
innodb_lock_wait_timeout       = 50
innodb_log_buffer_size         = 8M
innodb_log_file_size           = 512M
innodb_log_files_in_group      = 2
innodb_open_files              = 10000
innodb_purge_threads           = 4
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_sync_spin_loops         = 30
innodb_thread_concurrency      = 24
innodb_print_all_deadlocks     = 1

# LOGGING #
general_log                    = 0
general_log_file               = /var/lib/mysql/log/mysql-general.log
log_error                      = /var/lib/mysql/log/mysql-error.log
log_queries_not_using_indexes  = 1
long_query_time                = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/log/mysql-slow.log

# GTID
enforce_gtid_consistency       = 0
gtid_mode                      = OFF

注意项

  • 明确指定socket和pid的文件位置(不同的MySQL版本可能不一致,这样升级MySQL时路径就不会改变)
  • 指定操作系统必须使用mysql用户来运行mysqld进程,需要确保这个账户存在,并且拥有操作数据目录的权限
  • 选择InnoDB作为默认的存储引擎,大多数情况下是最好的选择
  • 配置大小合适的缓冲池(Buffer Pool)和日志文件(Log File)
  • open_file_limit, 如果设置过小,会碰到"打开的文件太多(too many open files)错误"

2.配置项

2.1 mysql的缓存

  • InnoDB缓存池
  • InnoDB日志文件和MyISAM数据的操作系统缓存
  • MyISAM 键缓存
  • 查询缓存
  • 无法手动配置的缓存,例如二进制日志和表定义文件的操作系统缓存

2.2 InnoDB 缓存池

缓存池内容

  • 索引
  • 行数据
  • 自适应哈希索引
  • 插入缓存(Insert Buffer)
  • 锁
  • 其它内部数据结构

results matching ""

    No results matching ""