高性能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