-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathMYSQL参数优化.txt
197 lines (141 loc) · 7.51 KB
/
MYSQL参数优化.txt
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
--------------仿照阿里云的rds配置调整----------------
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
socket=/mnt/mysql/data/mysql/mysql.sock
[mysqld]
datadir=/mnt/mysql/data/mysql
socket=/mnt/mysql/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server = utf8
key_buffer=16777216
binlog_order_commits=1
event_scheduler=1
innodb_disable_sort_file_cache=1
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda
open_files_limit=25000 #部分机器不生效
innodb_open_files=3000
innodb_spin_wait_delay=30
innodb_sync_spin_loops=100
interactive_timeout=7200
join_buffer_size=442368
back_log=300
server_id=20170519
transaction-isolation=READ-COMMITTED
log-bin=binlog
binlog_format=ROW
lower_case_table_names=1
default-storage-engine=INNODB
max_connections=2050
max_user_connections=2050
max_allowed_packet=50M
sort_buffer_size=1M
query_cache_size=3M
binlog_cache_size=4M
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1
#
relay_log_info_repository=table
master_info_repository=table
relay_log_recovery=on
slow-query-log=on
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
socket=/mnt/mysql/data/mysql/mysql.sock
default-character-set = utf8
--------------仿照阿里云的rds配置调整----------------结束
参考：mysql启动参数
http://blog.csdn.net/liu251890347/article/details/47998249
max_connections参数无效。
SHOW VARIABLES LIKE '%CONNECTION%';
SET GLOBAL max_connections=2050;
back_log:值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：
unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时。
back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog，目前系统为1024。对于Linux系统推荐设置为大于512的整数。
修改系统内核参数，可以编辑/etc/sysctl.conf去调整它。如：net.ipv4.tcp_max_syn_backlog = 2048，改完后执行sysctl -p 让修改立即生效
binlog_order_commits:按照binlog的写入顺序提交事务,保证redo和binlog的已执行
transaction-isolation：READ-COMMITTED阿里
READ-UNCOMMITTED(读取未提交内容)：A开启事务查询，B开启事务修改，A途中可以查看到B未提交的数据，B回滚，A查看到的是回滚的数据
READ-COMMITTED（读取提交内容）：A开启事务查询，B开启事务修改，A途中看不到B未提交的事务，B提交，A可以看到新数据,A再修改，B开启事务修改，提交，必须等A提交事务，否则挂起。
REPEATABLE-READ(可重读):A开启事务查询，B开启事务修改，A途中看不到修改的内容,B提交，还是看不到，A提交事务后可以看到，A开始事务再修改，B重开事务修改，修改被挂起，直到超时，对另一条记录修改却成功，说明A对表进行修改时加了行共享锁(可以select)
SERIERLIZED(可串行化)：A开启事务查询，B查询可查，B开启事务修改，只能等待，A提交，B可以修改。
max_allowed_packet：
# 允许外部文件级别的锁. 打开文件锁会对性能造成负面影响
# 所以只有在你在同样的文件上运行多个数据库实例时才使用此选项(注意仍会有其他约束!)
# 或者你在文件层面上使用了其他一些软件依赖来锁定 MyISAM 表
#external-locking
# 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的 BLOB 字段一起工作时相当必要)
# 每个连接独立的大小，大小动态增加
innodb_disable_sort_file_cache：设置为ON的话，表示在排序中生成的临时文件不会用到文件系统的缓存
sort_buffer_size：ORDER BY/GROUP BY排序能用的大小
压力测试：
--concurrency代表并发数量，多个可以用逗号隔开，concurrency=10,50,100, 并发连接线程数分别是10、50、100个并发。
--engines代表要测试的引擎，可以有多个，用分隔符隔开。
--iterations代表要运行这些测试多少次。
--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
--debug-info 代表要额外输出CPU以及内存的相关信息。
--number-int-cols ：创建测试表的 int 型字段数量
--auto-generate-sql-add-autoincrement : 代表对生成的表自动添加auto_increment列，从5.1.18版本开始
--number-char-cols 创建测试表的 char 型字段数量。
--create-schema 测试的schema，MySQL中schema也就是database。
--query 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--only-print 如果只想打印看看SQL语句是什么，可以用这个选项。
mysqlslap -ukmadmin -pkmadmin --concurrency=500 --iterations=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info
mysqlslap -ukmadmin -pkmadmin --concurrency=500 –commint=50 --iterations=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --engine=innodb --number-of-queries=1000 --debug-info
Warning: Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.098 seconds
Minimum number of seconds to run all queries: 0.098 seconds
Maximum number of seconds to run all queries: 0.098 seconds
Number of clients running queries: 100
Average number of queries per client: 0
User time 0.02, System time 0.03
Maximum resident set size 4736, Integral resident set size 0
Non-physical pagefaults 1623, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 2813, Involuntary context switches 166
binlog删除十天前
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
查看日志
SHOW MASTER LOGS;
清空binlog
RESET MASTER;
mysqld remove mysql57;
mysqld install mysql57 --defaults-file="D:\mysql-5.7.16-winx64\my.ini"
删除F:\Program Files\mysql-5.6.15-win32\data\ib_logfile0
unable to create temporary file
cd D:\mysql-5.7.16-winx64\bin
mysqld --defaults-file="D:\mysql-5.7.16-winx64\my.ini" --console
-- 输出到目录
mysqld --verbose --help >>e:\\cmd.txt
-- 按任意键退出
pause>null
主从复制下
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1
MySQL主从复制几个重要的启动选项
(1) log-slave-updates
log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作
(2) master-connect-retry
master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒
(3) read-only
read-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作
(4) slave-skip-errors
在复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。
Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。
--slave-skip-errors=[err1,err2,…….|ALL]
但必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本
MySQL管理利器：
http://www.ttlsa.com/mysql/mysql-manager-tools-mysql-utilities-tutorial/
自动故障转移：
http://www.ttlsa.com/mysql/mysql-automatic-failover/