首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
ProxySQL读写分离测试,7中如何定位DDL被阻塞的问题
发布时间:2019-12-30 07:24
浏览次数:

set rowcount 20000
delete from UFSystem..ua_log
set rowcount 0

在上篇文章《MySQL表结构变更,不可不知的Metadata Lock》中,我们介绍了MDL引入的背景,及基本概念,从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。

约束

 

truncate table ua_log_bak20111201

在MySQL 5.7中,针对MDL,引入了一张新表performance_schema.metadata_locks,该表可对外展示MDL的相关信息,包括其作用对象,类型及持有等待情况。

非空约束 --NN,ont null constraint

Preface

truncate table ua_log

 

必须填写数据不能为空

 

数据库 属性  选项 模型 改成 简单 然后确定
再进行数据库收缩

开启MDL的instrument

--指定表 Student 添加名为NN_Student_sClassId非空约束(指定列名sClassId),括号输入表达式

    I've implemented ProxySQL on PXC yesterday but got some errors when configured query rules.I'm gonna do it again in my master-slave environment again.Let's see the procedure.

但是相关instrument并没有开启(MySQL 8.0是默认开启的),其可通过如下两种方式开启,

alter table Student add constraint NN_Student_sClassId check(sClassId is not null)

 

临时生效

 

Procedure

修改performance_schema.setup_instrume nts表,但实例重启后,又会恢复为默认值。

主键约束 --PK,primary key constraint

 

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

唯一且不为空

Start ProxySQL.

 

--指定表 Student 添加名为PK_Student_sId主键约束(指定列名sId)

1 [root@zlm1 17:22:46 /var/lib]
2 #service proxysql start
3 Starting ProxySQL: ProxySQL is already running.
4 
5 [root@zlm1 17:23:16 /var/lib]
6 #ps aux|grep proxysql
7 root       666  0.0  0.5  58180  5180 ?        S    15:06   0:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
8 root       667  0.0  2.0 104152 21068 ?        Sl   15:06   0:02 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
9 root      5523  0.0  0.0 112640   960 pts/1    R+   15:55   0:00 grep --color=auto proxysql

永久生效

alter table Student add constraint PK_Student_sId primary key(sId)

 

在配置文件中设置

 

Login ProxySQL configure the hostgroups.

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

唯一约束 --UQ,unique constraint

 1 [root@zlm1 17:27:11 ~]
 2 #mysql -uadmin -padmin -h127.0.0.1 -P6032
 3 mysql: [Warning] Using a password on the command line interface can be insecure.
 4 Welcome to the MySQL monitor.  Commands end with ; or g.
 5 Your MySQL connection id is 1
 6 Server version: 5.5.30 (ProxySQL Admin Module)
 7 
 8 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 9 
10 Oracle is a registered trademark of Oracle Corporation and/or its
11 affiliates. Other names may be trademarks of their respective
12 owners.
13 
14 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
15 
16 admin@127.0.0.1:6032 [(none)]>select * from mysql_replication_hostgroups;
17 Empty set (0.00 sec)
18 
19 admin@127.0.0.1:6032 [(none)]>insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(10,20);
20 Query OK, 1 row affected (0.00 sec)
21 
22 admin@127.0.0.1:6032 [(none)]>select * from mysql_replication_hostgroups;
23 +------------------+------------------+---------+
24 | writer_hostgroup | reader_hostgroup | comment |
25 +------------------+------------------+---------+
26 | 10               | 20               |         |
27 +------------------+------------------+---------+
28 1 row in set (0.00 sec)
29 
30 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_replication_hostgroups;
31 Empty set (0.00 sec)
32 
33 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_replication_hostgroups;
34 Empty set (0.00 sec)
35 
36 admin@127.0.0.1:6032 [(none)]>load mysql servers to runtime;save mysql servers to disk;
37 Query OK, 0 rows affected (0.00 sec)
38 
39 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_replication_hostgroups;
40 +------------------+------------------+---------+
41 | writer_hostgroup | reader_hostgroup | comment |
42 +------------------+------------------+---------+
43 | 10               | 20               |         |
44 +------------------+------------------+---------+
45 1 row in set (0.00 sec)
46 
47 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_replication_hostgroups;
48 +------------------+------------------+---------+
49 | writer_hostgroup | reader_hostgroup | comment |
50 +------------------+------------------+---------+
51 | 10               | 20               |         |
52 +------------------+------------------+---------+
53 1 row in set (0.00 sec)

 

唯一,允许为空,但是同样的数据只能出现一次

 

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有