在查询推行时,等待次数和等候时间在一定水准上提示查询的瓶颈,以至老大有帮衬对系统进行确诊,对查询语句举行质量调优。不经常一回的要命等待,不足以表明系统设有瓶颈,不过,SQL
Server实例日常现身特定的等候类型,并且等待时间趋于扩大,那就认证,系统设有压力,或内部存款和储蓄器,或IO等,根据WaitType对系统进行监察和确诊,还是能够对查询进行质量调优,举例,Lock等待表明查询存在数量角逐,PageIOLatch等待阐明IO响应缓慢,PageLatch等待表明文件的构造必要改善等。

问题现象

    since i’ve broken down the
replication enviornment by “reset master;” yesterday.therefore,i’d like
to reconfigure it again,there’re several simple steps below:

生龙活虎,能源随机信号(RESOURCE SEMAPHORE)

从Ali云上镜头像过来的风姿洒脱台数据库服务器,SQL
Agent服务运行不了,提醒服务运转后终止。(原数据库服务器是例行的,猜忌跟镜像有关)

 

1,RESOURCE_SEMAPHORE 等待类型表示三个Workder等待SQL
Server付与其申请的内部存款和储蓄器,以便实行Hash和Sort等操作

 

I. Master node operations:

当出现 RESOURCE_SEMAPHORE
等待时,这申明查询语句乞请的内部存款和储蓄器未有获得满意,便是说,该查询语句在试行Task前,要求轻巧的内存财富,假诺SQL
Server当前的内存不足,不可能分红查询语句央浼的内部存款和储蓄器,将促成查询语句处于等候内部存款和储蓄器能源的场所。在SQL
Server存储引擎中,排序(Sort)操作和哈希(Hash)操作是不行消耗内部存款和储蓄器财富的五个操作,优化相应的查询语句,以压缩那三个操作,能够消除SQL
Server的内存压力,但在SQL Server实例中,常常出现RESOURCE_SEMAPHORE
等待,这表明SQL Server存在内部存款和储蓄器压力。

澳门微尼斯人手机版 1

 1 1.Check the necessary parameter is surely spedified.
 2 (root@localhost mysql3306.sock)[(none)]04:04:00>show variables like 'server_id';
 3 +---------------+---------+
 4 | Variable_name | Value   |
 5 +---------------+---------+
 6 | server_id     | 1023306 |
 7 +---------------+---------+
 8 1 row in set (0.00 sec)
 9 
10 (root@localhost mysql3306.sock)[(none)]04:04:22>show variables like 'log_bin';
11 +---------------+-------+
12 | Variable_name | Value |
13 +---------------+-------+
14 | log_bin       | ON    |
15 +---------------+-------+
16 1 row in set (0.00 sec)
17 
18 (root@localhost mysql3306.sock)[(none)]04:04:22>show variables like 'gtid_mode';
19 +---------------+-------+
20 | Variable_name | Value |
21 +---------------+-------+
22 | gtid_mode     | ON    |
23 +---------------+-------+
24 1 row in set (0.00 sec)
25 
26 This two parameter below is not necessary but still recommended:
27 
28 (root@localhost mysql3306.sock)[(none)]04:04:23>show variables like 'enforce_gtid_consistency';
29 +--------------------------+-------+
30 | Variable_name            | Value |
31 +--------------------------+-------+
32 | enforce_gtid_consistency | ON    |
33 +--------------------------+-------+
34 1 row in set (0.00 sec)
35 
36 (root@localhost mysql3306.sock)[(none)]04:04:30>show variables like 'log_slave_updates';
37 +-------------------+-------+
38 | Variable_name     | Value |
39 +-------------------+-------+
40 | log_slave_updates | ON    |
41 +-------------------+-------+
42 1 row in set (0.00 sec)
43 
44 (root@localhost mysql3306.sock)[(none)]04:04:31>
45 
46 2.Create replication user and grant imperative privileges. 
47 (root@localhost mysql3306.sock)[(none)]04:12:49>create user 'repl'@'%' identified by 'repl4slave';
48 Query OK, 0 rows affected (0.01 sec)
49 
50 (root@localhost mysql3306.sock)[(none)]04:12:57>grant replication slave on *.* to 'repl'@'%';
51 Query OK, 0 rows affected (0.00 sec)
52 
53 (root@localhost mysql3306.sock)[(none)]04:13:06>
54         
55 3.Backup the full database of master by mysqldump and send to the purpose Slave node.
56 [root@zlm3 04:20:52 /data/backup]
57 #pwd
58 /data/backup
59 
60 [root@zlm3 04:23:54 /data/backup]
61 #/usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -p --master-data=2 --single-transaction -A > db3306-`date +%Y%m%d`.sql
62 Enter password: 
63 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
64 
65 [root@zlm3 04:24:05 /data/backup]
66 #ls -l
67 total 1016
68 -rw-r--r-- 1 root root 1038595 Jun  6 04:24 db3306-20180606.sql
69 
70 using the "-E","-R" to add events and routines into the dumpfile of backup can eliminate above warning messages.why not using "--trigger" parameter?'cause its default value is "true" in my MySQL version(5.7.21),there's no need to explicitly specify.
71 
72 [root@zlm3 04:24:21 /data/backup]
73 #/usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -p --master-data=2 --single-transaction -A -E -R > db3306-`date +%Y%m%d`-full.sql
74 Enter password: 
75 
76 [root@zlm3 04:24:32 /data/backup]
77 #ls -l
78 total 2036
79 -rw-r--r-- 1 root root 1040952 Jun  6 04:24 db3306-20180606-full.sql
80 -rw-r--r-- 1 root root 1038595 Jun  6 04:24 db3306-20180606.sql
81 
82 [root@zlm3 04:24:36 /data/backup]
83 #scp db3306-20180606-full.sql zlm4:/data/backup
84 root@zlm4's password: 
85 db3306-20180606-full.sql                                                                                           100% 1017KB   1.0MB/s   00:00    
86 
87 [root@zlm3 04:24:56 /data/backup]
88 #

在数据库中有三个采撷,Min Memory Per
Query
,该选用表示SQL
Server为每个查询分配的蝇头内部存款和储蓄器,那表示,当三个查询必要特别的内部存款和储蓄器财富,该查询得到的内部存储器大小,很超越半数是由该选用决定的,独有为各样查询付与一定的内部存款和储蓄器之后,该查询语句才会真正带头实施。

 

 

2,发送RESOURCE SEMAPHORE用于给予诉求内存(Requested Memory)

平常来讲是系统日志和SQL Agent的日记

II. Slave node operations:

当SQL Server实例收到客户的询问央求时,SQL
Server优化器首先制造编写翻译布置(Complied
Plan),依照编写翻译布署再成立推行布署(Execution Plan)。当SQL
Server优化器创设编写翻译安立时,它须要总结查询在施行时供给消耗的内存,用于实行查询的内部存款和储蓄器分为必须内部存款和储蓄器(Required
Memory)和额外内部存款和储蓄器(Additional Memory)。必须内部存款和储蓄器是指SQL
Server实例施行Sort或Hash操作时必得分配的细微内部存款和储蓄器,若无分配必得内部存款和储蓄器,查询伏乞不会实施。额外内部存款和储蓄器是查询用于存款和储蓄有的时候的高中级数据的内部存款和储蓄器,如若SQL
Server未有丰盛的内部存款和储蓄器,查询将有时数据存储在硬盘中,那会收缩查询质量。

SQLServerAgent could not be started (reason: 无法连接到服务器“(local)”;SQLServerAgent 无法启动)
  1 1.clean the enviornment.(here i'll use the "rm -rf" to delete all the original files on it.)
  2 [root@zlm4 04:36:34 /data/mysql/mysql3306/data]
  3 #pwd
  4 /data/mysql/mysql3306/data
  5 
  6 [root@zlm4 04:36:37 /data/mysql/mysql3306/data]
  7 #ls -l
  8 total 410524
  9 -rw-r----- 1 mysql mysql        56 Apr 28 14:40 auto.cnf
 10 -rw-r----- 1 mysql mysql    847926 Jun  4 11:23 error.log
 11 -rw-r----- 1 mysql mysql      2144 Jun  4 11:23 ib_buffer_pool
 12 -rw-r----- 1 mysql mysql 104857600 Jun  4 11:23 ibdata1
 13 -rw-r----- 1 mysql mysql 104857600 Jun  4 11:23 ib_logfile0
 14 -rw-r----- 1 mysql mysql 104857600 May 28 03:27 ib_logfile1
 15 -rw-r----- 1 mysql mysql 104857600 Jun  4 11:23 ib_logfile2
 16 -rw-r----- 1 mysql mysql      3821 Apr 28 20:57 innodb_status.5065
 17 -rw-r----- 1 mysql mysql       136 Jun  4 11:23 master.info
 18 drwxr-x--- 2 mysql mysql      4096 Apr 28 14:40 mysql
 19 drwxr-x--- 2 mysql mysql      8192 Apr 28 14:40 performance_schema
 20 -rw-r----- 1 mysql mysql       201 Jun  4 06:49 relay-bin.000063
 21 -rw-r----- 1 mysql mysql       390 Jun  4 11:23 relay-bin.000064
 22 -rw-r----- 1 mysql mysql        38 Jun  4 06:49 relay-bin.index
 23 -rw-r----- 1 mysql mysql        65 Jun  4 11:23 relay-log.info
 24 -rw-r----- 1 mysql mysql     14064 Jun  4 06:46 slow.log
 25 drwxr-x--- 2 mysql mysql      8192 Apr 28 14:40 sys
 26 drwxr-x--- 2 mysql mysql      8192 May  2 04:59 zabbix
 27 drwxr-x--- 2 mysql mysql        97 May 29 04:28 zlm
 28 
 29 [root@zlm4 04:36:39 /data/mysql/mysql3306/data]
 30 #rm -rf *
 31 
 32 [root@zlm4 04:36:48 /data/mysql/mysql3306/data]
 33 #ls -l
 34 total 0
 35 
 36 [root@zlm4 04:36:51 /data/mysql/mysql3306/data]
 37 #cd ..
 38 
 39 [root@zlm4 04:36:52 /data/mysql/mysql3306]
 40 #cd logs
 41 
 42 [root@zlm4 04:37:07 /data/mysql/mysql3306/logs]
 43 #ls -l
 44 total 42944
 45 -rw-r----- 1 mysql mysql 8611664 May 25 11:31 mysql-bin.000015
 46 -rw-r----- 1 mysql mysql     257 May 25 11:31 mysql-bin.000016
 47 -rw-r----- 1 mysql mysql 2019506 May 28 04:49 mysql-bin.000017
 48 -rw-r----- 1 mysql mysql 5654926 May 28 11:37 mysql-bin.000018
 49 -rw-r----- 1 mysql mysql 7148106 May 29 11:27 mysql-bin.000019
 50 -rw-r----- 1 mysql mysql 7010806 May 30 11:29 mysql-bin.000020
 51 -rw-r----- 1 mysql mysql   73339 May 31 03:16 mysql-bin.000021
 52 -rw-r----- 1 mysql mysql 7646943 May 31 11:28 mysql-bin.000022
 53 -rw-r----- 1 mysql mysql 1126469 Jun  1 11:38 mysql-bin.000023
 54 -rw-r----- 1 mysql mysql 4626287 Jun  4 11:23 mysql-bin.000024
 55 -rw-r----- 1 mysql mysql     440 Jun  4 06:46 mysql-bin.index
 56 
 57 [root@zlm4 04:37:08 /data/mysql/mysql3306/logs]
 58 #rm -f *
 59 
 60 [root@zlm4 04:37:12 /data/mysql/mysql3306/logs]
 61 #ls -l
 62 total 0
 63 
 64 [root@zlm4 04:45:39 /data/mysql/mysql3306/logs]
 65 #
 66                          
 67 2.Start the mysqld and check the necessary parameter in mysql client.                       
 68 [root@zlm4 04:34:50 ~]
 69 #sh mysqld.sh
 70 
 71 [root@zlm4 04:40:50 ~]
 72 #ps aux|grep mysqld
 73 mysql     4012 25.1 15.8 896948 161060 pts/1   Sl   04:40   0:01 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 74 root      4042  0.0  0.0 112640   960 pts/1    R+   04:40   0:00 grep --color=auto mysqld
 75 
 76 [root@zlm4 04:40:55 ~]
 77 #mysql
 78 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
 79 
 80 [root@zlm4 04:40:58 ~]
 81 #ps aux|grep mysqld
 82 root      4053  0.0  0.0 112640   960 pts/1    R+   04:41   0:00 grep --color=auto mysqld
 83 
 84 The mysqld cannot be started,let's check the "error.log" to findout what has happened.
 85 
 86 [root@zlm4 04:41:10 ~]
 87 #cd /data/mysql/mysql3306/data/
 88 
 89 [root@zlm4 04:41:32 /data/mysql/mysql3306/data]
 90 #ls -l
 91 total 409624
 92 -rw-r----- 1 mysql mysql        56 Jun  6 04:40 auto.cnf
 93 -rw-r----- 1 mysql mysql      9361 Jun  6 04:40 error.log
 94 -rw-r----- 1 mysql mysql       215 Jun  6 04:40 ib_buffer_pool
 95 -rw-r----- 1 mysql mysql 104857600 Jun  6 04:40 ibdata1
 96 -rw-r----- 1 mysql mysql 104857600 Jun  6 04:40 ib_logfile0
 97 -rw-r----- 1 mysql mysql 104857600 Jun  6 04:40 ib_logfile1
 98 -rw-r----- 1 mysql mysql 104857600 Jun  6 04:40 ib_logfile2
 99 -rw-r----- 1 mysql mysql       173 Jun  6 04:40 slow.log
100 
101 [root@zlm4 04:43:05 /data/mysql/mysql3306/data]
102 #cat error.log|grep ERROR
103 2018-06-06T02:40:54.506533Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
104 2018-06-06T02:40:54.516986Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
105 2018-06-06T02:40:54.517028Z 0 [ERROR] Aborting
106 
107 [root@zlm4 04:43:15 /data/mysql/mysql3306/data]
108 #                         
109 
110 It seems the mysql.user table is indispensable.thus,i'll initialize the db first. 
111 
112 [root@zlm4 04:43:15 /data/mysql/mysql3306/data]
113 #/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --initialize
114 2018-06-06T02:54:35.627237Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
115 2018-06-06T02:54:35.627308Z 0 [ERROR] Aborting
116 
117 
118 [root@zlm4 04:54:35 /data/mysql/mysql3306/data]
119 #rm -f *
120 
121 [root@zlm4 04:54:47 /data/mysql/mysql3306/data]
122 #/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --initialize
123 
124 [root@zlm4 04:54:54 /data/mysql/mysql3306/data]
125 #ls -l
126 total 409644
127 -rw-r----- 1 mysql mysql        56 Jun  6 04:54 auto.cnf
128 -rw-r----- 1 mysql mysql       984 Jun  6 04:54 error.log
129 -rw-r----- 1 mysql mysql       420 Jun  6 04:54 ib_buffer_pool
130 -rw-r----- 1 mysql mysql 104857600 Jun  6 04:54 ibdata1
131 -rw-r----- 1 mysql mysql 104857600 Jun  6 04:54 ib_logfile0
132 -rw-r----- 1 mysql mysql 104857600 Jun  6 04:54 ib_logfile1
133 -rw-r----- 1 mysql mysql 104857600 Jun  6 04:54 ib_logfile2
134 drwxr-x--- 2 mysql mysql      4096 Jun  6 04:54 mysql
135 drwxr-x--- 2 mysql mysql      8192 Jun  6 04:54 performance_schema
136 -rw-r----- 1 mysql mysql       194 Jun  6 04:54 slow.log
137 drwxr-x--- 2 mysql mysql      8192 Jun  6 04:54 sys
138 
139 [root@zlm4 04:54:58 /data/mysql/mysql3306/data]
140 #ps aux|grep mysqld
141 root      4146  0.0  0.0 112640   960 pts/1    R+   04:55   0:00 grep --color=auto mysqld
142 
143 [root@zlm4 04:55:56 /data/mysql/mysql3306/data]
144 #/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf &
145 [1] 4151
146 
147 [root@zlm4 04:56:11 /data/mysql/mysql3306/data]
148 #ps aux|grep mysqld
149 mysql     4151  9.0 17.3 1069544 176676 pts/1  Sl   04:56   0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
150 root      4184  0.0  0.0 112640   956 pts/1    R+   04:56   0:00 grep --color=auto mysqld
151 
152 [root@zlm4 04:56:14 /data/mysql/mysql3306/data]
153 #
154 
155 okay,the mysqld process turned to be normal right now,go on.
156                                               
157 3.check the necessary parameter.(if they're not correct,modify them)
158 [root@zlm4 05:01:32 /data/mysql/mysql3306/data]
159 #mysql
160 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
161 
162 [root@zlm4 05:01:47 /data/mysql/mysql3306/data]
163 #cat error.log | grep temporary password
164 grep: password: No such file or directory
165 
166 [root@zlm4 05:02:19 /data/mysql/mysql3306/data]
167 #cat error.log | grep "temporary password"
168 2018-06-06T02:54:52.457126Z 1 [Note] A temporary password is generated for root@localhost: r?uoNuzqz3oj
169 
170 [root@zlm4 05:02:39 /data/mysql/mysql3306/data]
171 #mysql -p
172 Enter password: 
173 Welcome to the MySQL monitor.  Commands end with ; or \g.
174 Your MySQL connection id is 44
175 Server version: 5.7.21-log
176 
177 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
178 
179 Oracle is a registered trademark of Oracle Corporation and/or its
180 affiliates. Other names may be trademarks of their respective
181 owners.
182 
183 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
184 
185 (root@localhost mysql.sock)[(none)]05:02:57>show variables like 'server_id';
186 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
187 (root@localhost mysql.sock)[(none)]05:08:32>
188 
189 because of the MySQL 5.7 security strategies,using temprary password "r?uoNuzqz3oj" to login after initialization at the first time is imperative and the password must be changed before doing queries.
190 
191 (root@localhost mysql.sock)[(none)]05:12:02>alter user 'root'@'localhost' identified by 'Passw0rd';
192 Query OK, 0 rows affected (0.00 sec)
193 
194 (root@localhost mysql.sock)[(none)]05:12:36>show variables like 'server_id';
195 +---------------+---------+
196 | Variable_name | Value   |
197 +---------------+---------+
198 | server_id     | 1033306 |  --look out,the "server_id" should be different with the one in Maser host.
199 +---------------+---------+
200 1 row in set (0.00 sec)
201 
202 (root@localhost mysql.sock)[(none)]05:13:06>show variables like 'log_bin';
203 +---------------+-------+
204 | Variable_name | Value |
205 +---------------+-------+
206 | log_bin       | ON    |
207 +---------------+-------+
208 1 row in set (0.00 sec)
209 
210 (root@localhost mysql.sock)[(none)]05:13:11>show variables like 'gtid_mode';
211 +---------------+-------+
212 | Variable_name | Value |
213 +---------------+-------+
214 | gtid_mode     | ON    |
215 +---------------+-------+
216 1 row in set (0.01 sec)
217 
218 (root@localhost mysql.sock)[(none)]05:13:16>show variables like 'enforce_gtid_consistency';
219 +--------------------------+-------+
220 | Variable_name            | Value |
221 +--------------------------+-------+
222 | enforce_gtid_consistency | ON    |
223 +--------------------------+-------+
224 1 row in set (0.01 sec)
225 
226 (root@localhost mysql.sock)[(none)]05:13:20>show variables like 'log_slave_updates';
227 +-------------------+-------+
228 | Variable_name     | Value |
229 +-------------------+-------+
230 | log_slave_updates | ON    |
231 +-------------------+-------+
232 1 row in set (0.01 sec)
233 
234 (root@localhost mysql.sock)[(none)]05:13:25>
235                                              
236 4. Import the dumpfile of backup received from the Master node                       
237 [root@zlm4 06:05:18 ~]
238 #cd /data/backup
239 
240 [root@zlm4 06:05:22 /data/backup]
241 #ls -l
242 total 1020
243 -rw-r--r-- 1 root root 1040952 Jun  6 04:24 db3306-20180606-full.sql
244 
245 [root@zlm4 06:05:24 /data/backup]
246 #mysql < db3306-20180606-full.sql
247 ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
248 
249 [root@zlm4 06:05:47 /data/backup]
250 #mysql
251 Welcome to the MySQL monitor.  Commands end with ; or \g.
252 Your MySQL connection id is 446
253 Server version: 5.7.21-log MySQL Community Server (GPL)
254 
255 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
256 
257 Oracle is a registered trademark of Oracle Corporation and/or its
258 affiliates. Other names may be trademarks of their respective
259 owners.
260 
261 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
262 
263 (root@localhost mysql.sock)[(none)]06:08:54>show master status;
264 +------------------+----------+--------------+------------------+----------------------------------------+
265 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
266 +------------------+----------+--------------+------------------+----------------------------------------+
267 | mysql-bin.000003 |      398 |              |                  | fc288b24-6934-11e8-9b0e-080027de0e0e:1 |
268 +------------------+----------+--------------+------------------+----------------------------------------+
269 1 row in set (0.00 sec)
270 
271 (root@localhost mysql.sock)[(none)]06:09:02>select @@global.gtid_executed;
272 +----------------------------------------+
273 | @@global.gtid_executed                 |
274 +----------------------------------------+
275 | fc288b24-6934-11e8-9b0e-080027de0e0e:1 |
276 +----------------------------------------+
277 1 row in set (0.00 sec)
278 
279 (root@localhost mysql.sock)[(none)]06:09:21>select @@global.gtid_purged;
280 +----------------------+
281 | @@global.gtid_purged |
282 +----------------------+
283 |                      |
284 +----------------------+
285 1 row in set (0.00 sec)
286 
287 (root@localhost mysql.sock)[(none)]06:09:33>reset master;  -- This command can only be executed on Slave node.
288 Query OK, 0 rows affected (0.01 sec)
289 
290 (root@localhost mysql.sock)[(none)]06:10:01>select @@global.gtid_executed;
291 +------------------------+
292 | @@global.gtid_executed |
293 +------------------------+
294 |                        |
295 +------------------------+
296 1 row in set (0.00 sec)
297 
298 (root@localhost mysql.sock)[(none)]06:10:13>exit
299 Bye
300 
301 [root@zlm4 06:10:22 /data/backup]
302 #mysql < db3306-20180606-full.sql
303 
304 [root@zlm4 06:10:29 /data/backup]
305 #mysql
306 Welcome to the MySQL monitor.  Commands end with ; or \g.
307 Your MySQL connection id is 459
308 Server version: 5.7.21-log MySQL Community Server (GPL)
309 
310 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
311 
312 Oracle is a registered trademark of Oracle Corporation and/or its
313 affiliates. Other names may be trademarks of their respective
314 owners.
315 
316 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
317 
318 (root@localhost mysql.sock)[(none)]06:10:39>show databases;
319 +--------------------+
320 | Database           |
321 +--------------------+
322 | information_schema |
323 | mysql              |
324 | performance_schema |
325 | sys                |
326 | zlm                |  -- The database "zlm" in Master node has been restored here.
327 +--------------------+
328 5 rows in set (0.00 sec)
329 
330 (root@localhost mysql.sock)[(none)]06:10:43>            
331                   
332 5.Execute "change master to ..." command to make the Slave node become the real Slave DB server of the Master node.
333 (root@localhost mysql.sock)[(none)]06:10:43>change master to \
334     -> master_host='zlm3',\
335     -> master_port=3306,\
336     -> master_user='repl',\
337     -> master_password='repl4slave',\
338     -> master_auto_position=1;
339 Query OK, 0 rows affected, 2 warnings (0.03 sec)
340 
341 (root@localhost mysql.sock)[(none)]06:15:59>start slave;
342 Query OK, 0 rows affected (0.01 sec)
343 
344 (root@localhost mysql.sock)[(none)]06:16:12>show slave status\G
345 *************************** 1. row ***************************
346                Slave_IO_State: Waiting for master to send event
347                   Master_Host: zlm3
348                   Master_User: repl
349                   Master_Port: 3306
350                 Connect_Retry: 60
351               Master_Log_File: mysql-bin.000003
352           Read_Master_Log_Pos: 595
353                Relay_Log_File: relay-bin.000002
354                 Relay_Log_Pos: 414
355         Relay_Master_Log_File: mysql-bin.000003
356              Slave_IO_Running: Yes
357             Slave_SQL_Running: Yes
358               Replicate_Do_DB: 
359           Replicate_Ignore_DB: 
360            Replicate_Do_Table: 
361        Replicate_Ignore_Table: 
362       Replicate_Wild_Do_Table: 
363   Replicate_Wild_Ignore_Table: 
364                    Last_Errno: 0
365                    Last_Error: 
366                  Skip_Counter: 0
367           Exec_Master_Log_Pos: 595
368               Relay_Log_Space: 615
369               Until_Condition: None
370                Until_Log_File: 
371                 Until_Log_Pos: 0
372            Master_SSL_Allowed: No
373            Master_SSL_CA_File: 
374            Master_SSL_CA_Path: 
375               Master_SSL_Cert: 
376             Master_SSL_Cipher: 
377                Master_SSL_Key: 
378         Seconds_Behind_Master: 0
379 Master_SSL_Verify_Server_Cert: No
380                 Last_IO_Errno: 0
381                 Last_IO_Error: 
382                Last_SQL_Errno: 0
383                Last_SQL_Error: 
384   Replicate_Ignore_Server_Ids: 
385              Master_Server_Id: 1023306
386                   Master_UUID: 5c77c31b-4add-11e8-81e2-080027de0e0e
387              Master_Info_File: /data/mysql/mysql3306/data/master.info
388                     SQL_Delay: 0
389           SQL_Remaining_Delay: NULL
390       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
391            Master_Retry_Count: 86400
392                   Master_Bind: 
393       Last_IO_Error_Timestamp: 
394      Last_SQL_Error_Timestamp: 
395                Master_SSL_Crl: 
396            Master_SSL_Crlpath: 
397            Retrieved_Gtid_Set: 
398             Executed_Gtid_Set: 5c77c31b-4add-11e8-81e2-080027de0e0e:1-2
399                 Auto_Position: 1
400          Replicate_Rewrite_DB: 
401                  Channel_Name: 
402            Master_TLS_Version: 
403 1 row in set (0.00 sec)
404 
405 (root@localhost mysql.sock)[(none)]06:16:18>
406 
407 Eventually,the Master-Slave replication based on GTID has been accomplished now.

SQL Server 要予以每一种查询多少内部存款和储蓄器,查询才干确实起头推行吗?

 

 

  • Step1,总结要求的内存(Needed
    Memory):SQL
    Server总结种种查询供给某些内部存款和储蓄器手艺实行,这常常是不可缺乏内部存款和储蓄器和附加内部存款和储蓄器之和,当查问诉求以并发情势推行时,需求的内部存款和储蓄器公式是:(RequiredMemory*DOP)+额外内部存款和储蓄器。
  • Step2,总结伏乞的内部存款和储蓄器(Requested
    Memory):SQL
    Server检查每一种查询哀告须求的内部存款和储蓄器数量是不是当先系统的约束,SQL
    Server收缩附加内部存储器的数目,引致于不会压倒系统的上限,那个最后的内存数量是查询语句得以实行的央浼内部存款和储蓄器。
  • Step3,为查询分配央浼内部存款和储蓄器:SQL
    Server实例发送能源频限信号(RESOURCE
    SEMAPHORE),为查询(Query)付与/分配伏乞的情理内部存款和储蓄器。

澳门微尼斯人手机版 2

当财富随机信号发送之后,若是SQL
Server实例不能够被赋予查询的央求内部存款和储蓄器,那么查询将高居RESOURCE_SEMAPHORE
等待状态。SQL
Server维护七个先入先出( first-come-first-served)的等候队列,当新的查询处于RESOURCE_SEMAPHORE
等待情状,SQL Server将该查询放入队列的结尾。意气风发旦SQL
Server实例找到丰富的空余内部存款和储蓄器,那么SQL Server抽出RESOURCE_SEMAPHORE
等待队列顶部的第二个查询,立时予以其伏乞的内部存款和储蓄器;该查询获得央求内部存储器之后,早先试行查询任务;假设SQL
Server实例长时间有询问处于RESOURCE_SEMAPHORE等待景况,表明SQL Server
直面内部存储器压力。

2016-05-20 11:09:03 - ? [100] Microsoft SQLServerAgent 版本 11.0.3000.0 (内部版本号 X64 unicode 零售): 进程 ID 3568
2016-05-20 11:09:03 - ? [495] SQL Server 代理启动服务帐户是 WORKGROUP\iZ23egtmtrmZ$。
2016-05-20 11:09:03 - ! [150] SQL Server 不接受连接(错误: 18456)。请等待 SQL Server 允许连接。尝试的操作为: 启动时验证连接。
2016-05-20 11:09:03 - ! [000] 无法连接到服务器“(local)”;SQLServerAgent 无法启动
2016-05-20 11:09:03 - ! [298] SQLServer 错误:  18456,用户 'NT AUTHORITY\ANONYMOUS LOGON' 登录失败。 [SQLSTATE 28000] 
2016-05-20 11:09:03 - ! [382] 无法登录到服务器“(local)”(DisableAgentXPs)
2016-05-20 11:09:03 - ? [098] SQLServerAgent 已终止(一般)

二,调治队列功率信号

澳门微尼斯人手机版 3

DISPATCHER_QUEUE_SEMAPHORE,发生当叁个经过(Thread)等待管理更加多的Work时,该等待是说,叁个Thread处于空闲状态,等待调治去专门的职业。纵然等待时间增添,表达调治器(Dispatcher)极其空闲;该WaitType不会化为竞争财富,而将别的业务梗塞,在做Wait总括解析,能够过滤掉。

 

三,IO等待

化解方式

1,异步网络IO

在登记表中,定位到SQL Agent的ServerHOST属性:

发表评论

电子邮件地址不会被公开。 必填项已用*标注