Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

Systemtap EXP: PostgreSQL IN-BUILD mark Class 2 - query

3 minute read

背景

本文要讲的是PostgreSQL内建探针的第二个分类 : query.

相比第一个分类transaction, query更加精细. 例如函数调用, 可以精细到函数内部的每个SQL, 以及嵌套函数的最底层的SQL等.

包含SQL查询开始, 结束, 语义解析开始, 结束, 重写开始, 结束, plan开始, 结束, 执行开始, 结束.

Name Parameters Description
query-start (const char *) Probe that fires when the processing of a query is started. arg0 is the query string.
query-done (const char *) Probe that fires when the processing of a query is complete. arg0 is the query string.
query-parse-start (const char *) Probe that fires when the parsing of a query is started. arg0 is the query string.
query-parse-done (const char *) Probe that fires when the parsing of a query is complete. arg0 is the query string.
query-rewrite-start (const char *) Probe that fires when the rewriting of a query is started. arg0 is the query string.
query-rewrite-done (const char *) Probe that fires when the rewriting of a query is complete. arg0 is the query string.
query-plan-start () Probe that fires when the planning of a query is started.
query-plan-done () Probe that fires when the planning of a query is complete.
query-execute-start () Probe that fires when the execution of a query is started.
query-execute-done () Probe that fires when the execution of a query is complete.

源码位置 :

src/backend/tcop/postgres.c  
src/backend/tcop/pquery.c  
使用这些探针, 可以收集SQL级别的统计信息, 与事务级别的类似, 所以就不再举例, 有兴趣的童鞋可参考  :

http://blog.163.com/digoal@126/blog/static/163877040201391684012713/

下面要举的这个例子和数据库优化有关, 以前写过一篇优化案例讲解, 有兴趣的童鞋可以参考一下

http://blog.163.com/digoal@126/blog/static/163877040201221382150858/

http://blog.163.com/digoal@126/blog/static/163877040201221333411196/

在使用pgbench压数据库时, 可以选择三种模式.  
       -M querymode  
           Protocol to use for submitting queries to the server:  
           ·   simple: use simple query protocol.  
           ·   extended: use extended query protocol.  
           ·   prepared: use extended query protocol with prepared statements.  
           The default is simple query protocol. (See Chapter 48, Frontend/Backend Protocol, in the documentation for  
           more information.)

2种协议的区别参见 :

http://www.postgresql.org/docs/9.3/static/protocol-flow.html

A simple query cycle is initiated by the frontend sending a Query message to the backend. The message includes an SQL command (or commands) expressed as a text string. The backend then sends one or more response messages depending on the contents of the query command string, and finally a ReadyForQuery response message. ReadyForQuery informs the frontend that it can safely send a new command. (It is not actually necessary for the frontend to wait for ReadyForQuery before issuing another command, but the frontend must then take responsibility for figuring out what happens if the earlier command fails and already-issued later commands succeed.)  
  
The extended query protocol breaks down the above-described simple query protocol into multiple steps. The results of preparatory steps can be re-used multiple times for improved efficiency. Furthermore, additional features are available, such as the possibility of supplying data values as separate parameters instead of having to insert them directly into a query string.  
  
In the extended protocol, the frontend first sends a Parse message, which contains a textual query string, optionally some information about data types of parameter placeholders, and the name of a destination prepared-statement object (an empty string selects the unnamed prepared statement). The response is either ParseComplete or ErrorResponse. Parameter data types can be specified by OID; if not given, the parser attempts to infer the data types in the same way as it would do for untyped literal string constants.  
pgbench的prepared模式用的是extended协议, 同时使用了named prepared statement.  
接下来使用stap观察一下3种模式的区别.  
stap -e '  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start") {  
  println(pn(), user_string($arg1), pid())  
}  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start") {  
  println(pn(), user_string($arg1), pid())  
}  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__rewrite__start") {  
  println(pn(), user_string($arg1), pid())  
}  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start") {  
  println(pn(), pid())  
}  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start") {  
  println(pn(), pid())  
}'  
执行pgbench :   
pg93@db-172-16-3-150-> cat test.sql  
select clock_timestamp();  
首先是simple模式  
pg93@db-172-16-3-150-> pgbench -M simple -n -r -f ./test.sql -c 1 -j 1 -t 6  
输出, 每条SQL都需要经历query start, parse, rewrite, plan, execute.  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__rewrite__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__rewrite__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__rewrite__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__rewrite__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__rewrite__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__rewrite__start")select clock_timestamp();14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14146  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14146  
  
接下来是extended模式  
pg93@db-172-16-3-150-> pgbench -M extended -n -r -f ./test.sql -c 1 -j 1 -t 6  
输出, 没有了query start和 rewrite. 每条SQL都需要经历 parse, plan, execute.  
parse中包含SQL语句.  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14140  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14140  
  
最后是prepared模式  
pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 1 -j 1 -t 6  
输出, 只有一次query parse, plan, 后面执行同样类型SQL的都是execute.  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__parse__start")select clock_timestamp();14143  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__plan__start")14143  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14143  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14143  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14143  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14143  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14143  
process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__execute__start")14143  
显然是一prepared模式效率最高. 但是对于客户端和数据库之间有连接池的情况可能就不适用, 例如pgbouncer连接池.  
因为extended协议下如果使用prepared模式, 只有第一个parse消息中包含了sql, 后面都以named prepared statement代替, 如果更换服务端, 将找不到该backend process中存储的named prepared statement. 以致报错.

参考

1. http://blog.163.com/digoal@126/blog/static/163877040201391684012713/

2. http://www.postgresql.org/docs/9.3/static/dynamic-trace.html

3. http://blog.163.com/digoal@126/blog/static/163877040201221382150858/

4. http://blog.163.com/digoal@126/blog/static/163877040201221333411196/

5. src/backend/tcop/postgres.c

6. src/backend/tcop/pquery.c

7. http://www.postgresql.org/docs/9.3/static/protocol-flow.html

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...