高性能mysql 第8章 优化服务器设置 阅读笔记

高性能mysql
2017年7月11日

第8章 优化服务器设置

如果不知道配置路径

$ which myslqd

/usr/sbin/mysqld –verbose –help | grep -A 1 ‘Default options’

8.3 创建mysql 配置文件

不要把Socket文件和PID文件放到Mysql编译默认的位置，在不同的Mysql版本里这可能导致一些错误

• 设置缓冲池

8.4配置内存使用

• 重要缓存分配
• 大日志提升性能 innodb_max_dirty_pages_pct
• 查看MYISAM索引占用的实际空间
  select SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES where engine=’MYISAM’;
  unix
  du -sch ‘find /path/to/mysql/data/directory/ -name “*.MYI”’
  
  • CACHE INDEX 命令将表映射到对应缓冲区
    mysql> CACHE INDEX t1,t2 key_buffer_1;
    mysql > LOAD INDEX INTO CACHE t1,t2;
  • 键缓冲公式
  • 获取状态值变化量
    $mysqladmin extended-status -r -i 10|grep key_reads
  • fincore 工具可以看到那些块在缓存中
  • InodeDB为每个.ibd文件使用单个、全局的文件描述符，所以最好设置足够大

8.5 配置MySql 的IO/行为

• INnoDB事务日志
  innodb_flush_log_at_trx_commit 变量控制日志缓冲刷新的频繁程度
  0
  把日志缓冲写到日志文件，并且每秒种刷新一次，但是事物提交不做任何事。
  1
  把日志缓冲写到日志文件，每次事物持久化存储。默认（安全）
  2
  把日志缓冲写到日志文件，但是并不刷新。如果mysql进程”挂了”2不会丢失日志，如果整个服务器”挂了”或者断电丢失一些事务。
• MyISAM 的I/O配置
  mysql> show variables like ‘myisam_recover_options’; #检查是否损坏

8.6配置Mysql并发

innodb_thread_concurrency = 32 # 限制并发 并发值=CPU数量*磁盘量*2

concurrent_insert

0

MyISAM 不允许并发插入,所有插入都会对表加互斥锁

1

默认值，只要表没有空洞，允许并发操作

8.7 基于工作负载配置

innotop 监控服务器 pt-query-digest 创建查询报告 创建 processlist快照 脚本查看第三章

注意事项

• 服务器不能再内存临时表中存储BLOB值，
  
  1. 使用 SUBSTRING() 函数把值把VARCHAR
  2. 变长列 InnoDB 存储一个768字节的前缀在行内。
• 优化排序(Filesorts)
  max_length_for_sort_data 操过这个使用two-pass,否则使用single-pass
  tmp_table_size
  max_heap_table_size = 64M # 使用Memory 引擎的临时表能使用多大的内存,如果隐式临时表超过这两个值，则转换为MyISAM

8.9 安全和稳定的设置

• expire_logs_days = 7 # 自动清理二进制日志
• max_allowed_packet = 16M # 这个设置防止服务器发送太大的包
• max_connect_errors = 10000 # 有效禁止主机黑名单
• skip_name_resolve # 禁用了另一个网络相关和鉴权认证相关的陷阱：DNS查找
  验证主机名，为了导致超时，基于主机名的授权改为用ip地址、通配符。
• sql_mode 这个设定可以接受多种多样的值来确定改变服务器的行为。
• sysdate_is_now

防止备库问题

• read_only # 只接受来自主库传输过来的变更，不接受从应用来的变更。建议将备库设置为read_only
• skip_slave_start # 这个选项阻止mysql试图自动启动复制
• slave_net_timeout # 设置备库和主库连接失败并且需要重新连接的等待时间。
• relay_log 在好的硬件下建议打开，可以从崩溃中恢复
• overwrite_relay_log_info 可以让InoDB在事务日志重存储复制的位置。

8.10 高级InnoDB 设置

• innodb_autoinc_lock_mode 自增主键值，高并发情况下，自增主键值可能是个瓶颈。
• innodb_buffer_pool_instances = 8 # 把缓冲池切分成多段，可能是在高负载的多核机器上提升mysql可扩展性
• innodb_io_capacity = 500 # 处理IO的能力，刷新脏页 PCI-E SSD 可以设置为上万
  
  • innodb_read_io_threads = 16 # 控制有多少后台线程可以被I/O操作使用
    innodb_write_io_threads = 16 # 控制有多少后台线程可以被I/O操作使用
• innodb_strict_mode 将某些条件下把警告改成抛错
• innodb_old_blocks_time = 1000 # 指定一个页面从LRU链表的“年轻” 部分转移到“年老” 部分之间必须经过的毫秒数 1000毫秒=1秒

8.11 总结

使用InnoDB,两个最重要的选项

• innodb_buffer_pool_size
• innodb_log_file_size

在线工具

http://tools.percona.com

建议：不要”调优“ 服务器，不要信任网上不明身份的人的意见，不要去不断的刷show status

配置表

# my.cnf[client]port            = 3306socket          = /log/mysql/mysql.sock[mysqld_multi]mysqld = /app/mysql/bin/mysqld_safemysqladmin = /app/mysql/bin/mysqladminlog = /log/mysql/multi.log[mysqld1]port            = 3307socket          = /log/mysql_1/mysql.sockpid-file        = /log/mysql_1/mysqld.pidlog-error       = /log/mysql_1/error.logdatadir         = /data/mysql_1tmpdir          = /data/mysql_1slow_query_log_file = /log/mysql_1/slow.logrelay-log = mysqld-relay-binlong_query_time = 1 slow_query_log = 1sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"transaction_isolation = REPEATABLE-READold_passwords = 0back_log        = 1024 # 要求MySQL能有的连接数量open_files_limit = 65535explicit_defaults_for_timestamp = 1default-storage-engine=InnoDBperformance_schema = 0max_connections = 16384 table_open_cache = 8192  # 表缓存,为每个线程、每个表使用thread_concurrency = 32max_connect_errors = 10000  # 有效禁止主机黑名单interactive_timeout = 512wait_timeout = 256max_allowed_packet = 16Mbinlog_cache_size = 1Mmax_heap_table_size = 64M   # 使用Memory 引擎的临时表能使用多大的内存sort_buffer_size = 2Mjoin_buffer_size = 2M# 8 + (max_connections / 100)thread_cache_size = 1024query_cache_size = 0query_cache_type = 0#query_cache_limit = 16M#default_table_type = INNODBskip-external-lockingskip-name-resolveserver-id       = 525 #*** master ***#log-bin= bin-log#binlog-format = ROW#binlog_rows_query_log_events = 1#expire_logs_days = 7replicate-wild-ignore-table = mysql.%replicate-wild-ignore-table = test.%replicate-wild-ignore-table = information_schema.%replicate-wild-ignore-table = performance_schema.%#*** MyISAM Specific options ***key_buffer_size = 32Mread_buffer_size = 2Mread_rnd_buffer_size = 16Minnodb_buffer_pool_instances = 8innodb_old_blocks_time = 1000innodb_buffer_pool_size = 16Ginnodb_log_group_home_dir = /data/mysql_1innodb_data_home_dir  = /data/mysql_1innodb_data_file_path = ibdata1:1G:autoextendinnodb_autoextend_increment = 64innodb_read_io_threads = 16  # 控制有多少后台线程可以被I/O操作使用innodb_write_io_threads = 16 # 控制有多少后台线程可以被I/O操作使用innodb_thread_concurrency = 32innodb_flush_log_at_trx_commit = 2  # 把日志缓冲写到日志文件，但是并不刷新。如果mysql进程"挂了"2不会丢失日志，如果整个服务器"挂了"或者断电丢失一些事务。innodb_log_buffer_size = 128Minnodb_log_files_in_group = 2innodb_log_file_size = 1Ginnodb_open_files = 16384 innodb_file_per_table = 1  # 每张表使用一个文件 .ibdinnodb_purge_threads = 1innodb_stats_persistent = 0innodb_io_capacity = 500innodb_max_dirty_pages_pct = 80innodb_adaptive_flushing = 0innodb_file_format = barracudainnodb_adaptive_hash_index = 0#zfs#innodb_use_native_aio = 0#innodb_doublewrite = 0  #innodb_flush_method = O_DSYNCinnodb_flush_method = O_DIRECT  # 非windows 用fsync() innodb_use_native_aio = 1innodb_doublewrite = 1   # 双写缓冲#innodb_sync_spin_loops = 100#innodb_spin_wait_delay = 96#innodb_checksum_algorithm = NONE### slave ### #skip_slave_start  # 这个选项阻止mysql试图自动启动复制slave_compressed_protocol = 1slave_parallel_workers = 0master-info-repository = TABLErelay-log-info-repository = TABLEslave_type_conversions = ALL_NON_LOSSY[mysqld2]port            = 3306socket          = /log/mysql/mysql.sockpid-file        = /log/mysql/mysqld.pidlog-error       = /log/mysql/error.logdatadir         = /data/mysqltmpdir          = /data/mysqlslow_query_log_file = /log/mysql/slow.logrelay-log = mysqld-relay-binlong_query_time = 1 slow_query_log = 1sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"transaction_isolation = REPEATABLE-READold_passwords = 0back_log        = 1024      open_files_limit = 65535    # 打开文件太多(too many open files)explicit_defaults_for_timestamp = 1default-storage-engine=InnoDBperformance_schema = 0max_connections = 16384   #最大连接数 ”MySQL: ERROR 1040: Too many connections”的情况table_open_cache = 8192 thread_concurrency = 32max_connect_errors = 10000  # 每个主机在连接请求异常中断的最大次数，当超过该次数，则禁止host的连接请求，直到服务器重启或flush hosts命令清空该host的相关信息interactive_timeout = 512wait_timeout = 256max_allowed_packet = 16M  # 这个设置防止服务器发送太大的包binlog_cache_size = 1Mmax_heap_table_size = 64M  # 可创建的内存表大小sort_buffer_size = 2Mjoin_buffer_size = 2M# 8 + (max_connections / 100)thread_cache_size = 1024query_cache_size = 0query_cache_type = 0#query_cache_limit = 16M#default_table_type = INNODBskip-external-lockingskip-name-resolveserver-id       = 525 #*** master ***#log-bin= bin-log#binlog-format = ROW#binlog_rows_query_log_events = 1#expire_logs_days = 7  # 自动清理二进制日志replicate-wild-ignore-table = mysql.%replicate-wild-ignore-table = test.%replicate-wild-ignore-table = information_schema.%replicate-wild-ignore-table = performance_schema.%#*** MyISAM Specific options ***key_buffer_size = 32M  # GROUP BY 语句可能会使用MyISAM做临时表read_buffer_size = 2Mread_rnd_buffer_size = 16Minnodb_buffer_pool_instances = 8  # 把缓冲池切分成多段，可能是在高负载的多核机器上提升mysql可扩展性innodb_old_blocks_time = 1000 # 指定一个页面从LRU链表的“年轻” 部分转移到“年老” 部分之间必须经过的毫秒数innodb_buffer_pool_size = 8Ginnodb_log_group_home_dir = /data/mysqlinnodb_data_home_dir  = /data/mysql  #InnoDB 表空间 不能简单的删除或者改变大小innodb_data_file_path = ibdata1:1G:autoextendinnodb_autoextend_increment = 64innodb_read_io_threads = 16innodb_write_io_threads = 16innodb_thread_concurrency = 32   # 限制并发 并发值=CPU数量*磁盘量*2 实际中小一点更好innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 128Minnodb_log_files_in_group = 2innodb_log_file_size = 1Ginnodb_open_files = 16384 innodb_file_per_table = 1innodb_purge_threads = 1innodb_stats_persistent = 0innodb_io_capacity = 500  # 处理IO的能力，刷新脏页 PCI-E SSD 可以设置为上万innodb_max_dirty_pages_pct = 80innodb_adaptive_flushing = 0innodb_file_format = barracudainnodb_adaptive_hash_index = 0#zfs#innodb_use_native_aio = 0#innodb_doublewrite = 0#innodb_flush_method = O_DSYNCinnodb_flush_method = O_DIRECT  # 取决系统，通常需要带写换成的RAID卡 widows下默认使用async_unbufferedinnodb_use_native_aio = 1innodb_doublewrite = 1#innodb_sync_spin_loops = 100#innodb_spin_wait_delay = 96#innodb_checksum_algorithm = NONE[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 65535#pid-file         = logs/mysqld.pid