ProxySQL升级时对默认部署的实例的影响

公司有使用默认安装目录的ProxySQL跑业务. 在升级2.0.8 至 2.0.12的时候发现卸载2.0.8后这些使用默认方式安装的ProxySQL实例就会被关闭.

原因是rpm包安装和卸载前后执行了一些动作

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
[root@bj1-mysql-manager-prod-01 tmp]# rpm --scripts -qp  /tmp/proxysql-2.0.8-1-centos7.x86_64.rpm 
preinstall scriptlet (using /bin/sh):
# Cleanup artifacts
if [ -f /var/lib/proxysql/PROXYSQL_UPGRADE ]; then
rm -fr /var/lib/proxysql/PROXYSQL_UPGRADE
fi
postinstall scriptlet (using /bin/sh):
# Create relevant user, directories and configuration files
if [ ! -d /var/run/proxysql ]; then /bin/mkdir /var/run/proxysql ; fi
if [ ! -d /var/lib/proxysql ]; then /bin/mkdir /var/lib/proxysql ; fi
if ! id -u proxysql > /dev/null 2>&1; then useradd -r -U -s /bin/false -d /var/lib/proxysql -c "ProxySQL Server" proxysql; fi
/bin/chown -R proxysql: /var/lib/proxysql /var/run/proxysql
/bin/chown root:proxysql /etc/proxysql.cnf
/bin/chmod 640 /etc/proxysql.cnf
# Configure systemd appropriately.
/bin/systemctl daemon-reload
/bin/systemctl enable proxysql.service
# Notify that a package update is in progress in order to start service.
if [ $1 -eq 2 ]; then /bin/touch /var/lib/proxysql/PROXYSQL_UPGRADE ; fi
preuninstall scriptlet (using /bin/sh):
# When uninstalling always try stop the service, ignore failures
/bin/systemctl stop proxysql || true
postuninstall scriptlet (using /bin/sh):
if [ $1 -eq 0 ]; then
# This is a pure uninstall, systemd unit file removed
# only daemon-reload is needed.
/bin/systemctl daemon-reload
else
# This is an upgrade, ProxySQL should be started. This
# logic works for packages newer than 2.0.7 and ensures
# a faster restart time.
/bin/systemctl start proxysql.service
/bin/rm -fr /var/lib/proxysql/PROXYSQL_UPGRADE
fi
posttrans scriptlet (using /bin/sh):
if [ -f /var/lib/proxysql/PROXYSQL_UPGRADE ]; then
# This is a safeguard to start the service after an update
# which supports legacy "preun" / "postun" logic and will
# only execute for packages before 2.0.7.
/bin/systemctl start proxysql.service
/bin/rm -fr /var/lib/proxysql/PROXYSQL_UPGRADE
fi

MySQL动态行转列

通过max行转列不是动态的, 你还是要知道所有列名才可以

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@localhost 17:46:56 [fanboshi]> select * from t3;
+----+---------+------+-----------+
| id | title | env | progress |
+----+---------+------+-----------+
| 1 | 工单1 | 1 | 完成 |
| 2 | 工单1 | 2 | 完成 |
| 3 | 工单1 | 3 | 待审核 |
| 4 | 工单2 | 1 | 待审核 |
+----+---------+------+-----------+
4 rows in set (0.00 sec)

root@localhost 17:48:20 [fanboshi]> select title,max(if(env=1,progress,-1)) 'RC',max(if(env=2,progress,-1)) 'Stage',max(if(env=3,progress,-1)) 'Prod' from t3 group by title;
+---------+-----------+--------+-----------+
| title | RC | Stage | Prod |
+---------+-----------+--------+-----------+
| 工单1 | 完成 | 完成 | 待审核 |
| 工单2 | 待审核 | -1 | -1 |
+---------+-----------+--------+-----------+
2 rows in set (0.00 sec)

死锁案例2 两个UPDATE死锁

问题描述

2020.05.18 16:12 收到报警

登录主机, 查看error log( 开启了innodb_print_all_deadlocks参数)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
*** (1) TRANSACTION:

TRANSACTION 1261729280, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3645828, OS thread handle 139681739994880, query id 1522438991 172.16.23.82 yos_rw Searching rows for update
UPDATE f_log SET D_UPDATED_AT = NOW() , I_STATUS = 17 WHERE I_STATUS = 1 AND I_TYPE = 1
2020-05-18T16:11:11.696061+08:00 3056367 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1208 page no 29439 n bits 160 index PRIMARY of table `yos`.`f_log` trx id 1261729280 lock_mode X locks rec but not gap waiting
2020-05-18T16:11:11.696079+08:00 3056367 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 1261729278, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 3
MySQL thread id 3056367, OS thread handle 139682403342080, query id 1522438990 172.16.23.82 yos_rw updating
UPDATE f_log SET D_UPDATED_AT='2020-05-18 16:11:11.693998',I_STATUS=3 WHERE (I_FILE_ID='92b52d6589ce11a9a3c985e7c5f37462efb66ac7')
2020-05-18T16:11:11.696099+08:00 3056367 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1208 page no 29439 n bits 160 index PRIMARY of table `yos`.`f_log` trx id 1261729278 lock_mode X locks rec but not gap
2020-05-18T16:11:11.696112+08:00 3056367 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1208 page no 17 n bits 1120 index idx_status_type of table `yos`.`f_log` trx id 1261729278 lock_mode X locks rec but not gap waiting
2020-05-18T16:11:11.696223+08:00 3056367 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

简单解释一下上面的死锁日志

MGR单主到底做不做冲突检测?

和同事探讨一个问题, MGR单主做不做冲突检测.

我理解是不需要做的, 因为已经明确只有主节点才能写入数据了, 那么必然不会有数据冲突的可能, 没必要再做冲突检测浪费性能了.

看了下官方文档

1
In single-primary mode, Group Replication enforces that only a single server writes to the group, so compared to multi-primary mode, consistency checking can be less strict and DDL statements do not need to be handled with any extra care

译文 New Feature in Percona XtraDB Cluster 8.0 – Streaming Replication

New Feature in Percona XtraDB Cluster 8.0 – Streaming Replication

Percona XtraDB Cluster 8.0附带了一个升级的Galera 4.0库, 它提供了一个新特性—Streaming Replication.让我们回顾一下它是什么, 什么时候可能有用

以前版本的Percona XtraDB集群和Galera 3.x在处理大事务方面有限制.

让我们看一下sysbench-tpcc工作负载下的性能, 与此同时, 我们对一个表执行了一个大的更新语句, 该更新甚至与主工作负载中的表都不相关.

译文 Galera 4 Streaming Replication in Percona XtraDB Cluster 8.0

Galera 4 Streaming Replication in Percona XtraDB Cluster 8.0

我正在测试最新的Percona XtraDB Cluster 8.0 (PXC)版本, 其中包含了Galera 4插件, 我想分享一下到目前为止我在Streaming Replication特性方面的经验和想法,

What Is Streaming Replication, in One Sentence?

在Galera 4中, 大型事务可以分割成更小的片段, 甚至在提交之前, 这些片段就已经被复制到其他节点, 并且已经开始了认证(certification)和应用(apply)过程.

手册描述了所有的优点和缺点, 但是让我们看看它是如何工作的. 我已经创建了一个包含1千万行的表, 我将在这个表上运行一些大的update语句,

首先, 我在不使用Streaming Replication的情况下运行更新, 由于默认情况下Streaming Replication是disabled, 所以我们不需要做任何事情, 只需运行更新即可. 在node1上, 我记录更新之前和之后的时间, 在node2上, 我每秒钟运行一次select, 以查看这个更新在其他节点上实际提交的时间.


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2021 Fan() All Rights Reserved.

访客数 : | 访问量 :