AllinToyou

这个屌丝很懒，什么也没留下！

热门标签

MySQL代理层_ProxySQL--灵活强大的MySQL代理层

作者：AllinToyou | 2024-03-13 21:21:56

踩

proxysql连接数

一、亮点几乎所有的配置均可在线更改(其配置数据基于SQLite存储)，无需重启proxysql

基于正则和client_addr的强大和灵活的路由规则

详细的状态统计，统计结果和pt-query-digest对慢日志的分析结果类似，相当于有了统一的查看sql性能和sql语句统计的入口(Designed by a DBA for DBAs)

自动重连和重新执行机制(auto-reconnect and automatic re-execution of queries using it’s Connections Pool

)：若一个请求在链接或执行过程中意外中断，proxysql会根据其内部机制重新执行该操作

query cache功能：比mysql自带QC更灵活，可在mysql_query_rules表中依据digest,match_pattern,client_addr等维度控制哪类语句可以缓存

支持连接池(connection pool)并且支持multiplexing,区别于atlas之流的连接池实现。文中有详细对比说明

二、安装

rpm包下载地址

Installing from source

Make sure you have installed the equivalent for each of these packages for your operating system:automake

bzip2

cmake

make

gcc #>4.4版本

gcc-c++

git

openssl

openssl-devel

patch1234567891011

Go to the directory where you cloned the repo (or unpacked the tarball) and run:make

sudo make install123

Compilation time should be around a couple of minutes for the first time around. The configuration file will be found at /etc/proxysql.cnf afterwards.

在make这一步遇到了错误：g++ -fPIC -c -o obj/ProxySQL_GloVars.oo ProxySQL_GloVars.cpp -std=c++11 -I../include -I../deps/jemalloc/jemalloc/include/jemalloc -I../deps/mariadb-client-library/mariadb_client/include -I../deps/libconfig/libconfig-1.4.9/lib -I../deps/re2/re2 -I../deps/sqlite3/sqlite3 -O2 -ggdb -Wall

cc1plus: 错误：无法识别的命令行选项“-std=c++11”

make[1]: *** [obj/ProxySQL_GloVars.oo] 错误 1

make[1]: Leaving directory `/usr/local/src/proxysql-master/lib'

make: *** [build_lib] 错误 2123456

网查是由于gcc版本低导致，centos 6的yum源(以及epel源)都只能获取到4.4.7版本包 gcc-4.4.7-17.el6.x86_64 已安装并且是最新版本

包 gcc-c++-4.4.7-17.el6.x86_64 已安装并且是最新版本

而centos7上为4.8版本1234

换到centos7上，将上述软件安装/更新之后，make步骤完成，但是make install步骤又出了问题：install -m 0755 src/proxysql /usr/local/bin

install -m 0600 etc/proxysql.cnf /etc

install -m 0755 etc/init.d/proxysql /etc/init.d

if [ ! -d /var/lib/proxysql ]; then mkdir /var/lib/proxysql ; fi

update-rc.d proxysql defaults

make: update-rc.d：命令未找到

make: *** [install] 错误 12712345678

update-rc.d是ubuntu的自启动脚本管理软件,未成功安装不影响使用。

安装完成后，自动在/etc/init.d/proxysql增加服务管理脚本(需要把/usr/local/bin/加入$PATH或者软链至 $PATH目录下，脚本中直接用到proxysql命令)

三、配置

配置文件/etc/proxysql.cnf和配置数据库文件/var/lib/proxysql/proxysql.db，如果存在 “proxysql.db”文件，则启动过程不解析proxysql.cnf文件；配置文件只在第一次启动的时候读取

官方推荐用admin interface方式

登陆admin interface：mysql -uadmin -padmin -P6032 -h127.0.0.1

登陆成功后，可通过对main库(默认登陆后即在此库)的global_variables表中的

admin-admin_credentials

admin-mysql_ifaces

两个变量进行更改来修改登录认证123456

注意：admin interface对配置的存储是基于SQLite的，SQLite支持标准的SQL语法，与mysql也基本兼容。但是无法用use语句切换数据库，作者对use语句做了兼容(不报错)，但是却没有实际效果。

配置后端DB server：两种方式，区别在于： 1. 一种是在往mysql_servers表中添加server时就为其划分好hostgroup_id(例如0表示写组，1表示读组) 2. 另一种往mysql_servers表中添加server时不区分hostgroup_id(例如全部设为0)，然后通过mysql_replication_hostgroups表中的值，根据proxysql检测到的各server的read_only变量值来自动为后端server设置hostgroup_id

这里强烈推荐用第一种方式：

因为第一种是完全由我们控制的;而第二种假如我们误将读server的read_only属性设置为0，则proxysql会将其重新分配到写组，这绝对是不期望的。123456

四、功能测试

实验环境MySQL [(none)]> select * from mysql_servers;

| 0 | 192.168.1.21 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| 1 | 192.168.1.10 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| 1 | 192.168.1.4 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+#proxysql server的IP为：192.168.1.3412345678910

负载均衡测试

配置好一主(db1，hostgroup0)两从(db2和db3，hostgroup1) ，并且在’mysql_query_rules’表中增加一条路由规则insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (10,1,'^SELECT',1,1)1

意为所有以select开头的语句路由到hostgroup1，其余语句路由到hostgroup0

在两台server的mysql-client分别连接至proxysql的6033端口，执行’select @@hostname’观察其分配到的后端server

以mysql -e的形式执行该命令则能够看到请求在两台读server之间变换[root@db1 ~]# mysql -udm -p'dm' -h192.168.1.34 -P6033 -e "select @@hostname" -s -N.db1

[root@db1 ~]# mysql -udm -p'dm' -h192.168.1.34 -P6033 -e "select @@hostname" -s -Ndb5

[root@db1 ~]# mysql -udm -p'dm' -h192.168.1.34 -P6033 -e "select @@hostname" -s -Ndb1

[root@db1 ~]# mysql -udm -p'dm' -h192.168.1.34 -P6033 -e "select @@hostname" -s -Ndb5123456789101112再实验下mysql -e跟多条语句会如何

[root@db1 ~]# mysql -udm -p'dm' -P6033 -h192.168.1.34 -e "select @@hostname;select @@hostname;select @@hostname" -s -N

dm-web5

dm-web5123456

由以上结果可能会猜想并可印证：在一个client的一个链接周期内，所有query路由到同一台后端。

但是：这只是个假象(是因为正好用到了select @ 语句。按作者所说： sends a query that implicitly disables multiplexing. For example, if you run “SELECT @a” , ProxySQL will disable multiplexing for that client and will always use the same backend connection.)，proxysql的负载方式目前仅为加权轮询一种(经过作者确认的)，并无其他机制。

后端server宕机测试

对上述架构用sysbench做只读测试，过程中关闭(service mysqld stop)某一台server (测试均在mysql-monitor_enabled=false的前提下)

测试命令alias sysbench_test='sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua\--mysql-user=dm --mysql-password='dm' --mysql-port=6033\--mysql-host=192.168.1.34 --oltp-tables-count=16\--num-threads=8 run --oltp-skip-trx=on --oltp-read-only=on'1234

结果如下[root@db3 ~]# sysbench_test sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 8Random number generator seed is 0 and will be ignored

Threads started!

ALERT: mysql_drv_query() for query 'SELECT c FROM sbtest16 WHERE id=4964' failed: 2013 Lost connection to MySQL server during query

ALERT: mysql_drv_query() for query 'SELECT c FROM sbtest12 WHERE id=4954' failed: 2013 Lost connection to MySQL server during query

ALERT: mysql_drv_query() for query 'SELECT c FROM sbtest7 WHERE id BETWEEN 4645 AND 4645+99' failed: 2013 Lost connection to MySQL server during query123456789101112

不是说好的自动重连/重执行吗？为毛报错了呢(atlas有同样问题)但是两者通过上述的sysbench命令所抛出的错误经过多次比较，有不同：

proxysql报错就一种

failed: 2013 Lost connection to MySQL server during query

atlas报错则是两种

failed: 2013 Lost connection to MySQL server during

failed: 1317 Query execution was interrupted

是不是说明re-execute有效呢？(no，其实这个思路就不对)1234567

测试方法错了：其实关闭后端mysql服务来测试“reconnect”特性应该说从本质上就是不对的，mysql正常关闭会kill掉其上的所有processlist，我们可以用mysql-client来做一些验证mysql> select @@hostname;

+------------+| @@hostname |

+------------+| db1 |

+------------+1 row in set (0.00 sec)

**通过别的tty重启该mysql**

mysql> select @@hostname;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 4Current database: *** NONE ***

+------------+| @@hostname |

+------------+| db1 |

+------------+1 row in set (0.00 sec)12345678910111213141516171819

可以看到mysql-client是具有重连(reconnect)功能的，然后我们来做一个kill掉mysql-client线程(也就是mysql在关闭时会kill掉所有线程)的操作mysql> select @@hostname;

+------------+| @@hostname |

+------------+| db1 |

+------------+1 row in set (0.00 sec)

**在mysql服务端查询并kill掉这个链接**

mysql> select @@hostname;

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> 1234567891011

我们看到了之前sysbenche测试过程中关闭一台从库时的报错。也就是说这个场景下这个报错不是proxysql的‘reconnect’机制的问题，倒更像是‘re-execute’机制的问题。而kill操作应该理解为是mysql“主动”的动作，而非“异常”，所以这就不符合proxysql 的”re-execute”特性了，故而会报错。

应该换一种方式来进行这个测试：模拟网络层无法通信的异常

我们在两台slave上通过iptables阻断peoxysql到本机的3306端口来模拟无法链接和链接中断异常在sysbench开始之后再重启iptables已经建立的链接会被保留，所以新规则要放到第一条的位置

-A INPUT -s 192.168.1.34 -p tcp -m tcp –dport 3306 -j DROP

首先：启动sysbench只读测试

然后：在测试结束前，修改db1的iptables，禁止来自proxysql的请求进入

然后：观察sysbench的输出和proxysql.log的输出

结果：sysbench等待很长时间，依然无法完成，同时，proxysql也不会把db1标记为SHUNNED

proxysql.log输出：2016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 133 on 192.168.1.4:33062016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 136 on 192.168.1.4:33062016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 135 on 192.168.1.4:33062016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 137 on 192.168.1.4:33062016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 138 on 192.168.1.4:33062016-09-02 11:37:54 MySQL_Session.cpp:49:kill_query_thread(): [WARNING] KILL QUERY 134 on 192.168.1.4:3306可以看到在mysql-default_query_timeout=30000，30s之后proxysql确实kill了超时的语句。30s这个时间可以通过执行‘service iptables restart’的时间和proxysql.log里日志的时间来判定，这个设置30s是有效的

然后，直接对后端node(db1)进行同样的测试，发现结果是一样的，sysbench等待很长时间依然无法完成也无报错。

从这一点似乎可以判定在这个测试方法下，未达到预期结果的原因可能不在proxysql，而在sysbench本身。

通过对重启iptables之后的通信进行抓包(分别对针对proxysql和mysql在这个测试场景下进行抓包),命令如下：

~]# date; service iptables restart; tcpdump -i em2 host 192.168.1.35 and port 3306 and host not 192.168.1.10 -w /tmp/sysbench-proxysql-network-issue.pacp

~]# date; service iptables restart; tcpdump -i em2 host 192.168.1.34 and port 3306 and host not 192.168.1.10 -w /tmp/sysbench-proxysql-network-issue.pacp

发现，sysbench“一直”在重传由于iptables新规则而无法返回的几个请求，所以就成了“无穷尽等待的样子” (atlas 在这个场景下有同样问题)

照理说，proxysql kill掉了一些查询，会返回给sysbench错误，但为什么sysbench并未将错误展示出来呢(可能是因为re-execute机制)

最后，通过跟作者沟通，发现是由于没开启monitor模块，导致proxysql无法检测到后端出了什么类型的错误，也就无法执行对应各种后端错误的一些操作(之前我是特意关掉了monitor模块)1

测试对prepare语句的支持

好多框架用prepare语句来避免SQL注入等安全问题，同时能在MySQL解析查询方面降低一些开销，所以对于prepare语句支持与否也很重要

首先要从MySQL协议层面了解prepare语句，分为两种(参考)Prepared Statements in Application Programs

或者称为BINARY protocol

抓包分析一次prepare,set,execute过程，可以观察到，客户端发送的是COM_STMT_PREPARE

Prepared Statements in SQL Scripts

或者称为TEXT protocol

抓包分析一次prepare,set,execute过程，可观察到客户端发送的是COM_QUERY

关于prepare语句，作者给出的回复和计划如下：

这是我在实验1.2.1版本时跟作者的沟通。现在已经发布1.3.5了，从1.3版本开始,两种协议都支持了。但是在设置字符集方面，对于binary protocol 的prepare set names xxx和普通query(set names ‘utf8’ collate ‘utf8_general_ci’)(注意加了校对规则，不加校对规则可以)语句还无法正确处理(例如，php的laravel框架默认就是以第一种形式设置字符集)。

修正：经测试1.3.7版本，以上两prepare语句的小bug都已解决MySQL supports two type of prepared statements:

using API

using SQL Further details here

SQL support currently not supported either, because PREPARE doesn’t disable multiplexing, so it is possible that ProxySQL sends PREPARE in a connection, and EXECUTE into another.

SQL support will be fixed in v1.2.3 , see #684 .

API support is planned in ProxySQL 1.3 .

五、connection-pool和multiplexing(并对比Atlas)

首先来精确理解一下这两个词，依作者回复They are very related to each other

Connection pool is a cache of connections that can be reused.

Multiplexing is a technique to reuse these connections.

后期逐渐了解到了proxysql的multiplexing。连接池是一个共享的池子里面有跟后端创建好的一些连接，一个服务端脚本的执行过程中可能有多次sql请求：atlas的连接池：从连接池中分配一条连接，在整个脚本执行期间一直占有并使用该连接直到脚本执行完毕，然后将连接归还连接池

而proxysql的连接池：脚本执行过程中的每次sql请求都经历一次分配连接—-使用—-归还的过程

很显然，对于连接池的使用效率上来说，理论上多数情况下proxysql的方式会更加高效并且与DB间维护的链接数量会更少

测试场景：10条`select * from test.test_table`，10条`select @@hostname`；

ProxySQL/Atlas IP 192.168.1.35；两个读节点IP分别为192.168.1.37和192.168.1.38；

每次测试完之前重启ProxySQL/Atlas；1234

分两次测试，第一次测试脚本如下(每条命令一次连接)!#/bin/shfor i in {1..10};do

mysql -uuser -p'passwd' -P6033 -h192.168.1.35 -e "select @@hostname;" #select @xxx，会禁用multiplexingdonefor i in {1..10};do

mysql -uuser -p'passwd' -P6033 -h192.168.1.35 -e "select id from test.test_table;" #普通查询done1234567

第二次测脚本如下(一次连接，执行所有命令)for i in {1..10};do

query1 += 'select @@hostname;' #select @xxx，会禁用multiplexing

query2 += 'select id from test.test_table;' #普通查询doneecho $query1$query2 | mysql -uuser -p'passwd' -P6033 -h192.168.1.3512345

对ProxySQL进行测试分析

通过tcpdump抓包wireshark分析，通过ProxySQL对20条查询的路由情况及其和后端MySQL之间的建立连接情况(通过ProxySQL上的原端口号)来分析连接池和禁用multiplexing的情况。结果汇总如下

第一次测试(每条命令一次连接)select @xxx #会禁用multiplexing

1.35源端口 1.35---->1.37 42094 42096 42097 42099 42102 (转发5次) 1.35---->1.38 37971 37974 37976 37977 37979 (转发5次)

普通查询 1.35源端口 1.35---->1.37 42105 (转发3次) 1.35---->1.38 37980 (转发7次) 123456789

第二次测试(一次连接，执行所有命令)select @xxx #会禁用multiplexing

1.35源端口 1.35---->1.37 (转发0次) 1.35---->1.38 37817 (转发10次)

普通查询 1.35源端口 1.35---->1.37 (转发0次) 1.35---->1.38 37817 (转发10次) 123456789

对比来看Atlas的分时分析

第一次测试(每条命令一次连接)select @xxx

1.35源端口 1.35---->1.37 (转发0次)

1.35---->1.38 38405 38407 38409 38411 38413 (转发10次)

38415 38417 38419 38421 38423普通查询 1.35源端口 1.35---->1.37 (转发0次)

1.35---->1.38 38385 38387 38389 38391 38393 (转发10次)

38395 38397 38399 38401 3840312345678910

第二次测脚本如下(一次连接，执行所有命令)select @xxx

1.35源端口 1.35---->1.37 42435 (转发5次)

1.35---->1.38 38312 (转发5次)普通查询 1.35源端口 1.35---->1.37 42435 (转发5次)

1.35---->1.38 38312 (转发5次)123456789

由上面测试分析结果可以明显的看到ProxySQL的负载均衡策略是基于权重的轮询，但并不是严格的逐条轮询。而且可以看到在一次连接之内：由于某些语句(select @xx 或者prepare语句)导致ProxySQL自动关闭multiplexing之后，在本次链接之后的所有语句都会被路由到同一台MySQL

atlas做的仅仅是轮询转发，他不会去区分查询类型(例如有些查询是要路由到后端唯一的MySQL)

而且，在`第一次测试(每条命令一次连接)`中，atlas将所有的20条请求都路由到了1.38这台MySQL，并且每次都要新建连接(并没有用到其连接池)12345

六、proxysql对后端server健康检查可以主动来看一下其相关参数：| mysql-monitor_enabled | true || mysql-monitor_history | 600000 || mysql-monitor_connect_interval | 120000 || mysql-monitor_connect_timeout | 200 || mysql-monitor_ping_interval | 60000 || mysql-monitor_ping_max_failures | 3 || mysql-monitor_ping_timeout | 100 |

这两种检测的区别，据作者回复和抓包分析，总结如下：Ping is done using mysql_ping()

通过抓包分析，是通过现有的链接(连接池中)发送一个Request Ping语句

Connect is done using mysql_real_connect()

这是一个客户端到服务器建立链接，登录，退出登录，关闭链接的完整过程

这两个函数的返回值不同，可以帮助proxysql理解与后端链接除了哪些问题。

模拟场景，验证以上各设置并加深理解其故障检测机制：

两个前提

修改web1 MySQL配置文件中max_connections = 3；重启web1 MySQL，在其他tty打开几个MySQL链接以确保proxysql无法链接该MySQL。同时在proxysql server上打开抓包功能tcpdump -i em2 host 192.168.1.4 and port 3306 -w /tmp/web_shun.pcap，然后通过wireshark对比上面各值进行分析。

附件：数据包文件

通过分析可验以以下设置的有效性mysql-monitor_connect_intervalmysql-monitor_ping_intervalmysql-monitor_ping_max_failuresmysql-shun_recovery_time_secmysql-ping_interval_server_msec1

8在web1和web5在proxysql均为online状态下；

无客户端访问ProxySQL，即只验证monitor模块的行为

可以被动

被动就是将全局变量‘mysql-monitor_enabled’置为false，这种情况下，后端server故障后，proxysql不会主动探知，而是在有请求被“正常”路由到该server之后才会在runtime层更改该server状态为‘SHUNNED’ 或者重新变为‘ONLINE’。这个过程，应用无感知(表现为mysql-client命令和sysbench均无报错)

相关变量为| mysql-shun_on_failures | 5 || mysql-shun_recovery_time_sec | 60 || mysql-query_retries_on_failure | 1 || mysql-connect_retries_on_failure | 5 || mysql-connect_retries_delay | 1 || mysql-connection_max_age_ms | 0 || mysql-connect_timeout_server | 1000 || mysql-connect_timeout_server_max | 10000 |1

七、关于proxysql和mysql中的最大连接数

首先明确下MySQL中的最大连接数由max_connections变量控制，proxysql中的最大连接数有两个方面的设置mysql_users.max_connections和mysql_servers.max_connectionsif the mysql_servers.max_connections is reached, some of the connections will wait until the mysql-connect_timeout_server_max is reached. then proxysql will return error message SQLSTATE[HY000]: General error: 9001 Max connect timeout reached while reaching hostgroup 1 after 10000ms.

if the mysql_users.max_connections is reached, then the client will see the error message 1040: Too many connections

If backend’s global variable ‘max_connections’ is reached and proxysql has no ConnFree with the backend, then client can accomplish the connection with proxysql but all queries will return ERROR 1040 (#HY00): Too many connections, and the monitor will consider this backend is shun and will be loged to the proxysql.log

八、据我所知的bug和不足

bug：

1. 对于prepare语句(Prepared Statements in Application Programs，见上文解释。例如laravel框架的prepare语句)，如果在backend上更改了表结构，在一些情况下会导致proxysql返回如下错误

SQLSTATE[HY000]: General error: 2057 A stored procedure returning result sets of different size was called. This is not supported by libmysql

2. 对于prepare语句的set names xxx语句还不能有效处理

3. 对于非prepare语句的set names xxx collate xxxx还不能有效处理

不足：

1. 前后端账号分离：可以多个frontend用户对应一个或少数banckend用户，简化后端MySQL上的授权操作，尤其是项目多而且之间库表关联较紧密时；还能将前端用户和stats中相关表或者错误日志关联，非常方便的在众多接入到ProxySQL的项目中定位到异常项目。

2. 错误日志中对于SQL类错误只记录了SQL语句和MySQL返回的错误信息，没有关联到用户和库表，在众多项目中仅凭一条SQL语句去寻找源头难度可想而知(其实这个跟第一条是有关联的)。

3. 通过admin接口进行管理时，在作出多处修改时，无法一次性将修改应用到内存，也无法一次性将所有修改保存至磁盘。例如对mysql_servers, mysql_users,mysql_query_rules都

做了修改，就得分别使这三个方面的更改生效和保存至磁盘。反过来同理，假如做了一些更改但是最终想丢弃这些更改也无法方便的将其回复到修改之前的状态。

总结

稳定性方面：目前我们已经部分业务切换到了ProxySQL上，运行一直稳定，未遇到cpu负载高或者占用内存多等情况。

运维/DBA友好：借助于ProxySQL的错误日志，我们发现了之前没注意到的一些SQL问题(如主键重复类的问题等)。还通过stats库里的相关表定位了一些问题。

性能方面：也强于atlas(参见文章)

个人觉得，解决掉上述几点bug和不足的话(且不说可能会出现的其他feature)，ProxySQL就会更加强大和完美。

转自：http://blog.csdn.net/kai404

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/AllinToyou/article/detail/230638