Postgresql PL /Pproxy 分布式数据库代理

最新推荐文章于 2024-05-31 09:59:00 发布

wangdeng1314

最新推荐文章于 2024-05-31 09:59:00 发布

阅读量2.8k

点赞数

分类专栏： PostgreSql 文章标签： postgresql 数据库 transactions user login query

PostgreSql 专栏收录该内容

37 篇文章 1 订阅

订阅专栏

参考文档：
1)德哥博客：http://blog.163.com/digoal@126/blog/static/163877040201192535630895/
2)/wiki介绍：http://wiki.postgresql.org/wiki/PL/Proxy#Documentation（包含PLProxy下载地址，用法介绍，用法举例）
3)德哥视频：http://pan.baidu.com/share/link?shareid=5060&uk=201377085

遗留问题：
1）plproxy 建立Cluster 后可以运行在RUN ALL下，这个时候如果链接pgbouncer期中一个节点失效
那么，运行在整个节点上的函数全部都会报错，对于分布式数据库需要从分库查询数据时就会影响
到可以正常工作的库。
2）从各个文章中发现有介绍怎么在代理库上建立代理函数，查询当前库的版本和节点配置信息以及
他们的链接参数有什么意义。

PostgreSQL分布式设计:

三层结构 :

1. 路由层(几乎无限扩展)

主角: plproxy,postgresql

2. 连接池层(几乎无限扩展)

主角: pgbouncer

3. 数据层(几乎无限扩展)

主角: postgresql

扩展方式:

1. 路由层扩展:

路由层包含了数据层的连接配置(FDW或函数),plproxy语言写的函数壳(内置路由算法),这些基本上是静态数据,所以扩展非常方便.

添加服务器就行了.

2. 连接池层扩展:

连接池层扩展加服务器.

3. 数据层扩展:

数据层扩展,添加服务器,通过流复制增加数据节点,结合路由算法重分布数据(建议路由算法2^n取模),

物理分布:

1. 路由层和连接池层尽量靠近部署.可以考虑部署在同一台物理机.

2. 数据层尽量每个节点一台物理机.

环境需求:

   
   
    
    CentOS 5.7 x64
   
   
   
   
    
    flex-2.5.35
   
   
   
   
    
    PostgreSQL-9.1.1
   
   
   
   
    
    plproxy-2.2
   
   
   
   
    
    pgfincore-v1.1
   
   
   
   
    
    libevent-1.4.14b-stable
   
   
   
   
    
    pgbouncer 1.4.2

测试环境描述:

   
   
    
    1. pgbench : 172.16.3.176
   
   
   
   
    
    2. pgbouncer on pgbench HOST : 
   
   
   
   
    
    172.16.3.176:1998(
   
   
   
   
    
    proxy0 = host=172.16.3.150 dbname=proxy port=1921 pool_size=16
   
   
   
   
    
    proxy1 = host=172.16.3.39 dbname=proxy port=1921 pool_size=16
   
   
   
   
    
    proxy2 = host=172.16.3.40 dbname=proxy port=1921 pool_size=16
   
   
   
   
    
    proxy3 = host=172.16.3.33 dbname=proxy port=1921 pool_size=16
   
   
   
   
    
    )
   
   
   
   
    
    3. PostgreSQL 数据节点 : 172.16.3.150:1921/digoal, 172.16.3.39:1921/digoal, 172.16.3.40:1921/digoal, 172.16.3.33:1921/digoal
   
   
   
   
    
    4. PostgreSQL plproxy节点 : 172.16.3.150:1921/proxy, 172.16.3.39:1921/proxy, 172.16.3.40:1921/proxy, 172.16.3.33:1921/proxy
   
   
   
   
    
    5. pgbouncers on plproxy HOST : 
   
   
   
   
    
    172.16.3.150:1999, 172.16.3.39:1999, 172.16.3.40:1999, 172.16.3.33:1999(
   
   
   
   
    
    digoal0 = host=172.16.3.150 dbname=digoal port=1921 pool_size=8
   
   
   
   
    
    digoal1 = host=172.16.3.39 dbname=digoal port=1921 pool_size=8
   
   
   
   
    
    digoal2 = host=172.16.3.40 dbname=digoal port=1921 pool_size=8
   
   
   
   
    
    digoal3 = host=172.16.3.33 dbname=digoal port=1921 pool_size=8
   
   
   
   
    
    )

环境搭建:

1. 编译安装flex-2.5.35

./configure && make && make install

2. 编译安装PostgreSQL-9.1.1

   
   
    
    ./configure --prefix=/opt/pgsql --with-pgport=1921 --with-perl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-segsize=64
   
   
   
   
    
    gmake world
   
   
   
   
    
    gmake install-world

3. 编译安装plproxy-2.2

   
   
    
    mv plproxy-2.2 postgresql-9.1.1/contrib/plproxy-2.2
   
   
   
   
    
    make PG_CONFIG=/path/to/pg_config
   
   
   
   
    
    make install PG_CONFIG=/path/to/pg_config

4. 编译安装pgfincore-v1.1 （这个扩展可以把表放到操作系统的Cache中，提高性能）

   
   
    
    mv pgfincore-v1.1 postgresql-9.1.1/contrib/pgfincore-v1.1
   
   
   
   
    
    cp pgfincore.control /
   
   
   
   
    
    make clean
   
   
   
   
    
    make
   
   
   
   
    
    su
   
   
   
   
    
    make install PG_CONFIG=/path/to/pg_config

5. 编译安装libevent-1.4.14b-stable

./configure && make && make install

6. 编译安装pgbouncer 1.4.2

./configure --prefix=/opt/pgbouncer && make && make install

注意：如果找不到相关的 xxx.so文件，请把该文件的目录放入文件，最后执行 /sbin/ldconfig –v命令

vim /etc/ld.so.conf

include /etc/ld.so.conf.d/*.conf

/usr/local/lib/

配置:

1. 配置数据节点信息

新建用户 : digoal(nosuperuser)

新建表空间 : digoal, digoal_idx

新建数据库 : digoal

digoal库新建schema : digoal

digoal库新建过程语言 : plpgsql

允许代理函数连的连接池所在的服务器连接上面新建的用户和库 : 配置pg_hba.conf

配置postgresql.conf : 略

2. 配置plproxy节点信息(本例与数据节点共用PostgreSQL数据库实例集群)

新建用户 : proxy(nosuperuser)

新建表空间 : 共用digoal

新建数据库 : proxy

proxy库新建schema : proxy

plproxy初始化 : 用超级用户执行/opt/pgsql/share/contrib/plproxy.sql 创建handler function,language,validator function,foreign data wrapper

更改language可信度(否则普通用户不可以使用plproxy语言) :

   
   
    
      proxy=> \c proxy postgres
   
   
   
   
    
      update pg_language set lanpltrusted='t' where lanname='plproxy';

这个操作是为了途方便, 生产中请使用超级用户创建plproxy函数, 把execute权限赋予给普通用户.

3. 配置pgfincore

连接到数据节点\c digoal postgres

CREATE EXTENSION pgfincore;

4. 配置pgbouncer(代理函数连的连接池)

4台主机都需要配置,

   
   
    
    postgres@db-digoal-> cat config1999.ini 
   
   
   
   
    
    [databases]
   
   
   
   
    
    digoal0 = host=172.16.3.150 dbname=digoal port=1921 pool_size=8
   
   
   
   
    
    digoal1 = host=172.16.3.39 dbname=digoal port=1921 pool_size=8
   
   
   
   
    
    digoal2 = host=172.16.3.40 dbname=digoal port=1921 pool_size=8
   
   
   
   
    
    digoal3 = host=172.16.3.33 dbname=digoal port=1921 pool_size=8
   
   
   
   
    
    [pgbouncer]
   
   
   
   
    
    pool_mode = transaction
   
   
   
   
    
    listen_port = 1999
   
   
   
   
    
    unix_socket_dir = /opt/pgbouncer/etc
   
   
   
   
    
    listen_addr = *
   
   
   
   
    
    auth_type = md5
   
   
   
   
    
    auth_file = /opt/pgbouncer/etc/users1999.txt
   
   
   
   
    
    logfile = /dev/null
   
   
   
   
    
    pidfile = /opt/pgbouncer/etc/pgbouncer1999.pid
   
   
   
   
    
    max_client_conn = 10000
   
   
   
   
    
    reserve_pool_timeout = 0
   
   
   
   
    
    server_reset_query =
   
   
   
   
    
    admin_users = pgbouncer_admin
   
   
   
   
    
    stats_users = pgbouncer_guest
   
   
   
   
    
    ignore_startup_parameters = extra_float_digits
   
   
   
   
    
    postgres@db-digoal-> cat users1999.txt 
   
   
   
   
    
    "digoal" "md5462f71c79368ccf422f8a773ef40074d"

5. 配置pgbouncer(pgbench连的连接池)

   
   
    
    postgres@db-digoal-> cat config1998.ini 
   
   
   
   
    
    [databases]
   
   
   
   
    
    proxy0 = host=172.16.3.150 dbname=proxy port=1921 pool_size=16
   
   
   
   
    
    proxy1 = host=172.16.3.39 dbname=proxy port=1921 pool_size=16
   
   
   
   
    
    proxy2 = host=172.16.3.40 dbname=proxy port=1921 pool_size=16
   
   
   
   
    
    proxy3 = host=172.16.3.33 dbname=proxy port=1921 pool_size=16
   
   
   
   
    
    [pgbouncer]
   
   
   
   
    
    pool_mode = transaction
   
   
   
   
    
    listen_port = 1998
   
   
   
   
    
    unix_socket_dir = /opt/pgbouncer/config
   
   
   
   
    
    listen_addr = *
   
   
   
   
    
    auth_type = md5
   
   
   
   
    
    auth_file = /opt/pgbouncer/config/users.txt
   
   
   
   
    
    logfile = /dev/null
   
   
   
   
    
    pidfile = /opt/pgbouncer/config/pgbouncer1998.pid
   
   
   
   
    
    max_client_conn = 1500
   
   
   
   
    
    reserve_pool_timeout = 0
   
   
   
   
    
    server_reset_query = 
   
   
   
   
    
    admin_users = pgbouncer_admin
   
   
   
   
    
    stats_users = pgbouncer_guest
   
   
   
   
    
    ignore_startup_parameters = extra_float_digits

数据节点, 创建测试表, 插入测试数据:

   
   
    
    proxy=# \c digoal digoal
   
   
   
   
    
    create table user_info
   
   
   
   
    
    (userid int,
   
   
   
   
    
    engname text,
   
   
   
   
    
    cnname text,
   
   
   
   
    
    occupation text,
   
   
   
   
    
    birthday date,
   
   
   
   
    
    signname text,
   
   
   
   
    
    email text,
   
   
   
   
    
    qq numeric,
   
   
   
   
    
    crt_time timestamp without time zone,
   
   
   
   
    
    mod_time timestamp without time zone
   
   
   
   
    
    );
   
   
   
   
    
    

   
   
   
   
    
    create table user_login_rec
   
   
   
   
    
    (userid int,
   
   
   
   
    
    login_time timestamp without time zone,
   
   
   
   
    
    ip inet
   
   
   
   
    
    );
   
   
   
   
    
    

   
   
   
   
    
    create table user_logout_rec
   
   
   
   
    
    (userid int,
   
   
   
   
    
    logout_time timestamp without time zone,
   
   
   
   
    
    ip inet
   
   
   
   
    
    );

测试数据 :

0号节点

   
   
    
    insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
   
   
   
   
    
    select generate_series(0,50000000,4),
   
   
   
   
    
    'digoal.zhou',
   
   
   
   
    
    '德哥',
   
   
   
   
    
    'DBA',
   
   
   
   
    
    '1970-01-01'
   
   
   
   
    
    ,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
   
   
   
   
    
    'digoal@126.com',
   
   
   
   
    
    276732431,
   
   
   
   
    
    clock_timestamp(),
   
   
   
   
    
    NULL
   
   
   
   
    
    ;

1号节点

   
   
    
    insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
   
   
   
   
    
    select generate_series(1,50000000,4),
   
   
   
   
    
    'digoal.zhou',
   
   
   
   
    
    '德哥',
   
   
   
   
    
    'DBA',
   
   
   
   
    
    '1970-01-01'
   
   
   
   
    
    ,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
   
   
   
   
    
    'digoal@126.com',
   
   
   
   
    
    276732431,
   
   
   
   
    
    clock_timestamp(),
   
   
   
   
    
    NULL
   
   
   
   
    
    ;

2号节点

   
   
    
    insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
   
   
   
   
    
    select generate_series(2,50000000,4),
   
   
   
   
    
    'digoal.zhou',
   
   
   
   
    
    '德哥',
   
   
   
   
    
    'DBA',
   
   
   
   
    
    '1970-01-01'
   
   
   
   
    
    ,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
   
   
   
   
    
    'digoal@126.com',
   
   
   
   
    
    276732431,
   
   
   
   
    
    clock_timestamp(),
   
   
   
   
    
    NULL
   
   
   
   
    
    ;

3号节点

   
   
    
    insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
   
   
   
   
    
    select generate_series(3,50000000,4),
   
   
   
   
    
    'digoal.zhou',
   
   
   
   
    
    '德哥',
   
   
   
   
    
    'DBA',
   
   
   
   
    
    '1970-01-01'
   
   
   
   
    
    ,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
   
   
   
   
    
    'digoal@126.com',
   
   
   
   
    
    276732431,
   
   
   
   
    
    clock_timestamp(),
   
   
   
   
    
    NULL
   
   
   
   
    
    ;

所有节点 :

   
   
    
    set work_mem='2048MB';
   
   
   
   
    
    set maintenance_work_mem='2048MB';
   
   
   
   
    
    alter table user_info add constraint pk_user_info primary key (userid) using index tablespace digoal_idx;

开发:

数据节点 :

实体函数:

登录函数 :

   
   
    
    create or replace function f_user_login 
   
   
   
   
    
    (i_userid int,
   
   
   
   
    
    OUT o_userid int,
   
   
   
   
    
    OUT o_engname text,
   
   
   
   
    
    OUT o_cnname text,
   
   
   
   
    
    OUT o_occupation text,
   
   
   
   
    
    OUT o_birthday date,
   
   
   
   
    
    OUT o_signname text,
   
   
   
   
    
    OUT o_email text,
   
   
   
   
    
    OUT o_qq numeric
   
   
   
   
    
    )
   
   
   
   
    
    as $BODY$
   
   
   
   
    
    declare
   
   
   
   
    
    begin
   
   
   
   
    
    select userid,engname,cnname,occupation,birthday,signname,email,qq
   
   
   
   
    
    into o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qq
   
   
   
   
    
    from user_info where userid=i_userid;
   
   
   
   
    
    insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
   
   
   
   
    
    return;
   
   
   
   
    
    end;
   
   
   
   
    
    $BODY$
   
   
   
   
    
    language plpgsql;

退出函数 :

   
   
    
    create or replace function f_user_logout
   
   
   
   
    
    (i_userid int,
   
   
   
   
    
    OUT o_result int
   
   
   
   
    
    )
   
   
   
   
    
    as $BODY$
   
   
   
   
    
    declare
   
   
   
   
    
    begin
   
   
   
   
    
    insert into user_logout_rec (userid,logout_time,ip) values (i_userid,now(),inet_client_addr());
   
   
   
   
    
    o_result := 0;
   
   
   
   
    
    return;
   
   
   
   
    
    exception 
   
   
   
   
    
    when others then
   
   
   
   
    
    o_result := 1;
   
   
   
   
    
    return;
   
   
   
   
    
    end;
   
   
   
   
    
    $BODY$
   
   
   
   
    
    language plpgsql;

代理节点 :

创建server

   
   
    
    CREATE SERVER digoal FOREIGN DATA WRAPPER plproxy
   
   
   
   
    
    OPTIONS (
   
   
   
   
    
    connection_lifetime '1800',
   
   
   
   
    
    disable_binary '1',
   
   
   
   
    
    p0 'dbname=digoal0 host=127.0.0.1 port=1999 client_encoding=UTF8',
   
   
   
   
    
    p1 'dbname=digoal1 host=127.0.0.1 port=1999 client_encoding=UTF8',
   
   
   
   
    
    p2 'dbname=digoal2 host=127.0.0.1 port=1999 client_encoding=UTF8',
   
   
   
   
    
    p3 'dbname=digoal3 host=127.0.0.1 port=1999 client_encoding=UTF8'
   
   
   
   
    
    );

创建user mapping

   
   
    
    CREATE USER MAPPING FOR proxy SERVER digoal
   
   
   
   
    
    OPTIONS (user 'digoal', password 'digoal');

赋权server

grant usage on foreign server digoal to proxy;

创建代理函数:

   
   
    
    \c proxy proxy
   
   
   
   
    
    登录函数: 
   
   
   
   
    
    CREATE OR REPLACE FUNCTION f_user_login(i_userid int,
   
   
   
   
    
    OUT o_userid int,
   
   
   
   
    
    OUT o_engname text,
   
   
   
   
    
    OUT o_cnname text,
   
   
   
   
    
    OUT o_occupation text,
   
   
   
   
    
    OUT o_birthday date,
   
   
   
   
    
    OUT o_signname text,
   
   
   
   
    
    OUT o_email text,
   
   
   
   
    
    OUT o_qq numeric
   
   
   
   
    
    )
   
   
   
   
    
    as $BODY$
   
   
   
   
    
        CLUSTER 'digoal';
   
   
   
   
    
        RUN ON i_userid;
   
   
   
   
    
        target digoal.f_user_login;
   
   
   
   
    
    $BODY$
   
   
   
   
    
    LANGUAGE plproxy;
   
   
   
   
    
    

   
   
   
   
    
    退出函数: 
   
   
   
   
    
    create or replace function f_user_logout
   
   
   
   
    
    (i_userid int,
   
   
   
   
    
    OUT o_result int
   
   
   
   
    
    )
   
   
   
   
    
    as $BODY$
   
   
   
   
    
        CLUSTER 'digoal';
   
   
   
   
    
        RUN ON i_userid;
   
   
   
   
    
        target digoal.f_user_logout;
   
   
   
   
    
    $BODY$
   
   
   
   
    
    language plproxy;

pgbench压力测试 :

   
   
    
    postgres@db-digoal-> cat begin.sh
   
   
   
   
    
    #!/bin/bash
   
   
   
   
    
    nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/login.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy0 >>./login_0.log 2>&1 &
   
   
   
   
    
    nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/login.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy1 >>./login_1.log 2>&1 &
   
   
   
   
    
    nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/login.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy2 >>./login_2.log 2>&1 &
   
   
   
   
    
    nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/login.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy3 >>./login_3.log 2>&1 &
   
   
   
   
    
    nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/logout.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy0 >>./logout_0.log 2>&1 &
   
   
   
   
    
    nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/logout.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy1 >>./logout_1.log 2>&1 &
   
   
   
   
    
    nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/logout.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy2 >>./logout_2.log 2>&1 &
   
   
   
   
    
    nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/logout.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy3 >>./logout_3.log 2>&1 &
   
   
   
   
    
    postgres@db-digoal-> cat login.sql
   
   
   
   
    
    \setrandom userid 0 50000000
   
   
   
   
    
    SELECT f_user_login(:userid);
   
   
   
   
    
    postgres@db-digoal-> cat logout.sql
   
   
   
   
    
    \setrandom userid 0 50000000
   
   
   
   
    
    SELECT f_user_logout(:userid);

cat .pgpass 略

测试结果 :

   
   
    
    postgres@db-digoal-> cat login_0.log 
   
   
   
   
    
    transaction type: Custom query
   
   
   
   
    
    scaling factor: 1
   
   
   
   
    
    query mode: extended
   
   
   
   
    
    number of clients: 8
   
   
   
   
    
    number of threads: 8
   
   
   
   
    
    duration: 180 s
   
   
   
   
    
    number of transactions actually processed: 665468
   
   
   
   
    
    tps = 3695.624216 (including connections establishing)
   
   
   
   
    
    tps = 3695.675102 (excluding connections establishing)
   
   
   
   
    
    statement latencies in milliseconds:
   
   
   
   
    
            0.002366        \setrandom userid 0 50000000
   
   
   
   
    
            2.158355        SELECT f_user_login(:userid);
   
   
   
   
    
    postgres@db-digoal-> cat login_1.log 
   
   
   
   
    
    transaction type: Custom query
   
   
   
   
    
    scaling factor: 1
   
   
   
   
    
    query mode: extended
   
   
   
   
    
    number of clients: 8
   
   
   
   
    
    number of threads: 8
   
   
   
   
    
    duration: 180 s
   
   
   
   
    
    number of transactions actually processed: 665288
   
   
   
   
    
    tps = 3694.720318 (including connections establishing)
   
   
   
   
    
    tps = 3694.777428 (excluding connections establishing)
   
   
   
   
    
    statement latencies in milliseconds:
   
   
   
   
    
            0.002289        \setrandom userid 0 50000000
   
   
   
   
    
            2.159063        SELECT f_user_login(:userid);
   
   
   
   
    
    postgres@db-digoal-> cat login_2.log 
   
   
   
   
    
    transaction type: Custom query
   
   
   
   
    
    scaling factor: 1
   
   
   
   
    
    query mode: extended
   
   
   
   
    
    number of clients: 8
   
   
   
   
    
    number of threads: 8
   
   
   
   
    
    duration: 180 s
   
   
   
   
    
    number of transactions actually processed: 645371
   
   
   
   
    
    tps = 3585.275832 (including connections establishing)
   
   
   
   
    
    tps = 3585.340161 (excluding connections establishing)
   
   
   
   
    
    statement latencies in milliseconds:
   
   
   
   
    
            0.002341        \setrandom userid 0 50000000
   
   
   
   
    
            2.225684        SELECT f_user_login(:userid);
   
   
   
   
    
    postgres@db-digoal-> cat login_3.log 
   
   
   
   
    
    transaction type: Custom query
   
   
   
   
    
    scaling factor: 1
   
   
   
   
    
    query mode: extended
   
   
   
   
    
    number of clients: 8
   
   
   
   
    
    number of threads: 8
   
   
   
   
    
    duration: 180 s
   
   
   
   
    
    number of transactions actually processed: 732428
   
   
   
   
    
    tps = 4068.985625 (including connections establishing)
   
   
   
   
    
    tps = 4069.059175 (excluding connections establishing)
   
   
   
   
    
    statement latencies in milliseconds:
   
   
   
   
    
            0.002358        \setrandom userid 0 50000000
   
   
   
   
    
            1.960421        SELECT f_user_login(:userid);
   
   
   
   
    
    postgres@db-digoal-> cat logout_0.log 
   
   
   
   
    
    transaction type: Custom query
   
   
   
   
    
    scaling factor: 1
   
   
   
   
    
    query mode: extended
   
   
   
   
    
    number of clients: 8
   
   
   
   
    
    number of threads: 8
   
   
   
   
    
    duration: 180 s
   
   
   
   
    
    number of transactions actually processed: 774532
   
   
   
   
    
    tps = 4302.899259 (including connections establishing)
   
   
   
   
    
    tps = 4302.942647 (excluding connections establishing)
   
   
   
   
    
    statement latencies in milliseconds:
   
   
   
   
    
            0.002279        \setrandom userid 0 50000000
   
   
   
   
    
            1.853726        SELECT f_user_logout(:userid);
   
   
   
   
    
    postgres@db-digoal-> cat logout_1.log 
   
   
   
   
    
    transaction type: Custom query
   
   
   
   
    
    scaling factor: 1
   
   
   
   
    
    query mode: extended
   
   
   
   
    
    number of clients: 8
   
   
   
   
    
    number of threads: 8
   
   
   
   
    
    duration: 180 s
   
   
   
   
    
    number of transactions actually processed: 773650
   
   
   
   
    
    tps = 4298.002332 (including connections establishing)
   
   
   
   
    
    tps = 4298.047243 (excluding connections establishing)
   
   
   
   
    
    statement latencies in milliseconds:
   
   
   
   
    
            0.002308        \setrandom userid 0 50000000
   
   
   
   
    
            1.855774        SELECT f_user_logout(:userid);
   
   
   
   
    
    postgres@db-digoal-> cat logout_2.log 
   
   
   
   
    
    transaction type: Custom query
   
   
   
   
    
    scaling factor: 1
   
   
   
   
    
    query mode: extended
   
   
   
   
    
    number of clients: 8
   
   
   
   
    
    number of threads: 8
   
   
   
   
    
    duration: 180 s
   
   
   
   
    
    number of transactions actually processed: 752476
   
   
   
   
    
    tps = 4180.389824 (including connections establishing)
   
   
   
   
    
    tps = 4180.437536 (excluding connections establishing)
   
   
   
   
    
    statement latencies in milliseconds:
   
   
   
   
    
            0.002331        \setrandom userid 0 50000000
   
   
   
   
    
            1.908120        SELECT f_user_logout(:userid);
   
   
   
   
    
    postgres@db-digoal-> cat logout_3.log 
   
   
   
   
    
    transaction type: Custom query
   
   
   
   
    
    scaling factor: 1
   
   
   
   
    
    query mode: extended
   
   
   
   
    
    number of clients: 8
   
   
   
   
    
    number of threads: 8
   
   
   
   
    
    duration: 180 s
   
   
   
   
    
    number of transactions actually processed: 855429
   
   
   
   
    
    tps = 4752.346080 (including connections establishing)
   
   
   
   
    
    tps = 4752.383363 (excluding connections establishing)
   
   
   
   
    
    statement latencies in milliseconds:
   
   
   
   
    
            0.002288        \setrandom userid 0 50000000
   
   
   
   
    
            1.677890        SELECT f_user_logout(:userid);

小结 :

每秒处理事务数 : 32581

平均耗时 : 1.974879125 毫秒.

数据库节点平均负载 : 6

数据库节点平均空闲 : 78%

另一个测试的测试数据 :

8000W数据分布到4个节点,根据PK进行更新。

更新SQL请求频率 : 33027 次每秒

平均SQL处理耗时 : 1.9352235 毫秒

从测试结果来看，PLPROXY部署的环境得到的性能提升是超线性的。4台服务器得到的性能大于等于4倍单节点数据库的性能。

wangdeng1314

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
Postgresql PL /Pproxy 分布式数据库代理

参考文档：1)德哥博客：http://blog.163.com/digoal@126/blog/static/163877040201192535630895/2)/wiki介绍：http://wiki.postgresql.org/wiki/PL/Proxy#Documentation（包含PLProxy下载地址，用法介绍，用法举例）3)德哥视频：http://pan.baidu.c
复制链接

扫一扫