开发者社区> 绛云> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

postgresql成本因子调整

简介: postgresql因子的调整主要有这四个cpu_tuple_cost,random_page_cost,cpu_index_tuple_cost,cpu_operator_cost。这四个因子的值影响着计划中的cost值,这个因子大小的是否正确很大程度的影响着执行计划走的是否正确
+关注继续查看

    postgresql因子的调整主要有这四个cpu_tuple_cost,random_page_cost,cpu_index_tuple_cost,cpu_operator_cost。这四个因子的值影响着计划中的cost值,这个因子大小的是否正确很大程度的影响着执行计划走的是否正确。
    1.安装systemtap
    需要用到的工具是systemtap,在安装systemtap前请先进行如下操作
    
image

之后安装相同版本号的kernel-devel,kernel-debuginfo,kernel-debuginfo-common(注意包括小版本号一定要相同)

image

image

    至于去哪找这些rpm包我是从这里找到的http://rpm.pbone.net/index.php3。如果实在找不到同版本的这3个安装包建议换个版本的系统吧。之后安装systemtap,

image
   
 如果安装完后应该是有这些包

image
  

  下面输入这个

    [root@bogon ~]# stap -ve 'probe begin { log("hello world") exit() }'测试下是否成功安装
    
image
  

 2.安装postgresql

    systemtap安装成功后开始安装postgresql,我安装的版本的9.3,源码安装的
    
image

    安装步骤,就解压后到目录中执行
    [postgres@bogon postgresql-9.3.0]$ ./configure --prefix=/home/postgres/data --enable-dtrace

image
  
  之后就是
    [postgres@bogon postgresql-9.3.0]$ make && make install
    完成后运行下
    [postgres@bogon bin]$ pg_config --configure
    
image
   

 确保一定要有--enable-dtrace这个东西,这个就是允许探针访问pg的选项。

    接下来就是初始化数据库:initdb -D 数据库目录。
    [postgres@bogon data]$ pg_ctl start -D pgdata 启动数据库。
 
   3.调整因子前环境的配置
    创建测试用表:
    create table test(a int,b int);
    插入数据:(需要数据有一定的离散性)
    insert into test select random()1000000,random()1000000 from generate_series(1,1000000);
    创建索引:
    create index index_a on test(a);
    更新统计信息:
    analyse;
    将数据写入磁盘:
    checkpoint;
    关闭数据库:
    pg_ctl stop -D pgdata/
    清空操作系统缓存
    [root@bogon ~]# sync; echo 3 > /proc/sys/vm/drop_caches
    下面就要使用stap了,具体代码如下
    stap -e '
    global a
    probe process("/home/postgres/data/bin/postgres").mark("query__start") {
        delete a
        println("query__start ", user_string($arg1), "pid:", pid())
    }
    probe vfs.read.return {
        t = gettimeofday_ns() - @entry(gettimeofday_ns())
        # if (execname() == "postgres" && devname != "N/A")
        a[pid()] <<< t
    }
    probe process("/home/postgres/data/bin/postgres").mark("query__done") {
        if (@count(a[pid()])) 
        printdln("**", pid(), @count(a[pid()]), @avg(a[pid()]))
        println("query__done ", user_string($arg1), "pid:", pid())
        if (@count(a[pid()])) {
        println(@hist_log(a[pid()]))
        #println(@hist_linear(a[pid()],1024,4096,100))
    }
  delete a
}' -x 2808
    这个a是一个数组中存着,pid号,多少次读取数据块,读取数据块的平均时间,stap监控着postgres这个进程,vfs.read.return这个函数是得到块设备读入一个数据块的时间,并存入数组a中,最后返回pid号和读取块的次数和平均读取时间。-x 后面的数字要设置为你监控的pid号
    postgres=# select pg_backend_pid();
    
image
    
    4.调整成本因子
    
    4.1调整全表扫描的相关因子
    首先得到会话的pid号之后运行前面呢段代码:
    image

  

  确定模块是否加载上了

    [root@bogon ~]# lsmod |grep stap
    image

   

 执行如下语句:

    postgres=# explain (analyze,verbose,costs,buffers,timing) select * from test ;
    
image

    查看下stap的结果:
    
image
  

  最终看的结果就是a的数组中存储的值就是18782**4439**148019(第一项pid号,第二个读取块次数,第三个读取一次的平均时间),呢个value和count的图就是读取时间和次数的直方图。

    
    
    补充一个图还要查下pg_class中的relpages的数量,这个是整个表存储的页面数
    postgres=# select relpages from pg_class where relname='test';
    
image
   

 全表扫描的计算公式是什么呢?

    cost(真实执行时间)=seqscan_costrelpages+cpu_tuple_costlines(表的行数)
    根据上面的图可以得到如下结果:
    cost=677.602
    seq_page_cost=148019*10^-6
    relpages=4425
    lines=1000000
    由这些值可得
    cpu_tuple_cost=0.000022617925
    seqscan_cost=0.148019
    这两个值先记录下来。
    我们可以set seq_page_cost=0.148019;set cpu_tuple_cost=0.000022617925;在进行全表扫描看下结果。
    重复之前操作清理下系统缓存:
    [root@bogon ~]# sync; echo 3 > /proc/sys/vm/drop_caches
    重启数据库;
    设置这两个因子
    重新查询下之前的语句
    
image
   

 你会发现cost值是不是和真实值很接近呢。cpu_tuple_cost和sea_page_cost已经校准完成啦。

    
    4.2调整索引相关的成本因子
    重复之前调全表扫描因子的步骤,这里不再赘述。
    首先设置cpu_tuple_cost=1,random_page_cost=1,cpu_index_tuple_cost=1,cpu_operator_cost=1。设置的原因之后在进行解释。
    
image
  

  关闭全表扫描set enable_seqscan =off;set enable_bitmapscan =off;

    执行如下语句:explain (analyze,verbose,costs,buffers,timing) select * from test where a>489963;
    
image
    
    stap结果:
    
image

    索引扫描的代价计算公式是:
    cost=random_page_costblocks+cpu_tuple_costline1+cpu_index_tuple_costline2+cpu_operator_costline2
    cost=9283.272
    random_page_cost=1.534983
    line1=508823
    line2=?
    blocks=?
    这个line2就和之前设置所有因子都是1就有关系了,我们可以通过调整cpu_operator_cost为2其他因子不变,两个得到的cost结果相减就可以得到line2了,blocks也是同理通过调整random_page_cost来得到结果。
    image

   

 line2=2041453-1532460=508993

    
    
image
 
 blocks=1538282-1532460=5822
    2cpu_operator_cost=cpu_index_tuple_cost
    综上可得出
    cpu_operator_cost=0.00548671355463809587
    cpu_index_tuple_cost=0.01097342710927619174
    random_page_cost=1.534983
    我们再来重新调整完因子后再测试下语句
    
image
   

 和真实值有点偏差,可能是因为systemtap会占用一定的cpu资源,我这个是关闭systemtap进行测试的。

    
    
    最终结果是
    cpu_operator_cost=0.00548671355463809587
    cpu_index_tuple_cost=0.01097342710927619174
    random_page_cost=1.534983
    cpu_tuple_cost=0.000022617925
    seqscan_cost=0.148019

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
postgresql常见命令及操作
  pgsql已经更新到beta11了,不同版本的服务器启动或相关命令、配置可能会有不同,所以得根据pg版本进行操作。下面记录一些工作中常用到的一些操作,主要包括服务启动、备份/恢复数据、数据目录迁移、常见操作命令 本文环境: postgres : v10.3 os: MAC 虽然已经在kong部署中介绍了postgres的部署,为了行文连贯性,这里再简单记录下pg的启动相关命令。
3162 0
postgresql 排它约束
--pg支持 EXCLUSION Constraint,排它约束是约束中定义的操作计算结果为false,则不允许插入 Exclusion constraints ensure that if...
936 0
PostgreSQL
引用:http://baike.baidu.com/view/28196.htm 相关数据库:http://baike.baidu.com/taglist?tag=%CA%FD%BE%DD%BF%E2&tagfromview    PostgreSQL PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES,版本 4.2为基础的对象关系型数据库管理系统(ORDBMS)。
1138 0
+关注
绛云
阿里云数据库专家服务组PostgreSQL dba,热爱pg,正在学习pg内核的相关的代码,欢迎大家和我讨论,一起共同成长
15
文章
1
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载