PostgreSQL成本因子校准

环境部署：

　　　　1.CentOS7最小安装+PG5.5源代码（使用最新版本的PG，在tap监控时存在问题）

　　　　2.编译PG代码的依赖（开启DebugInfo包搜索）：systemtap、systemtap-sdt-devel.x86_64、readline-static.x86_64、zlib-static.x86_64、kernel-devel-version【依赖perl-core.x86_64】、kernel-debuginfo-version、kernel-debuginfo-common-version

　　　　3.安装PG时的配置项‘./configure --enable-dtrace --enable-debug --enable-cassert’

　　　　4.测试systemtap安装情况：stap -v -e 'probe vfs.read {printf("read performed\n"); exit()}'

 

校准参数：

　　　　 seq_page_cost(stap), cpu_tuple_cost(公式)

步骤：

1.创建测试表，插入测试数据，并刷新统计信息

　　①create table tbl_cost_align (id int, info text, crt_time timestamp);

　　②insert into tbl_cost_align select (random()*2000000000)::int, md5(random()::text), clock_timestamp() from generate_series(1,100000);

　　③insert into tbl_cost_align select (random()*2000000000)::int, md5(random()::text), clock_timestamp() from generate_series(1,10000000);

　　④analyze tbl_cost_align;

2.CHECKPOINT，并关闭数据库

3.将操作系统的缓存刷入硬盘  sync; echo 3 > /proc/sys/vm/drop_caches

4.以非0号cpu亲和启动数据库 taskset -c 1 /usr/local/pgsql/bin/postgres >/dev/null 2>&1

5.连接数据库，查看pid：select pg_backend_pid();

6.启动监控代码：

taskset -c 0 stap -e '
global a
probe process("/usr/local/pgsql/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("/usr/local/pgsql/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 18026

View Code

7.执行SQL：explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;

8.查询结束，观察stap及explain输出结果：

                                                              QUERY PLAN                                          
                     
------------------------------------------------------------------------------------------------------------------
---------------------
 Seq Scan on public.tbl_cost_align  (cost=0.00..195391.91 rows=10099891 width=45) (actual time=5.557..17236.458 ro
ws=10100000 loops=1)
   Output: id, info, crt_time
   Buffers: shared read=94393【注意这个read指的是未命中shared buffer, 如果是命中的话会有hit=?】
 Planning time: 46.697 ms
 Execution time: 27467.293 ms
(5 rows)

Explain输出

2558**81109**13444【io平均响应时间】
query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2558
    value |-------------------------------------------------- count
      512 |                                                       0
     1024 |                                                       0
     2048 |@@@@@@@@@@@                                        14011
     4096 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  61227
     8192 |                                                     694
    16384 |                                                      96
    32768 |@@@                                                 4787
    65536 |                                                     263
   131072 |                                                       4
   262144 |                                                       0
   524288 |                                                       0
  1048576 |                                                       0
  2097152 |                                                       1
  4194304 |                                                      14
  8388608 |                                                       1
 16777216 |                                                       9
 33554432 |                                                       1
 67108864 |                                                       1
134217728 |                                                       0
268435456 |                                                       0

stap输出

shuai=#  show seq_page_cost;
 seq_page_cost 
---------------
 1
(1 row)

shuai=# 
shuai=#  show cpu_tuple_cost;
 cpu_tuple_cost 
----------------
 0.01
(1 row)

当前参数设置

9.验证公式：195391.91= (shared read=)94393*1(seq_page_cost) +(rows=)10099891*0.01(cpu_tuple_cost)

10.seq_page_cost=0.013444，真是的执行时间为17236.458，所以：17236.458 = 94393*0.013444+10100000*cpu_tuple_cost，得到cpu_tuple_cost=0.00158093450574257426

10.重启数据库，刷新系统缓存，调整成本因子后再次检查执行计划：

shuai=# set seq_page_cost = 0.013444
shuai-# ;
SET
shuai=# set cpu_tuple_cost = 0.00158093450574257426;
SET
shuai=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;
                                                              QUERY PLAN                                          
                     
------------------------------------------------------------------------------------------------------------------
---------------------
 Seq Scan on public.tbl_cost_align  (cost=0.00..17236.29 rows=10099891 width=45) (actual time=24.497..18461.308 ro
ws=10100000 loops=1)
   Output: id, info, crt_time
   Buffers: shared read=94393
 Planning time: 93.559 ms
 Execution time: 28700.791 ms
(5 rows)

校准后的执行计划

 

转载于:https://www.cnblogs.com/pg-libs/p/6225090.html