高性能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_readsfincore 工具可以看到那些块在缓存中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值， 使用 SUBSTRING() 函数把值把VARCHAR 变长列 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_onlyskip_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_sizeinnodb_log_file_size

在线工具

http://tools.percona.com

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

配置表

# my.cnf [client] port = 3306 socket = /log/mysql/mysql.sock [mysqld_multi] mysqld = /app/mysql/bin/mysqld_safe mysqladmin = /app/mysql/bin/mysqladmin log = /log/mysql/multi.log [mysqld1] port = 3307 socket = /log/mysql_1/mysql.sock pid-file = /log/mysql_1/mysqld.pid log-error = /log/mysql_1/error.log datadir = /data/mysql_1 tmpdir = /data/mysql_1 slow_query_log_file = /log/mysql_1/slow.log relay-log = mysqld-relay-bin long_query_time = 1 slow_query_log = 1 sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" transaction_isolation = REPEATABLE-READ old_passwords = 0 back_log = 1024 # 要求MySQL能有的连接数量 open_files_limit = 65535 explicit_defaults_for_timestamp = 1 default-storage-engine=InnoDB performance_schema = 0 max_connections = 16384 table_open_cache = 8192 # 表缓存,为每个线程、每个表使用 thread_concurrency = 32 max_connect_errors = 10000 # 有效禁止主机黑名单 interactive_timeout = 512 wait_timeout = 256 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M # 使用Memory 引擎的临时表能使用多大的内存 sort_buffer_size = 2M join_buffer_size = 2M # 8 + (max_connections / 100) thread_cache_size = 1024 query_cache_size = 0 query_cache_type = 0 #query_cache_limit = 16M #default_table_type = INNODB skip-external-locking skip-name-resolve server-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 read_buffer_size = 2M read_rnd_buffer_size = 16M innodb_buffer_pool_instances = 8 innodb_old_blocks_time = 1000 innodb_buffer_pool_size = 16G innodb_log_group_home_dir = /data/mysql_1 innodb_data_home_dir = /data/mysql_1 innodb_data_file_path = ibdata1:1G:autoextend innodb_autoextend_increment = 64 innodb_read_io_threads = 16 # 控制有多少后台线程可以被I/O操作使用 innodb_write_io_threads = 16 # 控制有多少后台线程可以被I/O操作使用 innodb_thread_concurrency = 32 innodb_flush_log_at_trx_commit = 2 # 把日志缓冲写到日志文件，但是并不刷新。如果mysql进程"挂了"2不会丢失日志，如果整个服务器"挂了"或者断电丢失一些事务。 innodb_log_buffer_size = 128M innodb_log_files_in_group = 2 innodb_log_file_size = 1G innodb_open_files = 16384 innodb_file_per_table = 1 # 每张表使用一个文件 .ibd innodb_purge_threads = 1 innodb_stats_persistent = 0 innodb_io_capacity = 500 innodb_max_dirty_pages_pct = 80 innodb_adaptive_flushing = 0 innodb_file_format = barracuda innodb_adaptive_hash_index = 0 #zfs #innodb_use_native_aio = 0 #innodb_doublewrite = 0 #innodb_flush_method = O_DSYNC innodb_flush_method = O_DIRECT # 非windows 用fsync() innodb_use_native_aio = 1 innodb_doublewrite = 1 # 双写缓冲 #innodb_sync_spin_loops = 100 #innodb_spin_wait_delay = 96 #innodb_checksum_algorithm = NONE ### slave ### #skip_slave_start # 这个选项阻止mysql试图自动启动复制 slave_compressed_protocol = 1 slave_parallel_workers = 0 master-info-repository = TABLE relay-log-info-repository = TABLE slave_type_conversions = ALL_NON_LOSSY [mysqld2] port = 3306 socket = /log/mysql/mysql.sock pid-file = /log/mysql/mysqld.pid log-error = /log/mysql/error.log datadir = /data/mysql tmpdir = /data/mysql slow_query_log_file = /log/mysql/slow.log relay-log = mysqld-relay-bin long_query_time = 1 slow_query_log = 1 sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" transaction_isolation = REPEATABLE-READ old_passwords = 0 back_log = 1024 open_files_limit = 65535 # 打开文件太多(too many open files) explicit_defaults_for_timestamp = 1 default-storage-engine=InnoDB performance_schema = 0 max_connections = 16384 #最大连接数 ”MySQL: ERROR 1040: Too many connections”的情况 table_open_cache = 8192 thread_concurrency = 32 max_connect_errors = 10000 # 每个主机在连接请求异常中断的最大次数，当超过该次数，则禁止host的连接请求，直到服务器重启或flush hosts命令清空该host的相关信息 interactive_timeout = 512 wait_timeout = 256 max_allowed_packet = 16M # 这个设置防止服务器发送太大的包 binlog_cache_size = 1M max_heap_table_size = 64M # 可创建的内存表大小 sort_buffer_size = 2M join_buffer_size = 2M # 8 + (max_connections / 100) thread_cache_size = 1024 query_cache_size = 0 query_cache_type = 0 #query_cache_limit = 16M #default_table_type = INNODB skip-external-locking skip-name-resolve server-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 = 2M read_rnd_buffer_size = 16M innodb_buffer_pool_instances = 8 # 把缓冲池切分成多段，可能是在高负载的多核机器上提升mysql可扩展性 innodb_old_blocks_time = 1000 # 指定一个页面从LRU链表的“年轻” 部分转移到“年老” 部分之间必须经过的毫秒数 innodb_buffer_pool_size = 8G innodb_log_group_home_dir = /data/mysql innodb_data_home_dir = /data/mysql #InnoDB 表空间 不能简单的删除或者改变大小 innodb_data_file_path = ibdata1:1G:autoextend innodb_autoextend_increment = 64 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_thread_concurrency = 32 # 限制并发 并发值=CPU数量*磁盘量*2 实际中小一点更好 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 128M innodb_log_files_in_group = 2 innodb_log_file_size = 1G innodb_open_files = 16384 innodb_file_per_table = 1 innodb_purge_threads = 1 innodb_stats_persistent = 0 innodb_io_capacity = 500 # 处理IO的能力，刷新脏页 PCI-E SSD 可以设置为上万 innodb_max_dirty_pages_pct = 80 innodb_adaptive_flushing = 0 innodb_file_format = barracuda innodb_adaptive_hash_index = 0 #zfs #innodb_use_native_aio = 0 #innodb_doublewrite = 0 #innodb_flush_method = O_DSYNC innodb_flush_method = O_DIRECT # 取决系统，通常需要带写换成的RAID卡 widows下默认使用async_unbuffered innodb_use_native_aio = 1 innodb_doublewrite = 1 #innodb_sync_spin_loops = 100 #innodb_spin_wait_delay = 96 #innodb_checksum_algorithm = NONE [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 65535 #pid-file = logs/mysqld.pid