拾遗笔记

mysql_config.org

server 层参数

  1. 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.

  1. query-cache-type (0|1|2)
  2. query-cache-size
  3. max-connections
  4. 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%';
  5. wait_timeout (8h? 8day?)
  6. transaction-isolation (rc|rc) 事务隔离级别
  7. key_buffer
  8. low_case_table_names (ignore case )
  9. 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层做)

  1. server-id
  2. master-port
  3. slave-skip-errors
  4. relay_log
  5. sync_binlog
  6. replicate-ignore-db replicate-do-db replicate-do-table 与master
    上的binlog-do-db inlog-ignore-db 对应

Engine 层参数

innodb

  1. innodb-buffer-pool-size default 8M (一定要调大 50%~-80% mem)
  2. 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.

  3. innodb-flush-method(fdatasync|O_DIRECT|O_DSYNC)
    基本O_DIRECT
    fdatasync 用fsync 告诉操作系统 ( 一定要绕过缓存)
    O_DIRECT redo log 直接到硬盘
  4. innodb-file-per-table 一定要设
    每建个表 innodb 为其建两文件
    inodb_file_format 与之相关
  5. innodb-log-buffer-size –64M
  6. innodb-log-file-size 两个文件轮换着写
  7. innodb-io-capacity=200|2000|20000
    200 15000转sasi盘
    2000 150000转 做了raid10的盘20块
    根据iops来设置
  8. inodb-read-io-threads 2*cpu
    inodb-write-io-threads 2*cpu
  9. innodb_stats_on_metadata=off
  10. innodb-buffer-pool-instance=4 左右
    innodb-buffer-pool-instance*innodb-buffer-pool-size
    实际使用的内存
  11. libaio
    innodb_use_native_aio (需要装libaio)
  12. use-sys-malloc (do not use)
    use google tc-malloc
  13. mysql 改io 调度器为deadline
  14. 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

Comments

comments powered by Disqus