mysql_config.org

server 层参数
- 复制参数 master (server层做)
- 复制参数 slave (server层做)
Engine 层参数
- innodb
example with 4G Mem
tips

server 层参数

tmp-table-size
default 32M

copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等
需要用到结果集时，参数中设置的临时表的大小小于结果集的大小时，就
会将该表放在磁盘上，这个时候在硬盘上的IO要比内销差很多。所耗费的
时间也多很多。另外Mysql的另外一个参数max_heap_table_size比
tmp_table_size小时，则系统会把max_heap_table_size的值作为最大的内
存临时表的上限，大于这个时，改写硬盘。

This variable determines the maximum size for a temporary table
in memory. If the table becomes too large, a MYISAM table is
created on disk. Try to avoid temporary tables by optimizing the
queries where possible, but where this is not possible, try to
ensure temporary tables are always stored in memory. Watching the
processlist for queries with temporary tables that take too long
to resolve can give you an early warning that tmp_table_size
needs to be upped. Be aware that memory is also allocated
per-thread. An example where upping this worked for more was a
server where I upped this from 32MB (the default) to 64MB with
immediate effect. The quicker resolution of queries resulted in
less threads being active at any one time, with all-round
benefits for the server, and available memory.

query-cache-type (0|1|2)
query-cache-size
max-connections
table-open-cache
保持处于open 状态的table 的数量（应该是）（需要根据库里表的数量来决定）
mysql 会缓存一部分 open table, 当缓存满时再去open 一个table 时， mysql
会根据LRU法则把最旧的table 关闭掉。所以如果这个值设置的过小的话，
mysql 会频繁的open close table
可以根据Table_open_cache_misses这个值来判断 table-open-cache 是否正常
如果Table_open_cache_misses 远大于Open_tables
这几个变量show global status like 'Open%';
wait_timeout (8h? 8day?)
transaction-isolation (rc|rc) 事务隔离级别
key_buffer
low_case_table_names (ignore case )
sort_buffer_size (order by 是在server 层做)

复制参数 master (server层做)

log-bin
server-id
binlog-do-db (哪些库主从复制)
binlog-ignore-db （忽略哪些库）
binlog-format(row|stmt|mixed) row-整行数据记录， stmt-记录sql语句

半同步复制(plugin 存在)

半同步复制— 扔给从库从库接收后立刻回复master好了，
同步复制— 扔给从库从库接收后，从库上apply后再回复我好了

rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout

复制参数 slave (server层做)

server-id
master-port
slave-skip-errors
relay_log
sync_binlog
replicate-ignore-db replicate-do-db replicate-do-table 与master
上的binlog-do-db inlog-ignore-db 对应

Engine 层参数

innodb

innodb-buffer-pool-size default 8M (一定要调大 50%~-80% mem)
innodb-flush-log-at-tx-commit (0|1|2)
不同的值影响两个操作（write,flush） ,
- write 是操作系统级的write(意思是说 write 到操作系统的缓存了)
- flush 刷日志到硬盘
0 表示每秒钟write and flush 一次，但是sql commit 的时候并不提交
1 每次sql commit 都会write and flush （最安全也是最慢的）
2 每次sql commit 都会write ,但是每间隔1s 才flush 一次 (折中，如果mysql 崩掉只会丢失1s的数据)
0, the log buffer is written out to the log file once per second
and the flush to disk operation is performed on the log file, but
nothing is done at a transaction commit. When the value is 1 (the
default), the log buffer is written out to the log file at each
transaction commit and the flush to disk operation is performed
on the log file. When the value is 2, the log buffer is written
out to the file at each commit, but the flush to disk operation
is not performed on it. However, the flushing on the log file
takes place once per second also when the value is 2. Note that
the once-per-second flushing is not 100% guaranteed to happen
every second, due to process scheduling issues.
innodb-flush-method(fdatasync|O_DIRECT|O_DSYNC)
基本O_DIRECT
fdatasync 用fsync 告诉操作系统 ( 一定要绕过缓存)
O_DIRECT redo log 直接到硬盘
innodb-file-per-table 一定要设
每建个表 innodb 为其建两文件
inodb_file_format 与之相关
innodb-log-buffer-size –64M
innodb-log-file-size 两个文件轮换着写
innodb-io-capacity=200|2000|20000
200 15000转sasi盘
2000 150000转做了raid10的盘20块
根据iops来设置
inodb-read-io-threads 2*cpu
inodb-write-io-threads 2*cpu
innodb_stats_on_metadata=off
innodb-buffer-pool-instance=4 左右
innodb-buffer-pool-instance*innodb-buffer-pool-size
实际使用的内存
libaio
innodb_use_native_aio (需要装libaio)
use-sys-malloc (do not use)
use google tc-malloc
mysql 改io 调度器为deadline
sync_binlog：默认情况下，0,表示写日志文件不会马上同步到磁盘。可以设置每N次写操作后同步到磁盘。

example with 4G Mem

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name server generated for 653252292@qq.com at 2015-07-01 11:34:37

[mysql]

# CLIENT #
port                           = 3306
socket                         = /data/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /data/mysql/mysql.sock
pid-file                       = /data/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /data/mysql/

# BINARY LOGGING #
log-bin                        = /data/mysql/mysql-bin
server_id              = 1
sync-binlog                    = 1
expire_logs_days               = 7
# CACHES AND LIMITS #
tmp-table-size                 = 200M
max-heap-table-size            = 200M
sort-buffer-size               =200M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 3
innodb-log-file-size           = 1300M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2800M
explicit_defaults_for_timestamp=true

# LOGGING #
log-error                      = /data/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /data/mysql/mysql-slow.log

tips

可以查看当前mysql 正在执行哪些sql 语句，
可以用来统计正在大量执行哪些sql 语句
set global general_log=on

拾遗笔记