linuxea:Cetus配置测试


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

详情参阅:https://github.com/Lede-Inc/cetus

I. 开始安装

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=<num> ,设置刚启动的连接数量
# max-pool-size=<num>,设置最大连接数量
# max-resp-size=<num>,设置最大响应大小,一旦超过此大小,则会报错给客户端
# 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 

II. 管理端安装配置

安装一个mysql的客户端

[root@Linuxea-VM-Node145 ~]# cat << EOF > /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中,账号大体可以分两类:
    1 监控后端DB状态的账号(default-username)
    2 业务端访问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> 

III. 压测

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

监控如下:

读取:

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

监控如下:

  • 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

监控如下:

读取:

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

监控如下:

1 分享

您可以选择一种方式赞助本站

支付宝扫码赞助

支付宝扫码赞助

日期: 2018-06-01分类: Mariadb

标签: mariadb, 读写分离

发表评论