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