PostgreSQL prepared statement和simple query的profile及性能差异

prepared statement是非常重要的高并发SQL优化手段之一，效果也显而易见。
下面是测试，同时观察绑定和不绑定的情况下的profile。
在未使用绑定变量的时候，新增或上升了一些硬解析相关的CODE。

测试数据

postgres=# create table test(id int primary key, info text);

postgres=# insert into test select generate_series(1,1000000),'test';

postgres=# create or replace function f1(int) returns setof text as $$
  select info from test where id=$1;
$$ language sql;

测试用例

vi test.sql
\setrandom id 1 1000000
select f1(:id);

使用绑定变量

pgbench -M prepared -n -r -P 5 -f ./test.sql -c 64 -j 64 -T 100

progress: 10.0 s, 526016.9 tps, lat 0.120 ms stddev 0.033
progress: 15.0 s, 523072.8 tps, lat 0.121 ms stddev 0.027
progress: 20.0 s, 523305.2 tps, lat 0.121 ms stddev 0.017
progress: 25.0 s, 523320.9 tps, lat 0.121 ms stddev 0.015
progress: 30.0 s, 523290.4 tps, lat 0.121 ms stddev 0.016
progress: 35.0 s, 523216.3 tps, lat 0.121 ms stddev 0.015
progress: 40.0 s, 523046.3 tps, lat 0.121 ms stddev 0.022
progress: 45.0 s, 523200.9 tps, lat 0.121 ms stddev 0.015
progress: 50.0 s, 523853.5 tps, lat 0.121 ms stddev 0.016
progress: 55.0 s, 526587.1 tps, lat 0.120 ms stddev 0.005
progress: 60.0 s, 526710.0 tps, lat 0.120 ms stddev 0.008

TOP 调用

perf top

   PerfTop:   62851 irqs/sec  kernel:12.9%  exact:  0.0% [1000Hz cycles],  (all, 64 CPUs)
---------------------------------------------------------------------------------------------
  samples  pcnt function                      DSO
  _______ _____ _____________________________ ____________________________________
 39828.00  4.3% AllocSetAlloc                 /home/digoal/pgsql9.5.2/bin/postgres
 33282.00  3.6% SearchCatCache                /home/digoal/pgsql9.5.2/bin/postgres
 23098.00  2.5% base_yyparse                  /home/digoal/pgsql9.5.2/bin/postgres
 21318.00  2.3% GetSnapshotData               /home/digoal/pgsql9.5.2/bin/postgres
 13218.00  1.4% hash_search_with_hash_value   /home/digoal/pgsql9.5.2/bin/postgres
 11399.00  1.2% _int_malloc                   /lib64/libc-2.12.so
 11362.00  1.2% LWLockAcquire                 /home/digoal/pgsql9.5.2/bin/postgres
 11151.00  1.2% palloc                        /home/digoal/pgsql9.5.2/bin/postgres
  9536.00  1.0% __GI_vfprintf                 /lib64/libc-2.12.so
  9160.00  1.0% __strcmp_sse42                /lib64/libc-2.12.so
  8997.00  1.0% schedule                      [kernel.kallsyms]
  8921.00  1.0% __strlen_sse42                /lib64/libc-2.12.so
  8799.00  0.9% nocachegetattr                /home/digoal/pgsql9.5.2/bin/postgres
  8431.00  0.9% MemoryContextAllocZeroAligned /home/digoal/pgsql9.5.2/bin/postgres
  8314.00  0.9% expression_tree_walker        /home/digoal/pgsql9.5.2/bin/postgres
  7968.00  0.9% core_yylex                    /home/digoal/pgsql9.5.2/bin/postgres
  7193.00  0.8% _bt_compare                   /home/digoal/pgsql9.5.2/bin/postgres
  6402.00  0.7% _int_free                     /lib64/libc-2.12.so
  6185.00  0.7% memcpy                        /lib64/libc-2.12.so
  5988.00  0.6% fmgr_info_cxt_security        /home/digoal/pgsql9.5.2/bin/postgres
  5749.00  0.6% __GI___libc_malloc            /lib64/libc-2.12.so
  5697.00  0.6% PostgresMain                  /home/digoal/pgsql9.5.2/bin/postgres
  5444.00  0.6% fmgr_sql                      /home/digoal/pgsql9.5.2/bin/postgres
  5372.00  0.6% LWLockRelease                 /home/digoal/pgsql9.5.2/bin/postgres
  4917.00  0.5% grouping_planner              /home/digoal/pgsql9.5.2/bin/postgres
  4902.00  0.5% ExecInitExpr                  /home/digoal/pgsql9.5.2/bin/postgres
  4626.00  0.5% pfree                         /home/digoal/pgsql9.5.2/bin/postgres
  4607.00  0.5% doCustom                      /home/digoal/pgsql9.5.2/bin/pgbench
  4537.00  0.5% DirectFunctionCall1Coll       /home/digoal/pgsql9.5.2/bin/postgres
  4521.00  0.5% fget_light                    [kernel.kallsyms]
  4329.00  0.5% pqParseInput3                 /home/digoal/pgsql9.5.2/lib/libpq.so.5.8
  4164.00  0.4% AllocSetFree                  /home/digoal/pgsql9.5.2/bin/postgres
  4013.00  0.4% hash_any                      /home/digoal/pgsql9.5.2/bin/postgres
  3998.00  0.4% new_list                      /home/digoal/pgsql9.5.2/bin/postgres
  3994.00  0.4% do_select                     [kernel.kallsyms]
  3653.00  0.4% LockReleaseAll                /home/digoal/pgsql9.5.2/bin/postgres
  3618.00  0.4% hash_search                   /home/digoal/pgsql9.5.2/bin/postgres
  3505.00  0.4% palloc0                       /home/digoal/pgsql9.5.2/bin/postgres
  3457.00  0.4% ScanKeywordLookup             /home/digoal/pgsql9.5.2/bin/postgres
  3390.00  0.4% FunctionCall2Coll             /home/digoal/pgsql9.5.2/bin/postgres
  3296.00  0.4% LockAcquireExtended           /home/digoal/pgsql9.5.2/bin/postgres
  3275.00  0.4% __memset_sse2                 /lib64/libc-2.12.so
  3201.00  0.3% __cfree                       /lib64/libc-2.12.so
  3125.00  0.3% lappend                       /home/digoal/pgsql9.5.2/bin/postgres
  3004.00  0.3% exec_bind_message             /home/digoal/pgsql9.5.2/bin/postgres
  2995.00  0.3% __strcpy_ssse3                /lib64/libc-2.12.so
  2992.00  0.3% device_not_available          [kernel.kallsyms]

不使用绑定变量
性能明显下降

pgbench -M simple -n -r -P 5 -f ./test.sql -c 64 -j 64 -T 100

progress: 10.0 s, 480056.6 tps, lat 0.132 ms stddev 0.028
progress: 15.0 s, 480355.0 tps, lat 0.132 ms stddev 0.019
progress: 20.0 s, 480321.8 tps, lat 0.132 ms stddev 0.020
progress: 25.0 s, 480246.2 tps, lat 0.132 ms stddev 0.019
progress: 30.0 s, 480274.6 tps, lat 0.132 ms stddev 0.020
progress: 35.0 s, 480286.1 tps, lat 0.132 ms stddev 0.018
progress: 40.0 s, 480229.3 tps, lat 0.132 ms stddev 0.020
progress: 45.0 s, 480095.6 tps, lat 0.132 ms stddev 0.021
progress: 50.0 s, 480098.9 tps, lat 0.132 ms stddev 0.020
progress: 55.0 s, 480066.5 tps, lat 0.132 ms stddev 0.025
progress: 60.0 s, 480148.3 tps, lat 0.132 ms stddev 0.021

TOP 调用

perf top
   PerfTop:   65503 irqs/sec  kernel:12.3%  exact:  0.0% [1000Hz cycles],  (all, 64 CPUs)
----------------------------------------------------------------------------------------------
  samples  pcnt function                       DSO
  _______ _____ ______________________________ ____________________________________
 45824.00  4.6% AllocSetAlloc                  /home/digoal/pgsql9.5.2/bin/postgres
 38982.00  3.9% base_yyparse                   /home/digoal/pgsql9.5.2/bin/postgres
 35333.00  3.6% SearchCatCache                 /home/digoal/pgsql9.5.2/bin/postgres
 23770.00  2.4% GetSnapshotData                /home/digoal/pgsql9.5.2/bin/postgres
 12440.00  1.3% palloc                         /home/digoal/pgsql9.5.2/bin/postgres
 12092.00  1.2% hash_search_with_hash_value    /home/digoal/pgsql9.5.2/bin/postgres
 12092.00  1.2% _int_malloc                    /lib64/libc-2.12.so
 11911.00  1.2% core_yylex                     /home/digoal/pgsql9.5.2/bin/postgres    上升
 11286.00  1.1% LWLockAcquire                  /home/digoal/pgsql9.5.2/bin/postgres
 10893.00  1.1% __strcmp_sse42                 /lib64/libc-2.12.so
 10759.00  1.1% MemoryContextAllocZeroAligned  /home/digoal/pgsql9.5.2/bin/postgres    上升
  9946.00  1.0% expression_tree_walker         /home/digoal/pgsql9.5.2/bin/postgres    上升
  9175.00  0.9% schedule                       [kernel.kallsyms]
  9049.00  0.9% nocachegetattr                 /home/digoal/pgsql9.5.2/bin/postgres
  8859.00  0.9% __strlen_sse42                 /lib64/libc-2.12.so
  8020.00  0.8% __GI_vfprintf                  /lib64/libc-2.12.so
  7396.00  0.7% _int_free                      /lib64/libc-2.12.so
  6847.00  0.7% __GI___libc_malloc             /lib64/libc-2.12.so
  6842.00  0.7% _bt_compare                    /home/digoal/pgsql9.5.2/bin/postgres
  6468.00  0.7% grouping_planner               /home/digoal/pgsql9.5.2/bin/postgres
  5468.00  0.6% fmgr_sql                       /home/digoal/pgsql9.5.2/bin/postgres
  5403.00  0.5% memcpy                         /lib64/libc-2.12.so
  5328.00  0.5% LWLockRelease                  /home/digoal/pgsql9.5.2/bin/postgres
  5277.00  0.5% fmgr_info_cxt_security         /home/digoal/pgsql9.5.2/bin/postgres
  5024.00  0.5% ExecInitExpr                   /home/digoal/pgsql9.5.2/bin/postgres
  4819.00  0.5% DirectFunctionCall1Coll        /home/digoal/pgsql9.5.2/bin/postgres
  4620.00  0.5% new_list                       /home/digoal/pgsql9.5.2/bin/postgres
  4582.00  0.5% fget_light                     [kernel.kallsyms]
  4563.00  0.5% ScanKeywordLookup              /home/digoal/pgsql9.5.2/bin/postgres
  4501.00  0.5% doCustom                       /home/digoal/pgsql9.5.2/bin/pgbench
  4453.00  0.4% AllocSetFree                   /home/digoal/pgsql9.5.2/bin/postgres
  4354.00  0.4% pfree                          /home/digoal/pgsql9.5.2/bin/postgres
  4096.00  0.4% pqParseInput3                  /home/digoal/pgsql9.5.2/lib/libpq.so.5.8
  4050.00  0.4% do_select                      [kernel.kallsyms]
  4000.00  0.4% lappend                        /home/digoal/pgsql9.5.2/bin/postgres
  3892.00  0.4% hash_any                       /home/digoal/pgsql9.5.2/bin/postgres
  3863.00  0.4% __memset_sse2                  /lib64/libc-2.12.so
  3798.00  0.4% expression_tree_mutator        /home/digoal/pgsql9.5.2/bin/postgres    下降
  3777.00  0.4% palloc0                        /home/digoal/pgsql9.5.2/bin/postgres
  3773.00  0.4% check_stack_depth              /home/digoal/pgsql9.5.2/bin/postgres    新增
  3643.00  0.4% heap_getsysattr                /home/digoal/pgsql9.5.2/bin/postgres    新增
  3487.00  0.4% SearchSysCache                 /home/digoal/pgsql9.5.2/bin/postgres    新增
  3485.00  0.4% LockReleaseAll                 /home/digoal/pgsql9.5.2/bin/postgres
  3460.00  0.3% eval_const_expressions_mutator /home/digoal/pgsql9.5.2/bin/postgres    新增
  3444.00  0.3% FunctionCall2Coll              /home/digoal/pgsql9.5.2/bin/postgres
  3419.00  0.3% __strcpy_ssse3                 /lib64/libc-2.12.so
  3201.00  0.3% LockAcquireExtended            /home/digoal/pgsql9.5.2/bin/postgres

时间： 2024-09-15 14:48:09

PostgreSQL prepared statement和simple query的profile及性能差异的相关文章

psycopg2 postgresql driver for python don't support prepared statement Direct

前面使用py-postgresql测试过PostgreSQL性能, 可能是这个驱动效率较低, 我们接下来使用psycopg2测试一下. psycopg2使用libpq接口, 支持2PC, 支持异步提交等, 但是不支持绑定变量. 安装 [root@localhost ~]# . /home/postgres/.bash_profile root@localhost-> which pg_config /opt/pgsql9.3.5/bin/pg_config [root@localhost ~]#

关于weblogic中使用prepared statement cache后操作DDL的问题

前几天有客户问我这么个问题,他们在weblogic中配置了prepared statement cache, 而他们应用中有操作DDL的地方,比如alter table什么的,这时候如果使用cached prepared statement的话,Oracle端会抛出SQLException: 违反协议.其实这个问题,weblogic 文档中已经有描述,如下: http://e-docs.bea.com/wls/docs81/ConsoleHelp/jdbc_connection_pools.ht

mysql query cache用法与性能详细介绍

mysql Query Cache 默认为打开.从某种程度可以提高查询的效果,但是未必是最优的解决方案,如果有的大量的修改和查询时,由于修改造成的cache失效,会给服务器造成很大的开销,可以通过query_cache_type[0(OFF)1(ON)2(DEMAND)]来控制缓存的开关. 需要注意的是mysql query cache 是对大小写敏感的,因为Query Cache 在内存中是以 HASH 结构来进行映射,HASH 算法基础就是组成 SQL 语句的字符,所以任何sql语句的改变

MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

类似于SQL Server中的:sp_executesql sql server script: --- 涂聚文 20160906 IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuDeptUserCount') DROP PROCEDURE proc_Select_DuDeptUserCount GO CREATE PROCEDURE proc_Select_DuDeptUserCount ( @where NV

PostgreSQL 利用编译器extension 支持int128，提升聚合性能

标签 PostgreSQL , int128 , clang , gcc , icc 背景 PostgreSQL 9.4以及以前的版本,在INT,INT2,INT8的聚合计算中,为了保证数据不会溢出,中间结果使用numeric来存储. numeric是PostgreSQL自己实现的一种数值类型,可以存储非常大的数值(估计是做科学计算的需求),但是牺牲了一定的性能. 为了提高聚合,特别是大数据量的聚合时的性能,社区借用了编译器支持的int128类型,作为数据库int, int2, int8的中间计

MySQL使用profile查询性能的操作教程_Mysql

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之五：函数的差异(六)

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先是迁移数据,然后就是SQL.存储过程.序列等程序中不同的数据库中数据的使用方式的转换.下面根据自己的理解和测试,写了一些SQL以及数据库对象转换方面的文章,不足之处,尚请多多指教. 1.NVL2 NVL2(expr1, expr2, expr3) 是Oracle的

PostgreSQL 性能优化方法 - 1

使用一个非常容易理解的案例,讲解PostgreSQL的优化手段. 本文成文自2012年初,有一些性能指标数据已经过时了,但是优化方法没有过时,请关注优化的方法. 现在的硬件和软件,一台主机达到的性能数据已经可以替代我当时测试的8台主机性能了. [软件环境] CentOS 5 x64 PostgreSQL 9.1.3 (还是以前测试的,所以用的老版本,你可以用最新的版本测试比如9.5) plproxy 2.3 pgbouncer 1.4.2 [测试模型] 设计一个包含INSERT, UPDATE,

批量删除数据后, 未释放empty索引页导致mergejoin执行计划变慢 case - 分析与规避方法

标签 PostgreSQL , merge join , min , max , 优化器 , 索引倾斜 , 垃圾回收背景 PostgreSQL支持三种JOIN的方法,nestloop, merge, hash. 这三种JOIN方法的差别和原理可以参考 https://www.postgresql.org/docs/devel/static/planner-optimizer.html <PostgreSQL nestloop/hash/merge join讲解> nested loop jo