Increase default value for prepared-plan-cache.capacity from 100 to 1000

[dbsid]

Enhancement

Typically a transaction for core-banking application will contains several hundreds of statement. The default value 100 for prepared-plan-cache.capacity is way too small, I'll propose to increase the default value to 1000+, anyway we have the memory-guard-ratio to protect from oom.

[dbsid]

running two business simulation of a core-bank application using single one connection, the prepared plan cache is up to ~500

[tiancaiamao]

Is this value configurable?

[dbsid]

Is this value configurable?

changing the values requires restart the tidb instance.

[qw4990]

Could you please post more specified data to show the benefit and risks of increasing this config item? E.g. the latency, throughput, CPU usage, memory usage after increasing this config. @dbsid We need to prove that this modify is reasonable, for example, high benefits and low risks in some crucial scenarios.

[shaoxiqian]

@qw4990 test case: sysbench oltp_read_write.lua
threads:128 time: 600s sysbench --config-file=config oltp_read_write.lua --tables=32 --table-size=10000000 run

prepared-plan-cache.capacity 100vs1000	100	1000	diff(%)
cpu	1230%	1140%	-7.31%
memory	1.54G	2.15G	+39.61%
qps	16997.86	17636.77	+3.76%
tps	849.89	881.84	+3.76%
Queries Using Plan Cache OPS	10.3K	15.2K	+47.57%
latency(avg)	150.59	145.13	-3.63%
95th percentile:	189.93	179.94	-5.26%

[shaoxiqian]

@qw4990 Two more test case for the value of prepared-plan-cache.capacity 100 vs 1000 Test case 1: systench Config: tables=32 table-size=10000000 threads=400、800 time:300s

oltp_update_non_index

400并发	800并发
100 1000 Diff(%) QPS 66824.24 68946.85 +3.18% Latency (avg) 5.98 5.8 -3.01% 95th 13.22 12.30 -6.96%	100 1000 Diff(%) QPS 77791.53 79277.98 +1.91% Latency (avg) 10.28 10.09 -1.85% 95th 23.10 22.69 -1.77%

oltp_update_index

400并发	800并发
100 1000 Diff(%) QPS 27559.82 27940.78 +1.83% Latency (avg) 14.511 14.31 -1.38% 95th 25.74 25.28 -1.79%	100 1000 Diff(%) QPS 30537.16 32723.53 +7.16% Latency (avg) 25.97 24.44 -1.79% 95th 49.21 44.17 -10.24%

oltp_insert

400并发	800并发
100 1000 Diff(%) QPS 36563.15 40551.89 +10.91% Latency (avg) 21.87 19.72 -9.83% 95th 46.63 34.95 -25.05%	100 1000 Diff(%) QPS 30537.16 32723.53 +7.16% Latency (avg) 25.97 24.44 -1.79% 95th 49.21 44.17 -10.24%

oltp_point_select

400并发	800并发
100 1000 Diff(%) QPS 230893.54 216613.29 -6.18% Latency (avg) 1.73 1.85 +6.94% 95th 6.43 7.04 +9.49%	100 1000 Diff(%) QPS 231852.84 250362.21 +7.98% Latency (avg) 3.45 3.19 -7.54% 95th 14.46 12.75 -11.83%

Test Case 2:TPC-C Config: threads=400、800	400并发	800并发
100 1000 Diff(%) tpmC 111233.7 110291.9 -0.85% efficiency 865.0% 857.6% -0.86%	100 1000 Diff(%) tpmC 115434.1 117087.7 +1.43% efficiency 897.6% 910.5% +1.44%

[dbsid]

@tiancaiamao @qw4990 From the test, for cases such as sysbench read-write on 32 tables, the number of prepared statement exceeds 100. By increasing the limit from 100 to 1000, we basically get more qps、less cpu usage and lower latency, at the cost of more memory usage. We think it's a reasonable tradeoff. In real-world, it's super hard for user to spot the size limitation of plan cache and extend it correspondingly. For other cases when the number of prepared statements does not exceeds 100 and just use query interface, we don't see performance penalty from this change.