注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PL/Proxy 之二: Dynamic records  

2014-09-18 19:25:17|  分类: PG性能优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

       关于 PL/Proxy 搭建参考   PL/Proxy 之一: 使用外部表 SQL/MED搭建 PL/Proxy , 本文紧接着上篇 blog 介绍 PL/Proxy 使用方面, 由于 PL/Proxy 使用通过函数封装,如果每张表的插入,查询操作都单独写函数太烦琐,这里介绍传递 SQL 的用法。

回顾下环境信息,数据库节点信息

proxy
db0
db1

备注: proxy 库为 proxy 节点, db0, db1 为数据节点,均在同一台虚拟机的一个 PostgreSQL 实例上。

创建三类函数

 func_ddl(sql text) :      传递 ddl 函数
 func_dml(sql text)       传递 dml 函数
 func_query(sql text)   传递 查询sql 函数

备注:当然仅这三个函数不足以包含所有数据库操作。
 
一 proxy 节点执行

--DDL 函数
CREATE OR REPLACE FUNCTION  func_ddl(sql text)
RETURNS SETOF text AS $$
    CLUSTER 'proxy_srv';
    RUN ON ALL;
$$ LANGUAGE plproxy;

--DML 函数
CREATE OR REPLACE FUNCTION  func_dml(sql text)
RETURNS SETOF text AS $$
    CLUSTER 'proxy_srv';
    RUN ON ANY;
$$ LANGUAGE plproxy;

--query 函数
CREATE OR REPLACE FUNCTION func_query(sql text)
RETURNS SETOF RECORD AS $$
    CLUSTER 'proxy_srv';
    RUN ON ALL;
$$ LANGUAGE plproxy;


二 db0,db1 数据节点执行

--DDL 函数
CREATE OR REPLACE FUNCTION  func_ddl(sql text)
RETURNS  integer AS $$
begin
execute sql;
return 1;
end;
$$ LANGUAGE plpgsql;

--DML 函数
CREATE OR REPLACE FUNCTION  func_dml(sql text)
RETURNS  integer AS $$
begin
execute sql;
return 1;
end;
$$ LANGUAGE plpgsql;

--query 函数
CREATE OR REPLACE FUNCTION func_query(sql text)
RETURNS SETOF RECORD AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN EXECUTE sql
    LOOP
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;


三测试
--3.1 DDL 函数测试

--proxy 结点执行
proxy=> select func_ddl('create table tbl_proxy1(id int4,name text,create_time timestamp(0) without time zone default clock_timestamp())');
NOTICE:  PL/Proxy: dropping stale conn
NOTICE:  PL/Proxy: dropping stale conn
 func_ddl
----------
 1
 1
(2 rows

--db0,db1 验证
db0=> \dt tbl_proxy1
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | tbl_proxy1 | table | proxy
(1 row)

db0=> \c db1
You are now connected to database "db1" as user "proxy".

db1=> \dt tbl_proxy1
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | tbl_proxy1 | table | proxy
(1 row)


--3.2 DML 函数测试

--proxy 节点
proxy=> select func_dml('insert into tbl_proxy1 (id,name) values(1,''a'')');
 func_dml
----------
 1
(1 row)

proxy=> select func_dml('insert into tbl_proxy1 (id,name) values(2,''b'')');
 func_dml
----------
 1
(1 row)

proxy=> select func_dml('insert into tbl_proxy1 (id,name) values(3,''c'')');
 func_dml
----------
 1
(1 row)

--数据节点验证
db1=> select * from tbl_proxy1 ;
 id | name |     create_time     
----+------+---------------------
  1 | a    | 2014-09-18 18:45:08
  2 | b    | 2014-09-18 18:45:55
(2 rows)

db1=> \c db0
You are now connected to database "db0" as user "proxy".

db0=> select * from tbl_proxy1 ;
 id | name |     create_time     
----+------+---------------------
  3 | c    | 2014-09-18 18:45:58
(1 row)


--3.3 query 测试

proxy=> SELECT * FROM func_query('SELECT * FROM tbl_proxy1;') AS (id integer, name text,create_time timestamp(0) without time zone );            
id | name |     create_time     
----+------+---------------------
  2 | b    | 2014-09-18 18:55:13
  3 | c    | 2014-09-18 18:55:17
  1 | a    | 2014-09-18 18:55:07
(3 rows)

备注:这里需要定义返回列类型.

四 性能分析
     我们想插入 10 万数据简单测试这种模式性能,由于 generate_series() 函数调用后数据只落在一个数据节点,这里通过写个 shell 脚本插入 10 万数据。

--生成 insert 语句的脚本

[pg93@db1 script]$ cat insert.sh
#!/bin/bash

for ((i=1;i<=100000;i++)); do
echo "select func_dml('insert into tbl_proxy1(id,name) values(${i},''${i}_test'')');";
done

备注:插入 10 万数据。

--清理 db0,db1 数据

proxy=> select func_ddl('truncate table tbl_proxy1;');
 func_ddl
----------
 1
 1
(2 rows)


--导入数据

[pg93@db1 script]$ ./insert.sh | psql proxy proxy > insert.out


--数据验证

db1=> select count(*) from tbl_proxy1;
 count
-------
 50020
(1 row)

db1=> \c db0
You are now connected to database "db0" as user "proxy".
db0=> select count(*) from tbl_proxy1;
 count
-------
 49980
(1 row)

备注:数据分布较均匀。

--创建索引

proxy=> select func_ddl('create unique index uk_proxy_id on tbl_proxy1 using btree (id);');
 func_ddl
----------
 1
 1
(2 rows)

db0=> \d tbl_proxy1
                        Table "public.tbl_proxy1"
   Column    |              Type              |         Modifiers        
-------------+--------------------------------+---------------------------
 id          | integer                        |
 name        | text                           |
 create_time | timestamp(0) without time zone | default clock_timestamp()
Indexes:
    "uk_proxy_id" UNIQUE, btree (id)

db0=> \c db1
You are now connected to database "db1" as user "proxy".
db1=> \d tbl_proxy1
                        Table "public.tbl_proxy1"
   Column    |              Type              |         Modifiers        
-------------+--------------------------------+---------------------------
 id          | integer                        |
 name        | text                           |
 create_time | timestamp(0) without time zone | default clock_timestamp()
Indexes:
    "uk_proxy_id" UNIQUE, btree (id)

备注: 索引已在数据节点创建。

--proxy 节点上查询

proxy=> explain analyze SELECT * FROM func_query('SELECT id,name FROM tbl_proxy1 where id=1;') AS (id integer, name text);
                                                  QUERY PLAN                                                 
--------------------------------------------------------------------------------------------------------------
 Function Scan on func_query  (cost=0.25..10.25 rows=1000 width=36) (actual time=1.803..1.805 rows=1 loops=1)
 Total runtime: 1.847 ms
(2 rows)


--数据节点查询

db1=> explain analyze SELECT id,name FROM tbl_proxy1 where id=1;
                                                       QUERY PLAN                                                       
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using uk_proxy_id on tbl_proxy1  (cost=0.29..4.31 rows=1 width=14) (actual time=0.032..0.034 rows=1 loops=1)
   Index Cond: (id = 1)
 Total runtime: 0.086 ms
(3 rows)

备注: 同样的查询, proxy 节点查询需要 1.847 ms,  数据节点查询仅需  0.086 ms,性能差别较大,所以从这个测试来看,传递 SQL 的这三个函数在性能方面会有下降,不建议使用这种传递 SQL 的方式,建议函数调用方式。


五 参考
  评论这张
 
阅读(406)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016