Stress testing report of Performance Schema - Database Autonomy Service

This topic describes the performance changes of your database instances in scenarios with different parameter settings of the Performance Schema feature.

Testing scenario

In this example, an ApsaraDB RDS for MySQL instance and a PolarDB for MySQL cluster are used for testing. The following table describes the specifications of the instance and cluster.


Database type	Edition	Instance family	Instance type	Number of CPU cores and memory capacity	Maximum number of concurrent connections	Maximum IOPS
ApsaraDB RDS for MySQL	High-availability Edition	General-purpose	mysql.n4.large.2c	4 cores, 16 GB memory	8,000	2,800
PolarDB for MySQL	Cluster Edition	Dedicated	polar.mysql.x4.large	4 cores, 16 GB memory	8,000	48,000

Number of test tables: 10.
Number of concurrent stress testing tasks that can be performed (concurrency level): 1, 2, 4, 8, 16, 32, 64, 128, 256, 512, or 1,024.
Test tool: SysBench.
Note SysBench is a modular, open source, cross-platform, and multi-threaded benchmark tool that can be used to evaluate the performance of a heavily loaded database system based on core metrics. For more information about SysBench, see SysBench documentation.

Test results

Various scenarios are created for testing based on the settings of the performance_schema parameter and the consumers and instrumentation configuration items of the performance_schema parameter. For more information, see the Scenarios with different parameter settings section of this topic.

The following tables list the average queries per second (QPS) of your instance or cluster at a concurrency level of 32 after the CPU utilization of your instance or cluster reaches 100% in various scenarios. The system delivers the optimal performance in the pfsoff scenario. For more information about the test data at different concurrency levels, see the Test data section of this topic.

Note In the pfson_conon_ins_sqlon scenario, the new version of the performance insight feature provided by Database Autonomy Service (DAS) is enabled.

ApsaraDB RDS for MySQL


Scenario	Read-only		Read/write		Write-only
pfsoff	47,176	Baseline value	37,999	Baseline value	39,940	Baseline value
pfson_conoff_insoff	41,111	Reduced by 12.86%	33,170	Reduced by 12.71%	32,327	Reduced by 19.06%
pfson_conon_insoff	41,773	Reduced by 11.45%	33,568	Reduced by 11.66%	31,434	Reduced by 21.30%
pfson_conon_ins_sqlon	39,889	Reduced by 15.44%	33,056	Reduced by 13.01%	31,865	Reduced by 20.22%
pfson_conoff_inson	39,344	Reduced by 16.61%	32,698	Reduced by 13.95%	31,698	Reduced by 20.64%
pfson_conon_inson	39,168	Reduced by 16.97%	32,280	Reduced by 15.05%	30,834	Reduced by 22.81%

PolarDB for MySQL


Scenario	Read-only		Read/write		Write-only
pfsoff	36,768	Baseline value	38,876	Baseline value	39,550	Baseline value
pfson_conoff_insoff	31,981	Reduced by 13.02%	33,342	Reduced by 14.24%	34,182	Reduced by 13.57%
pfson_conon_insoff	31,187	Reduced by 15.18%	32,980	Reduced by 15.17%	31,768	Reduced by 19.68%
pfson_conon_ins_sqlon	30,976	Reduced by 15.75%	31,980	Reduced by 17.77%	31,101	Reduced by 21.36%
pfson_conoff_inson	31,025	Reduced by 15.62%	32,855	Reduced by 15.49%	31,750	Reduced by 19.72%
pfson_conon_inson	29,652	Reduced by 19.35%	30,656	Reduced by 21.14%	30,014	Reduced by 24.11%

Scenarios with different parameter settings

Various scenarios are created based on the settings of the performance_schema parameter and the consumers and instrumentation configuration items of the performance_schema parameter. The following table describes the parameter settings in various scenarios.

Note For information about how to modify the performance_schema parameter, see Modify the parameters of an ApsaraDB RDS for MySQL instance.


Scenario	Parameter settings
pfsoff	The Performance Schema feature is disabled.
pfson_conoff_insoff	The Performance Schema feature is enabled. All options of the consumers configuration item are disabled. `-- All options of the consumers configuration item are disabled. update performance_schema.setup_consumers set Enabled = 'NO' ;` All options of the instrumentation configuration item are disabled. `-- All options of the instrumentation configuration item are disabled. update performance_schema.setup_instruments set Enabled = 'NO' ,timed = 'NO';`
pfson_conon_insoff	The Performance Schema feature is enabled. All options of the consumers configuration item are enabled. `-- All options of the consumers configuration item are enabled. update performance_schema.setup_consumers set Enabled = 'YES' ;` All options of the instrumentation configuration item are disabled. `-- All options of the instrumentation configuration item are disabled. update performance_schema.setup_instruments set Enabled = 'NO' ,timed = 'NO';`
pfson_conon_ins_sqlon	The Performance Schema feature is enabled. The global_instrumentation, thread_instrumentation, events_statements_current, events_statements_history, and statements_digest options of the consumers configuration item are enabled. `-- The global_instrumentation, thread_instrumentation, events_statements_current, events_statements_history, and statements_digest options of the consumers configuration item are enabled. update performance_schema.setup_consumers set enabled = if(name in ('global_instrumentation','thread_instrumentation','events_statements_current','events_statements_history','statements_digest'),'YES', 'NO');` All options prefixed with statement of the instrumentation configuration item are enabled. `-- All options prefixed with statement of the instrumentation configuration item are enabled. update performance_schema.setup_instruments set enabled = if(name like 'statement/%', 'YES', 'NO'), timed = enabled;` Note In the pfson_conon_ins_sqlon scenario, the performance insight feature is enabled.
pfson_conoff_inson	The Performance Schema feature is enabled. All options of the consumers configuration item are disabled. `-- All options of the consumers configuration item are disabled. update performance_schema.setup_consumers set Enabled = 'NO' ;` All options of the instrumentation configuration item are enabled. `-- All options of the instrumentation configuration item are enabled. update performance_schema.setup_instruments set Enabled = 'YES' ,timed = 'YES';`
pfson_conon_inson	The Performance Schema feature is enabled. All options of the consumers configuration item are enabled. `-- All options of the consumers configuration item are enabled. update performance_schema.setup_consumers set Enabled = 'YES' ;` All options of the instrumentation configuration item are enabled. `-- All options of the instrumentation configuration item are enabled. update performance_schema.setup_instruments set Enabled = 'YES' ,timed = 'YES';`

Test data

The following tables list the average QPS of your instance or cluster in read-only, write-only, and read/write scenarios after the CPU utilization of your instance or cluster reaches 100%.

ApsaraDB RDS for MySQL

Read-only scenario

Table 1. Test data in the read-only scenario
Concurrency level	pfsoff	pfson_conoff_insoff	pfson_conon_insoff	pfson_conon_ins_sqlon	pfson_conoff_inson	pfson_conon_inson
1	3,456	3,209	3,233	3,179	3,249	3,116
1	Baseline value	Reduced by 7.16%	Reduced by 6.45%	Reduced by 8.02%	Reduced by 5.98%	Reduced by 9.84%
2	9,432	8,668	8,701	8,588	8,854	8,313
2	Baseline value	Reduced by 8.10%	Reduced by 7.75%	Reduced by 8.95%	Reduced by 6.13%	Reduced by 11.86%
4	16,485	14,965	15,123	14,926	14,942	14,053
4	Baseline value	Reduced by 9.22%	Reduced by 8.26%	Reduced by 9.46%	Reduced by 9.36%	Reduced by 14.75%
8	25,614	23,024	23,286	22,781	23,017	21,659
8	Baseline value	Reduced by 10.11%	Reduced by 9.09%	Reduced by 11.06%	Reduced by 10.14%	Reduced by 15.44%
16	39,846	35,869	35,483	34,162	34,288	33,387
16	Baseline value	Reduced by 9.98%	Reduced by 10.95%	Reduced by 13.14%	Reduced by 13.95%	Reduced by 16.21%
32	47,176	41,111	41,773	39,889	39,344	39,168
32	Baseline value	Reduced by 12.86%	Reduced by 11.45%	Reduced by 15.44%	Reduced by 16.61%	Reduced by 16.97%
64	49,230	42,186	42,776	41,791	41,747	38,877
64	Baseline value	Reduced by 14.31%	Reduced by 13.11%	Reduced by 15.11%	Reduced by 15.20%	Reduced by 21.03%
128	48,201	41,458	41,280	40,797	40,990	36,532
128	Baseline value	Reduced by 13.99%	Reduced by 14.36%	Reduced by 15.36%	Reduced by 14.96%	Reduced by 24.21%
256	46,982	40,090	39,869	39,556	40,414	34,602
256	Baseline value	Reduced by 14.67%	Reduced by 15.14%	Reduced by 15.81%	Reduced by 13.98%	Reduced by 26.35%
512	47,026	41,279	40,353	39,488	39,337	34,023
512	Baseline value	Reduced by 15.22%	Reduced by 14.19%	Reduced by 16.03%	Reduced by 16.35%	Reduced by 27.65%
1,024	47,533	40,104	40,032	39,509	39,462	33,696
1,024	Baseline value	Reduced by 15.63%	Reduced by 15.78%	Reduced by 16.88%	Reduced by 16.98%	Reduced by 29.11%

Write-only scenario

Table 2. Test data in the write-only scenario
Concurrency level	pfsoff	pfson_conoff_insoff	pfson_conon_insoff	pfson_conon_ins_sqlon	pfson_conoff_inson	pfson_conon_inson
1	3,614	3,305	3,293	3,269	3,354	3,222
1	Baseline value	Reduced by 8.56%	Reduced by 8.88%	Reduced by 9.56%	Reduced by 7.19%	Reduced by 10.86%
2	8,452	7,644	7,547	7,606	7,603	7,281
2	Baseline value	Reduced by 9.56%	Reduced by 10.71%	Reduced by 10.01%	Reduced by 10.04%	Reduced by 13.86%
4	13,648	12,231	12,193	12,106	12,057	11,676
4	Baseline value	Reduced by 10.38%	Reduced by 10.66%	Reduced by 11.30%	Reduced by 11.66%	Reduced by 14.45%
8	25,648	22,227	22,824	22,360	22,534	21,431
8	Baseline value	Reduced by 13.34%	Reduced by 11.01%	Reduced by 12.82%	Reduced by 12.14%	Reduced by 16.44%
16	32,154	27,119	27,669	27,093	27,347	26,170
16	Baseline value	Reduced by 15.66%	Reduced by 13.95%	Reduced by 15.74%	Reduced by 14.95%	Reduced by 18.61%
32	39,940	31,434	31,434	31,865	31,698	30,834
32	Baseline value	Reduced by 21.30%	Reduced by 19.06%	Reduced by 20.22%	Reduced by 20.64%	Reduced by 22.81%
64	39,123	30,915	31,220	30,661	30,672	29,514
64	Baseline value	Reduced by 20.98%	Reduced by 20.20%	Reduced by 21.63%	Reduced by 21.06%	Reduced by 24.56%
128	41,320	32,692	32,610	32,135	32,184	29,423
128	Baseline value	Reduced by 20.88%	Reduced by 21.08%	Reduced by 22.23%	Reduced by 22.11%	Reduced by 28.79%
256	42,004	33,322	33,124	32,838	32,814	30,348
256	Baseline value	Reduced by 20.67%	Reduced by 21.14%	Reduced by 21.82%	Reduced by 21.88%	Reduced by 27.75%
512	43,156	34,033	33,597	33,783	33,196	30,157
512	Baseline value	Reduced by 21.14%	Reduced by 22.15%	Reduced by 21.72%	Reduced by 23.08%	Reduced by 30.12%
1,024	47,562	37,056	36,232	37,113	36,723	32,256
1,024	Baseline value	Reduced by 22.09%	Reduced by 23.82%	Reduced by 21.97%	Reduced by 22.79%	Reduced by 32.18%

Read/write scenario

Table 3. Test data in the read/write scenario
Concurrency level	pfsoff	pfson_conoff_insoff	pfson_conon_insoff	pfson_conon_ins_sqlon	pfson_conoff_inson	pfson_conon_inson
1	2,997	2,754	2,844	2,758	2,741	2,672
1	Baseline value	Reduced by 8.1%	Reduced by 5.12%	Reduced by 7.98%	Reduced by 8.55%	Reduced by 10.86%
2	8,135	7,304	7,610	7,420	7,498	7,226
2	Baseline value	Reduced by 10.22%	Reduced by 6.45%	Reduced by 8.79%	Reduced by 7.83%	Reduced by 11.17%
4	14,568	13,031	13,427	13,178	13,164	12,754
4	Baseline value	Reduced by 10.55%	Reduced by 7.83%	Reduced by 9.54%	Reduced by 9.64%	Reduced by 12.45%
8	24,658	21,933	22,663	21,854	22,130	21,250
8	Baseline value	Reduced by 11.05%	Reduced by 8.09%	Reduced by 11.37%	Reduced by 10.25%	Reduced by 13.82%
16	31,562	28,119	28,443	27,629	27,936	26,957
16	Baseline value	Reduced by 10.91%	Reduced by 9.88%	Reduced by 12.46%	Reduced by 11.49%	Reduced by 14.61%
32	37,999	33,170	33,568	33,056	32,698	32,280
32	Baseline value	Reduced by 12.71%	Reduced by 11.66%	Reduced by 13.01%	Reduced by 13.95%	Reduced by 15.05%
64	39,120	33,311	33,948	33,068	33,659	31,605
64	Baseline value	Reduced by 14.85%	Reduced by 13.22%	Reduced by 15.47%	Reduced by 13.96%	Reduced by 19.21%
128	41,687	35,742	35,334	35,451	35,488	32,899
128	Baseline value	Reduced by 14.26%	Reduced by 15.24%	Reduced by 14.96%	Reduced by 14.87%	Reduced by 21.08%
256	42,654	36,243	33,570	36,115	35,949	33,133
256	Baseline value	Reduced by 15.03%	Reduced by 16.14%	Reduced by 15.33%	Reduced by 15.72%	Reduced by 22.32%
512	43,156	36,626	36,087	36,302	36,286	32,518
512	Baseline value	Reduced by 15.13%	Reduced by 16.38%	Reduced by 15.88%	Reduced by 15.92%	Reduced by 24.65%
1,024	43,658	36,699	36,625	36,585	36,756	32,359
1,024	Baseline value	Reduced by 15.94%	Reduced by 16.11%	Reduced by 16.20%	Reduced by 15.81%	Reduced by 25.88%

PolarDB for MySQL

Read-only scenario

Table 4. Test data in the read-only scenario
Concurrency level	pfsoff	pfson_conoff_insoff	pfson_conon_insoff	pfson_conon_ins_sqlon	pfson_conoff_inson	pfson_conon_inson
1	3,389	3,296	3,233	3,179	3,249	3,016
1	Baseline value	Reduced by 2.74%	Reduced by 4.62%	Reduced by 2.56%	Reduced by 4.13%	Reduced by 11.04%
2	8,868	8,632	8,601	8,588	8,654	8,213
2	Baseline value	Reduced by 3.08%	Reduced by 3.78%	Reduced by 3.16%	Reduced by 2.84%	Reduced by 7.39%
4	16,965	14,485	15,123	14,926	14,942	13,053
4	Baseline value	Reduced by 14.62%	Reduced by 10.86%	Reduced by 12.02%	Reduced by 11.92%	Reduced by 23.06%
8	25,024	22,614	23,286	22,781	23,017	21,659
8	Baseline value	Reduced by 9.63%	Reduced by 9.01%	Reduced by 8.96%	Reduced by 8.14%	Reduced by 14.45%
16	38,869	35,846	35,483	34,162	34,288	32,387
16	Baseline value	Reduced by 9.88%	Reduced by 8.95%	Reduced by 12.11%	Reduced by 11.79%	Reduced by 16.68%
32	36,768	31,981	31,187	30,976	31,025	29,652
32	Baseline value	Reduced by 13.02%	Reduced by 15.18%	Reduced by 15.75%	Reduced by 15.62%	Reduced by 19.35%
64	49,186	42,230	42,776	41,791	41,747	39,877
64	Baseline value	Reduced by 14.14%	Reduced by 13.07%	Reduced by 15.03%	Reduced by 15.12%	Reduced by 18.93%
128	42,958	39,201	40,280	39,797	39,990	37,532
128	Baseline value	Reduced by 11.88%	Reduced by 9.08%	Reduced by 10.43%	Reduced by 10.11%	Reduced by 12.63%
256	45,090	41,026	39,869	39,556	40,414	35,602
256	Baseline value	Reduced by 9.01%	Reduced by 11.58%	Reduced by 12.27%	Reduced by 10.37%	Reduced by 21.04%
512	46,279	41,004	40,353	39,488	39,337	34,023
512	Baseline value	Reduced by 11.14%	Reduced by 12.81%	Reduced by 14.67%	Reduced by 15.02%	Reduced by 26.48%
1,024	47,231	41,533	40,032	39,509	39,462	33,696
1,024	Baseline value	Reduced by 12.06%	Reduced by 15.24%	Reduced by 16.35%	Reduced by 16.45%	Reduced by 28.66%

Write-only scenario

Table 5. Test data in the write-only scenario
Concurrency level	pfsoff	pfson_conoff_insoff	pfson_conon_insoff	pfson_conon_ins_sqlon	pfson_conoff_inson	pfson_conon_inson
1	3,505	3,364	3,354	3,269	3,293	3,122
1	Baseline value	Reduced by 4.07%	Reduced by 5.41%	Reduced by 6.73%	Reduced by 6.05%	Reduced by 10.93%
2	7,844	7,452	7,603	7,606	7,547	7,281
2	Baseline value	Reduced by 4.56%	Reduced by 4.22%	Reduced by 5.09%	Reduced by 3.44%	Reduced by 7.18%
4	13,231	12,648	12,057	12,106	12,193	10,676
4	Baseline value	Reduced by 4.38%	Reduced by 8.87%	Reduced by 8.30%	Reduced by 7.85%	Reduced by 19.31%
8	25,227	22,648	22,534	22,360	22,824	20,431
8	Baseline value	Reduced by 10.22%	Reduced by 10.68%	Reduced by 11.36%	Reduced by 9.53%	Reduced by 19.01%
16	27,119	26,154	26,347	25,093	26,669	22,170
16	Baseline value	Reduced by 13.66%	Reduced by 11.95%	Reduced by 14.74%	Reduced by 12.95%	Reduced by 18.25%
32	39,550	34,182	31,768	31,101	31,750	30,014
32	Baseline value	Reduced by 13.57%	Reduced by 19.68%	Reduced by 21.36%	Reduced by 19.72%	Reduced by 24.11%
64	37,915	31,223	30,672	30,661	31,220	29,514
64	Baseline value	Reduced by 17.65%	Reduced by 19.13%	Reduced by 19.12%	Reduced by 17.66%	Reduced by 22.16%
128	34,692	31,320	31,884	32,135	31,910	29,423
128	Baseline value	Reduced by 16.88%	Reduced by 12.08%	Reduced by 10.76%	Reduced by 14.11%	Reduced by 15.22%
256	33,390	31,704	31,814	32,018	31,124	29,348
256	Baseline value	Reduced by 7.67%	Reduced by 7.14%	Reduced by 5.41%	Reduced by 9.88%	Reduced by 12.11%
512	34,033	31,156	31,196	31,783	31,597	30,157
512	Baseline value	Reduced by 8.45%	Reduced by 8.34%	Reduced by 6.61%	Reduced by 7.16%	Reduced by 11.39%
1,024	36,855	33,862	33,723	33,613	33,232	32,956
1,024	Baseline value	Reduced by 8.12%	Reduced by 8.52%	Reduced by 8.81%	Reduced by 9.83%	Reduced by 10.58%

Read/write scenario

Table 6. Test data in the read/write scenario
Concurrency level	pfsoff	pfson_conoff_insoff	pfson_conon_insoff	pfson_conon_ins_sqlon	pfson_conoff_inson	pfson_conon_inson
1	3,305	2,754	2,843	2,758	2,741	2,614
1	Baseline value	Reduced by 16.67%	Reduced by 13.98%	Reduced by 16.55%	Reduced by 17.07%	Reduced by 20.91%
2	8,668	7,304	7,610	7,420	7,498	7,182
2	Baseline value	Reduced by 15.74%	Reduced by 12.21%	Reduced by 14.42%	Reduced by 13.52%	Reduced by 17.14%
4	14,965	13,031	13,427	13,178	13,164	12,754
4	Baseline value	Reduced by 12.92%	Reduced by 10.28%	Reduced by 11.94%	Reduced by 12.03%	Reduced by 14.77%
8	23,068	21,933	21,663	21,354	21,130	20,250
8	Baseline value	Reduced by 9.94%	Reduced by 6.09%	Reduced by 8.68%	Reduced by 8.41%	Reduced by 12.22%
16	35,869	28,119	28,443	28,629	27,936	26,957
16	Baseline value	Reduced by 14.96%	Reduced by 17.71%	Reduced by 16.74%	Reduced by 16.95%	Reduced by 24.85%
32	38,876	33,342	32,980	31,980	32,855	30,656
32	Baseline value	Reduced by 14.24%	Reduced by 15.17%	Reduced by 17.74%	Reduced by 15.49%	Reduced by 21.14%
64	42,186	33,311	33,948	33,068	33,659	31,305
64	Baseline value	Reduced by 20.86%	Reduced by 19.53%	Reduced by 21.61%	Reduced by 20.21%	Reduced by 25.79%
128	40,359	35,742	35,334	35,451	35,488	32,899
128	Baseline value	Reduced by 11.44%	Reduced by 12.45%	Reduced by 12.16%	Reduced by 12.07%	Reduced by 18.48%
256	40,090	36,243	33,570	36,115	35,949	33,125
256	Baseline value	Reduced by 9.67%	Reduced by 16.26%	Reduced by 9.92%	Reduced by 10.33%	Reduced by 17.37%
512	41,220	36,626	36,087	36,302	36,286	32,451
512	Baseline value	Reduced by 11.15%	Reduced by 12.45%	Reduced by 11.93%	Reduced by 11.98%	Reduced by 20.27%
1,024	40,055	36,699	36,625	36,585	36,756	32,860
1,024	Baseline value	Reduced by 8.38%	Reduced by 8.56%	Reduced by 8.66%	Reduced by 8.24%	Reduced by 17.96%