MySQL配置的工作原理
1
2
3
4
5
|
root@msc3:~
# which mysqld
/usr/sbin/mysqld
root@msc3:~
# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
Default options are
read
from the following files
in
the given order:
/etc/my
.cnf
/etc/mysql/my
.cnf ~/.my.cnf
|
1
2
3
4
5
6
|
# 配置文件
max_connections=5000
max-connections=5000
# 命令行
/usr/sbin/mysqld
--max_connections=5000
/usr/sbin/mysqld
--max-connections=5000
|
-
query-cache-size 全局配置项
-
sort-buffer-size 默认全局相同,但每个线程里也可以设置
-
join-buffer-size 默认全局,且每个线程也可以设置。但若一个查询中关联多张表,可以为每个关联分配一个关联缓存(
join-buffer
),所以一个查询可能有多个关联缓冲。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
# 设置全局变量,GLOBAL和@@global作用是一样的
set
GLOBAL
sort
-buffer-size = <value>
set
@@global.
sort
-buffer-size := <value>
# 设置会话级变量,下面6种方式作用是一样的
# 即:没有修饰符、SESSION、LOCAL等修饰符作用是一致的
set
SESSION
sort
-buffer-size = <value>
set
@@session.
sort
-buffer-size := <value>
set
@@
sort
-buffer-size = <value>
set
LOCAL
sort
-buffer-size = <value>
set
@@ocal.
sort
-buffer-size := <value>
set
sort
-buffer-size = <value>
# set命令可以同时设置多个变量,但其中只要有一个变量设置失败,所有的变量都未生效
SET GLOBAL
sort
-buffer-size = 100, SESSION
sort
-buffer-size = 1000;
SET GLOBAL max-connections = 1000,
sort
-buffer-size = 1000000;
|
1
2
3
4
5
6
7
8
9
|
//
@exp 表示用户变量,上面的示例均是系统变量
//
错误
set
@user = 123456;
set
@group =
select
GROUP from USER where User = @user;
select
* from USER where GROUP = @group;
//
正确
SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;
有一些配置使用了不同的单位,比如
table-cache
变量指定表可以被缓存的数量,而不是表可以被缓存的字节数。而
key-buffer-size
则是以字节为单位。
|
小心翼翼的配置MySQL
好习惯1:不要通过配置项的名称来推断一个变量的作用
-
read-buffer-size
:当MySQL需要顺序读取数据时,如无法使用索引,其将进行全表扫描或者全索引扫描。这时,MySQL按照数据的存储顺序依次读取数据块,每次读取的数据块首先会暂存在缓存中,当缓存空间被写满或者全部数据读取结束后,再将缓存中的数据返回给上层调用者,以提高效率。 -
read-rnd-buffer-size
:和顺序读取相对应,当MySQL进行非顺序读取(随机读取)数据块的时候,会利用这个缓冲区暂存读取的数据。比如:根据索引信息读取表数据、根据排序后的结果集与表进行Join等等。总的来说,就是当数据块的读取需要满足一定的顺序的情况下,MySQL 就需要产生随机读取,进而使用到read-rnd-buffer-size
参数所设置的内存缓冲区。
好习惯2:不要轻易在全局修改会话级别的配置
1
2
3
4
|
SET @@seession.
sort
-buffer-size := <value>
-- 执行查询的sql
SET @@seession.
sort
-buffer-size := DEFAULT
#恢复默认值
# 可以将类似的代码封装在函数中方便使用。
|
好习惯3:配置变量时,并不是值越大越好
好习惯4:规范注释,版本控制
坏习惯1:根据一些“比率”来调优
坏习惯2:随便使用调优脚本
给你一个基本的MySQL配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
[mysql]
# CLIENT #
port = 3306
socket =
/var/lib/mysql/mysql
.sock
[mysqld]
# GENERAL #
user = mysql
port = 3306
default-storage-engine = InnoDB
socket =
/var/lib/mysql/mysql
.sock
pid-
file
=
/var/lib/mysql/mysql
.pid
# DATA STORAGE #
datadir =
/var/lib/mysql/
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
# BINARY LOGGING #
log-bin =
/var/lib/mysql/mysql-bin
expire-logs-days = 14
sync
-binlog = 1
# LOGGING #
log-error =
/var/lib/mysql/mysql-error
.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-
file
=
/var/lib/mysql/mysql-slow
.log
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-
type
= 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open
-files-limit = 65535
table-definition-cache = 4096
table-
open
-cache = 10240
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-
in
-group = 2
innodb-log-
file
-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-
file
-per-table = 1
innodb-buffer-pool-size = 12G
|
分段
-
[client] 客户端默认设置内容
-
[mysql] 使用mysql命令登录mysql数据库时的默认设置
-
[mysqld] 数据库本身的默认设置
GENERAL
DATA STORAGE
为缓存分配内存
-
InnoDB缓冲池
-
InnoDB日志文件和MyISAM数据的操作系统缓存(MyISAM依赖于OS缓存数据)
-
MyISAM键缓存
-
查询缓存
-
无法配置的缓存,比如:bin-log或者表定义文件的OS缓存
MyISAM
key-buffer-size
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
//
1.通过SQL语句查询
SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE =
'MYISAM'
//
2.统计索引文件的大小
$
du
-sch `
find
/path/to/mysql/data/directory/
-name
"*.MYI"
`
比如:
root@dev-msc3:
# du -sch `find /var/lib/mysql -name "*.MYI"`
72K
/var/lib/mysql/static/t_global_region
.MYI
40K
/var/lib/mysql/mysql/db
.MYI
12K
/var/lib/mysql/mysql/proxies_priv
.MYI
12K
/var/lib/mysql/mysql/tables_priv
.MYI
4.0K
/var/lib/mysql/mysql/func
.MYI
4.0K
/var/lib/mysql/mysql/columns_priv
.MYI
4.0K
/var/lib/mysql/mysql/proc
.MYI
4.0K
/var/lib/mysql/mysql/event
.MYI
4.0K
/var/lib/mysql/mysql/user
.MYI
4.0K
/var/lib/mysql/mysql/procs_priv
.MYI
4.0K
/var/lib/mysql/mysql/ndb_binlog_index
.MYI
164K total
|
1
2
3
|
//
key_blocks_unused的值可以通过 SHOW STATUS获取
//
key_cache_block_size的值可以通过 SHOW VARIABLES获取
(key_blocks_unused * key_cache_block_size) / key_buffer_size
|
1
2
3
|
# 计算每隔10s缓存未命中次数的增量
# 使用此命令时请带上用户和密码参数:mysqladmin -uroot -pxxx extended-status -r -i 10 | grep Key_reads
$ mysqladmin extended-status -r -i 10 |
grep
Key_reads
|
myisam-recover
-
DEFAULT:表示不设置,会尝试修复崩溃或者未完全关闭的表,但在恢复数据时不会执行其它动作
-
BACKUP:将数据文件备份到.bak文件,以便随后进行检查
-
FORCE:即使.myd文件中丢失的数据超过1行,也让恢复动作继续执行
-
QUICK:除非有删除块,否则跳过恢复
SAFETY
max-allowed-packet
max-connect-errors
LOGGING
sync-binlog
-
log-error:用于配置错误日志的存放目录
-
slow-query-log:打开慢日志,默认关闭
-
slow-query-log-file:配置慢日志的存放目录
-
log-queries-not-using-indexes:如果该sql没有使用索引,会将其写入到慢日志,但是否真的执行很慢,需要区分,默认关闭。
CACHES AND LIMITS
tmp-table-size && max-heap-table-size
query-cache-type && query-cache-size
max-connections
thread-cache-size
open-files-limit
tablecachesize
INNODB
innodb-buffer-pool-size
innodb-log-file-size && innodb-log-files-in-group
innodb-flush-log-at-trx-commit
-
0:每1秒钟将日志缓冲写到日志文件并刷新到磁盘,事务提交时不做任何处理
-
1:每次事务提交时,将日志缓冲写到日志文件并刷新到磁盘
-
2:每次事务提交时,将日志缓冲写到日志文件,然后每秒刷新一次到磁盘
innodb-flush-method
fdatasync
-
有的文件系统和os可以累积写操作后合并执行,通过对I/O的重排序来提升效率、或者并发写入多个设备
-
有的还可以做预读优化,比如连续请求几个顺序的块,它会通知硬盘预读下一个块
0_DIRCET
0_DSYNC
innodb-file-per-table
innodb-data-home-dir = /var/lib/mysql innodb-data-file-path = ibdata1:1G;ibdata2:1G;ibdata3:1G
innodb-data-file-path =ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend