开发者社区> 绛云> 正文

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

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

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23582 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
22359 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
16663 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
22540 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
19810 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
11998 0
+关注
绛云
阿里云数据库专家服务组PostgreSQL dba,热爱pg,正在学习pg内核的相关的代码,欢迎大家和我讨论,一起共同成长
15
文章
1
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载