缓存查询计划对性能的提升验证测试

最新推荐文章于 2024-02-02 16:50:23 发布

weixin_33963594

最新推荐文章于 2024-02-02 16:50:23 发布

阅读量223

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://my.oschina.net/quanzl/blog/1498571

本文通过修改PostgreSQL源代码中的查询解析过程，实现了对特定简单查询的缓存，以此提高查询执行效率。实验结果显示，这一改动能显著减少延迟并提高每秒事务处理数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

为什么80%的码农都做不了架构师？>>>

1、用一个简单的表，让它的执行阶段不要花费太久

postgres=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          |

简单的SQL语句

select a from t;

2、测试，9k多一点

quanzl-mac:bin quanzl$ ./pgbench -f select1.sql postgres -T 10 -n
transaction type: select1.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 91537
latency average = 0.109 ms
tps = 9151.154970 (including connections establishing)
tps = 9153.441420 (excluding connections establishing)

quanzl-mac:bin quanzl$ ./pgbench -f select1.sql postgres -T 10 -n
transaction type: select1.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 91661
latency average = 0.109 ms
tps = 9162.928603 (including connections establishing)
tps = 9165.530320 (excluding connections establishing)

3、修改代码，因为只是验证想法，不妨简单一些

src/backend/tcop/postgres.c 的函数 List *pg_parse_query(const char *query_string)

	static List	   *sel_parsetree_list = NIL;
。。。
// 如果已经分析过，直接复制一份返回
	if (strcmp("select a from t;", query_string)==0 && sel_parsetree_list!=NIL)
		return copyObject(sel_parsetree_list);
。。。
// 如果是第一次调用，将查询树暂存
	if (strcmp("select a from t;", query_string)==0 && sel_parsetree_list==NIL)
	{
		MemoryContext oldcontext = MemoryContextSwitchTo(TopMemoryContext);
		sel_parsetree_list = copyObject(raw_parsetree_list);
		MemoryContextSwitchTo(oldcontext);
	}

4、测试变化

quanzl-mac:bin quanzl$ ./pgbench -f select1.sql postgres -T 10 -n
transaction type: select1.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 94443
latency average = 0.106 ms
tps = 9443.693083 (including connections establishing)
tps = 9446.186718 (excluding connections establishing)

quanzl-mac:bin quanzl$ ./pgbench -f select1.sql postgres -T 10 -n
transaction type: select1.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 94921
latency average = 0.105 ms
tps = 9489.780463 (including connections establishing)
tps = 9492.466957 (excluding connections establishing)

5、可以看到，有明显的变化，方案可行

这个只是验证，实际实现要复杂得多。如果对执行计划做类似改动，相信会有更大惊喜。

补充：

对计划树的缓存对比，惊喜的表现

quanzl-mac:bin quanzl$ ./pgbench -f select1.sql flying -T 10 -n
transaction type: select1.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 115574
latency average = 0.087 ms
tps = 11556.180953 (including connections establishing)
tps = 11559.223205 (excluding connections establishing)


quanzl-mac:bin quanzl$ ./pgbench -f select1.sql flying -T 10 -n
transaction type: select1.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 141918
latency average = 0.070 ms
tps = 14191.319752 (including connections establishing)
tps = 14196.134852 (excluding connections establishing)

---------------------------------------------------
我们的力量源自最先进的开源数据库PostgreSQL

转载于:https://my.oschina.net/quanzl/blog/1498571