高性能mysql
第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值，
使用 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_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 # 把缓冲池切分成多段，可能是在高负载x的多核机器上提升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 = 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
