运维首页 > 数据运维 > Cetus配置测试

Cetus配置测试

2023年 7月 15日 23.4k 0

简介Cetus是由C语言开发的关系型数据库MySQL的中间件，主要提供了一个全面的数据库访问代理功能。Cetus连接方式与MySQL基本兼容，应用程序几乎不用修改即可通过Cetus访问数据库，实现了数据库层的水平扩展和高可用。Cetus分为读写分离和分库两个版本。本篇主要实现读写分离，后端是MariaDB Galera Cluster而不是主从复制。大致模样如下：cetus-a.png详情参阅：https://github.com/Lede-Inc/cetus

开始安装

1.1 下载安装安装依赖包

[root@Linuxea-VM-Node145 /data]# yum install cmake gcc glib2-devel flex libevent-devel mysql-devel gperftools-libs -y

克隆源码

[root@Linuxea-VM-Node145 /data]# git clone https://github.com/Lede-Inc/cetus.git
正克隆到 'cetus'...
remote: Counting objects: 1640, done.
remote: Compressing objects: 100% (119/119), done.
remote: Total 1640 (delta 104), reused 140 (delta 73), pack-reused 1448
接收对象中: 100% (1640/1640), 6.25 MiB | 1.26 MiB/s, done.
处理 delta 中: 100% (1002/1002), done.
[root@Linuxea-VM-Node145 /data]# cd cetus
[root@Linuxea-VM-Node145 /data/cetus]# mkdir build && cd build

创建安装目录

[root@Linuxea-VM-Node145 /data/cetus/build]# mkdir /usr/local/cetus

开始编译

[root@Linuxea-VM-Node145 /data/cetus/build]# cmake ../ -DCMAKE_BUILD_TYPE=Debug -DCMAKE_INSTALL_PREFIX=/usr/local/cetus -LinuxeaIMPLE_PARSER=ON
[root@Linuxea-VM-Node145 /data/cetus/build]# make && make install

1.2 准备配置文件

[root@Linuxea-VM-Node145 /data/cetus/build]# cd /usr/local/cetus/conf
[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# cp proxy.conf.example proxy.conf
[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# cp users.json.example users.json

启动命令如下，先不启动，配置配置文件

bin/cetus --defaults-file=conf/proxy.conf [--conf-dir＝/home/user/cetus_install/conf/]

准备配置文件

[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# egrep -v "^$|^#" proxy.conf
[cetus]
# For mode-switch
daemon = true
disable-sharding-mode=true

# Loaded Plugins
plugins=proxy,admin
plugin-dir=/usr/local/cetus/lib/cetus/plugins
# Proxy Configuration, For eaxmlpe: MySQL master and salve host ip are both 192.0.0.1
# 监听的IP和端口
proxy-address=0.0.0.0:4006
# 读写后端(主库)的IP和端口
proxy-backend-addresses=10.10.240.202:3306
# 只读后端(从库)的IP和端口
proxy-read-only-backend-addresses=10.10.240.203:3306,10.10.240.146:3306

# Admin Configuration
# 管理模块的IP和端口
admin-address=0.0.0.0:7001 
#管理模块的用户名
admin-username=admin    
#管理模块的密码明文
admin-password=admin    

# Backend Configuration, use test db and username created
#默认数据库
#default-db=
#默认登陆用户名
#default-username=cetus_app
default-username=Ccetus
# 设置刚启动的连接数量
default-pool-size=100 
max-resp-len=10485760
long-query-time=100

# default-pool-size= ，设置刚启动的连接数量
# max-pool-size=，设置最大连接数量
# max-resp-size=，设置最大响应大小，一旦超过此大小，则会报错给客户端
# enable-client-compress=[true|false]，支持客户端压缩
# enable-tcp-stream=[true|false]，启动tcp stream，无需等响应收完就发送给客户端
# master-preferred=[true|false]，除非注释强制访问从库，否则一律访问主库
# reduce-connections=True #[true|false]，自动减少过多的后端连接数量

# File and Log Configuration, put log in /data and marked by proxy port, /data/cetus neeLinuxea to be created manually and has rw authority for cetus os user
max-open-files = 65536
pid-file = cetus6001.pid
plugin-dir=lib/cetus/plugins
# 日志文件路
log-file=/data/cetus/cetus_6001.log 
# info | message | warning | error | critical(default)
log-level=debug 


##Check salve delay 主从检测 （只适用于主从）
# disable-threaLinuxea=false
# check-slave-delay=true
# slave-delay-down=5
# slave-delay-recover=1

# For trouble
keepalive=true
verbose-shutdown=true
log-backtrace-on-crash=true

1.3 准备用户授权这里的用户文件可以在后面进行添加，不需要修改也可以

[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# cat users.json
{
    "users":    [{
            "user":    "Ccetus",
            "client_pwd":    "password",
            "server_pwd":    "password"
        }, {
            "user":    "scetus",
            "client_pwd":    "password",
            "server_pwd":    "password"
}
[root@Linuxea-VM-Node145 /usr/local/cetus/conf]#

注意：其中Ccetus用户需要在数据库进行创建,而后填写在配置中.在后端数据库进行授权

GRANT ALL  ON *.* TO 'Ccetus'@'%' identified by 'password';  
GRANT SELECT  ON *.* TO 'scetus'@'%' identified by 'password';  
GRANT ALL ON linuxea.* TO 'linuxea'@'10.10.0.96' IDENTIFIED BY 'password';

修改配置文件权限

[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# chmod 660 proxy.conf

开始启动

[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# ../bin/cetus --defaults-file=/usr/local/cetus/conf/proxy.conf

管理端安装配置

安装一个mysql的客户端

[root@Linuxea-VM-Node145 ~]# cat  /etc/yum.repos.d/MariaDB.repo
> [mariadb]
> name = MariaDB
> baseurl = http://yum.mariadb.org/10.1/centos7-amd64
> gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
> gpgcheck=1
> EOF
[root@Linuxea-VM-Node145 ~]# yum install MariaDB-client -y

查看后端

mysql --prompt="proxy> " --comments -h10.10.240.145 -P7001 -uadmin -padmin

2.1 管理端使用说明

  • 注意：1，创建用户在Cetus中，账号大体可以分两类：
  • 监控后端DB状态的账号(default-username)
  • 业务端访问DB存取数据的账号。这两类账号都需要：1 在Cetus的users.json中配置 2 在MySQL中进行授权。 第2类账号的MySQL端授权根据业务访问的库表进行授权就行，注意授权时，IP授予Cetus的IP（user@'cetus-ip'）。MySQL目前支持IP白名单(show allow_ip proxy;)用来限制客户端连接Cetus，目前已经支持IP段的设置。IP白名单不设置，则对客户端IP无限制，如果设置了，则只允许在IP白名单中的客户端IP访问。

2，cetus创建动态的增加账号信息

mysql --prompt="admiin> " --comments -h10.10.240.145 -P7001 -uadmin -padmin

3，用户创建这里的 update 命令虽然是“update”，但是当没哟匹配的条件时，功能类似“insert”

update user_pwd set password='password' where user='mark';
update app_user_pwd set password='password' where user='mark';

创建完成就会写入到文件中：

[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# cat users.json
{
    "users":    [{
            "user":    "Ccetus",
            "client_pwd":    "password",
            "server_pwd":    "password"
        }, {
            "user":    "scetus",
            "client_pwd":    "password",
            "server_pwd":    "password"
        }, {
            "user":    "mark",
            "client_pwd":    "password",
            "server_pwd":    "password"
        }]
}

4，mariadb创建用户

MariaDB [(none)]> GRANT ALL  ON *.* TO 'mark'@'10.10.240.145' identified by 'password'; 
Query OK, 0 rows affected (0.01 sec)

6，拒绝所有并放行需要放行

admiin> add deny_ip proxy *;
Query OK, 0 rows affected (0.00 sec)

7，Proxy创建白名单

[root@Linuxea-VM-Node145 ~]# mysql --prompt="proxy> " --comments -h10.10.240.145 -P7001 -uadmin -padmin
Welcome to the MariaDB monitor.  CommanLinuxea end with ; or \g.
Your MySQL connection id is 1
Server version: cetus 1.0.0 admin

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

proxy> add allow_ip proxy mark@10.0.1.61;
Query OK, 1 row affected (0.00 sec)

proxy> show allow_ip proxy;
+--------+----------------+
| Plugin | Address        |
+--------+----------------+
| proxy  | mark@10.0.1.61 |
+--------+----------------+
1 row in set (0.00 sec)
proxy>

8，查看后端主机

proxy>  select * from backends;
+-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+
| backend_ndx | address            | state | type | slave delay | uuid | idle_conns | used_conns | total_conns |
+-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+
| 1           | 10.10.240.202:3306 | up    | rw   | NULL        | NULL | 2          | 0          | 2           |
| 2           | 10.10.240.203:3306 | up    | ro   | 0           | NULL | 74         | 0          | 74          |
| 3           | 10.10.240.146:3306 | up    | ro   | 0           | NULL | 54         | 24         | 78          |
+-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+
3 rows in set (0.00 sec)

proxy>

压测

cetus与maxsclae比较,maxsclae使用readwritesplit

用同一个库进行测试，db20180528_3337先在收据库授权：

GRANT all  ON db20180528_3337.* TO 'mark'@'%' identified by 'password';  
flush privileges;

  • cetus写入:

    sysbench /usr/share/sysbench/oltp_update_index.lua \
--mysql-host=10.10.240.145 \
--mysql-port=4006 \
--mysql-user=mark \
--mysql-password=password \
--mysql-db=db20180528_3337 \
--db-driver=mysql \
--tables=10 \
--table-size=100000 \
--report-interval=10 \
--threads=50 \
--time=60 \
--skip-trx=on \
run

    oltp_update_index

    [ 10s ] thds: 50 tps: 3498.36 qps: 3498.36 (r/w/o: 0.00/1181.95/2316.41) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 4159.14 qps: 4159.14 (r/w/o: 0.00/1387.71/2771.43) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 4188.54 qps: 4188.54 (r/w/o: 0.00/1391.28/2797.26) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 2151.35 qps: 2151.35 (r/w/o: 0.00/699.29/1452.07) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 4083.64 qps: 4083.64 (r/w/o: 0.00/1371.75/2711.89) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 4022.16 qps: 4022.16 (r/w/o: 0.00/1341.62/2680.54) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
SQL statistics:
  queries performed:
      read:                            0
      write:                           73766
      other:                           147325
      total:                           221091
  transactions:                        221091 (3682.08 per sec.)
  queries:                             221091 (3682.08 per sec.)
  ignored errors:                      0      (0.00 per sec.)
  reconnects:                          0      (0.00 per sec.)

General statistics:
  total time:                          60.0433s
  total number of events:              221091

Latency (ms):
       min:                                    0.34
       avg:                                   13.57
       max:                                 4359.03
       95th percentile:                       64.47
       sum:                              3000618.25

Threads fairness:
  events (avg/stddev):           4421.8200/172.02
  execution time (avg/stddev):   60.0124/0.01

    监控如下：cetus-update.png读取：

    sysbench /usr/share/sysbench/oltp_read_only.lua     \
--mysql-host=10.10.240.145 \
--mysql-port=4006 \
--mysql-user=mark \
--mysql-password=password \
--mysql-db=db20180528_3337 \
--db-driver=mysql \
--tables=10 \
--table-size=100000 \
--report-interval=10 \
--threads=50 \
--time=60 \
--skip-trx=on \
run

    oltp_read_only.lua

    [ 10s ] thds: 50 tps: 1856.11 qps: 26022.04 (r/w/o: 26022.04/0.00/0.00) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 1924.47 qps: 26939.92 (r/w/o: 26939.92/0.00/0.00) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 1863.81 qps: 26091.55 (r/w/o: 26091.55/0.00/0.00) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 1779.71 qps: 24918.53 (r/w/o: 24918.53/0.00/0.00) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 1771.68 qps: 24799.98 (r/w/o: 24799.98/0.00/0.00) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 1766.63 qps: 24729.84 (r/w/o: 24729.84/0.00/0.00) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
SQL statistics:
  queries performed:
      read:                            1535506
      write:                           0
      other:                           0
      total:                           1535506
  transactions:                        109679 (1826.73 per sec.)
  queries:                             1535506 (25574.19 per sec.)
  ignored errors:                      0      (0.00 per sec.)
  reconnects:                          0      (0.00 per sec.)

General statistics:
  total time:                          60.0393s
  total number of events:              109679

Latency (ms):
       min:                                   11.65
       avg:                                   27.36
       max:                                  146.12
       95th percentile:                       31.37
       sum:                              3000763.51

Threads fairness:
  events (avg/stddev):           2193.5800/35.56
  execution time (avg/stddev):   60.0153/0.01

    监控如下：centus-read.png

  • maxsclae:写入update:

    sysbench /usr/share/sysbench/oltp_update_index.lua \
--mysql-host=10.10.240.145 \
--mysql-port=3337 \
--mysql-user=mark \
--mysql-password=password \
--mysql-db=db20180528_3337 \
--db-driver=mysql \
--tables=10 \
--table-size=100000 \
--report-interval=10 \
--threads=50 \
--time=60 \
--skip-trx=on \
run

    oltp_update_index.lua

    [ 10s ] thds: 50 tps: 4059.71 qps: 4059.71 (r/w/o: 0.00/1343.11/2716.61) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 4107.74 qps: 4107.74 (r/w/o: 0.00/1351.08/2756.66) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 4111.42 qps: 4111.42 (r/w/o: 0.00/1376.64/2734.78) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 3931.22 qps: 3931.22 (r/w/o: 0.00/1313.37/2617.85) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 4245.93 qps: 4245.93 (r/w/o: 0.00/1421.71/2824.22) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 4058.91 qps: 4058.91 (r/w/o: 0.00/1342.77/2716.14) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
SQL statistics:
  queries performed:
      read:                            0
      write:                           81518
      other:                           163691
      total:                           245209
  transactions:                        245209 (4083.81 per sec.)
  queries:                             245209 (4083.81 per sec.)
  ignored errors:                      0      (0.00 per sec.)
  reconnects:                          0      (0.00 per sec.)

General statistics:
  total time:                          60.0424s
  total number of events:              245209

Latency (ms):
       min:                                    0.32
       avg:                                   12.24
       max:                                  657.95
       95th percentile:                       69.29
       sum:                              3000433.84

Threads fairness:
  events (avg/stddev):           4904.1800/197.99
  execution time (avg/stddev):   60.0087/0.01

    监控如下：maxscale-update.png读取:

    sysbench /usr/share/sysbench/oltp_read_only.lua     \
--mysql-host=10.10.240.145 \
--mysql-port=3337 \
--mysql-user=mark \
--mysql-password=password \
--mysql-db=db20180528_3337 \
--db-driver=mysql \
--tables=10 \
--table-size=100000 \
--report-interval=10 \
--threads=50 \
--time=60 \
--skip-trx=on \
run

    oltp_read_only.lua

    [ 10s ] thds: 50 tps: 2746.72 qps: 38489.80 (r/w/o: 38489.80/0.00/0.00) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 2947.88 qps: 41265.98 (r/w/o: 41265.98/0.00/0.00) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 3281.79 qps: 45944.62 (r/w/o: 45944.62/0.00/0.00) lat (ms,95%): 23.95 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 2680.51 qps: 37528.49 (r/w/o: 37528.49/0.00/0.00) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 2672.41 qps: 37409.39 (r/w/o: 37409.39/0.00/0.00) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 2700.45 qps: 37811.13 (r/w/o: 37811.13/0.00/0.00) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00
SQL statistics:
  queries performed:
      read:                            2384956
      write:                           0
      other:                           0
      total:                           2384956
  transactions:                        170354 (2835.47 per sec.)
  queries:                             2384956 (39696.57 per sec.)
  ignored errors:                      0      (0.00 per sec.)
  reconnects:                          0      (0.00 per sec.)

General statistics:
  total time:                          60.0777s
  total number of events:              170354

Latency (ms):
       min:                                    6.73
       avg:                                   17.62
       max:                                  325.16
       95th percentile:                       37.56
       sum:                              3001080.47

Threads fairness:
  events (avg/stddev):           3407.0800/1140.34
  execution time (avg/stddev):   60.0216/0.03

    监控如下:maxscale-read.png

张二河
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

张二河

这个人很懒，什么都没有留下～

文章8551 评论0