postgresql成本因子调整

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

之后安装相同版本号的kernel-devel，kernel-debuginfo，kernel-debuginfo-common（注意包括小版本号一定要相同）

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

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

  

  下面输入这个

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

  

 2.安装postgresql

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

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

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

   

 确保一定要有--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();
    

    
    4.调整成本因子
    
    4.1调整全表扫描的相关因子
    首先得到会话的pid号之后运行前面呢段代码：
    

  

  确定模块是否加载上了

    [root@bogon ~]# lsmod |grep stap
    

   

 执行如下语句：

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

    查看下stap的结果：
    

  

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

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

   

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

    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
    重启数据库；
    设置这两个因子
    重新查询下之前的语句
    

   

 你会发现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。设置的原因之后在进行解释。
    

  

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

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

    
    stap结果：
    

    索引扫描的代价计算公式是：
    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来得到结果。
    

   

 line2=2041453-1532460=508993

    
    

 
 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
    我们再来重新调整完因子后再测试下语句
    

   

 和真实值有点偏差，可能是因为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