This topic describes the performance changes of your database instance in the various parameter configuration scenarios of Performance Schema.

Testing scenario

  • In this topic, an ApsaraDB RDS for MySQL instance is used for testing. The following table describes the specifications of the instance.
    Edition Instance family Instance type Number of cores and memory capacity Maximum number of connections Maximum IOPS
    High-availability Edition General-purpose mysql.n4.large.2c 4 cores, 16 GB memory 8000 2800
  • 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 parameter configuration scenarios are created for testing based on whether performance_schema and the consumers and instrumentation configuration items of performance_schema are enabled. For more information, see Parameter configuration scenarios.

The following table lists the average queries per second (QPS) of your instance at a concurrency level of 32 after the CPU utilization of your instance reaches 100% in various parameter configuration scenarios. The system has optimal performance in the pfsoff scenario. For the test data at different concurrency levels, see Test data.

Note The pfson_conon_ins_sqlon scenario indicates that the performance insight feature provided by Database Autonomy Service (DAS) is enabled.
Parameter configuration scenario Read-only Read/write Write-only
pfsoff 47176 Baseline value 37999 Baseline value 39940 Baseline value
pfson_conoff_insoff 41111 Reduced by 12.86% 33170 Reduced by 12.71% 32327 Reduced by 19.06%
pfson_conon_insoff 41773 Reduced by 11.45% 33568 Reduced by 11.66% 31434 Reduced by 21.30%
pfson_conon_ins_sqlon 39889 Reduced by 15.44% 33056 Reduced by 13.01% 31865 Reduced by 20.22%
pfson_conoff_inson 39344 Reduced by 16.61% 32698 Reduced by 13.95% 31698 Reduced by 20.64%
pfson_conon_inson 39168 Reduced by 16.97% 32280 Reduced by 15.05% 30834 Reduced by 22.81%

Parameter configuration scenarios

Various parameter configuration scenarios are created based on whether performance_schema and the consumers and instrumentation configuration items of performance_schema are enabled.
Note For information about how to enable or disable performance_schema, see Modify the parameters of an ApsaraDB RDS for MySQL instance.
Scenario Description
pfsoff performance_schema is disabled.
pfson_conoff_insoff
  • performance_schema 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
  • performance_schema 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
  • performance_schema 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 The pfson_conon_ins_sqlon scenario indicates that the performance insight feature is enabled.
pfson_conoff_inson
  • performance_schema 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
  • performance_schema 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 at different concurrency levels after the CPU utilization of your instance reaches 100%.

Read-only scenario

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 3456 3209 3233 3179 3249 3116
Baseline value Reduced by 7.16% Reduced by 6.45% Reduced by 8.02% Reduced by 5.98% Reduced by 9.84%
2 9432 8668 8701 8588 8854 8313
Baseline value Reduced by 8.10% Reduced by 7.75% Reduced by 8.95% Reduced by 6.13% Reduced by 11.86%
4 16485 14965 15123 14926 14942 14053
Baseline value Reduced by 9.22% Reduced by 8.26% Reduced by 9.46% Reduced by 9.36% Reduced by 14.75%
8 25614 23024 23286 22781 23017 21659
Baseline value Reduced by 10.11% Reduced by 9.09% Reduced by 11.06% Reduced by 10.14% Reduced by 15.44%
16 39846 35869 35483 34162 34288 33387
Baseline value Reduced by 9.98% Reduced by 10.95% Reduced by 13.14% Reduced by 13.95% Reduced by 16.21%
32 47176 41111 41773 39889 39344 39168
Baseline value Reduced by 12.86% Reduced by 11.45% Reduced by 15.44% Reduced by 16.61% Reduced by 16.97%
64 49230 42186 42776 41791 41747 38877
Baseline value Reduced by 14.31% Reduced by 13.11% Reduced by 15.11% Reduced by 15.20% Reduced by 21.03%
128 48201 41458 41280 40797 40990 36532
Baseline value Reduced by 13.99% Reduced by 14.36% Reduced by 15.36% Reduced by 14.96% Reduced by 24.21%
256 46982 40090 39869 39556 40414 34602
Baseline value Reduced by 14.67% Reduced by 15.14% Reduced by 15.81% Reduced by 13.98% Reduced by 26.35%
512 47026 41279 40353 39488 39337 34023
Baseline value Reduced by 15.22% Reduced by 14.19% Reduced by 16.03% Reduced by 16.35% Reduced by 27.65%
1024 47533 40104 40032 39509 39462 33696
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

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 3614 3305 3293 3269 3354 3222
Baseline value Reduced by 8.56% Reduced by 8.88% Reduced by 9.56% Reduced by 7.19% Reduced by 10.86%
2 8452 7644 7547 7606 7603 7281
Baseline value Reduced by 9.56% Reduced by 10.71% Reduced by 10.01% Reduced by 10.04% Reduced by 13.86%
4 13648 12231 12193 12106 12057 11676
Baseline value Reduced by 10.38% Reduced by 10.66% Reduced by 11.30% Reduced by 11.66% Reduced by 14.45%
8 25648 22227 22824 22360 22534 21431
Baseline value Reduced by 13.34% Reduced by 11.01% Reduced by 12.82% Reduced by 12.14% Reduced by 16.44%
16 32154 27119 27669 27093 27347 26170
Baseline value Reduced by 15.66% Reduced by 13.95% Reduced by 15.74% Reduced by 14.95% Reduced by 18.61%
32 39940 31434 31434 31865 31698 30834
Baseline value Reduced by 21.30% Reduced by 19.06% Reduced by 20.22% Reduced by 20.64% Reduced by 22.81%
64 39123 30915 31220 30661 30672 29514
Baseline value Reduced by 20.98% Reduced by 20.20% Reduced by 21.63% Reduced by 21.06% Reduced by 24.56%
128 41320 32692 32610 32135 32184 29423
Baseline value Reduced by 20.88% Reduced by 21.08% Reduced by 22.23% Reduced by 22.11% Reduced by 28.79%
256 42004 33322 33124 32838 32814 30348
Baseline value Reduced by 20.67% Reduced by 21.14% Reduced by 21.82% Reduced by 21.88% Reduced by 27.75%
512 43156 34033 33597 33783 33196 30157
Baseline value Reduced by 21.14% Reduced by 22.15% Reduced by 21.72% Reduced by 23.08% Reduced by 30.12%
1024 47562 37056 36232 37113 36723 32256
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

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 2997 2754 2844 2758 2741 2672
Baseline value Reduced by 8.1% Reduced by 5.12% Reduced by 7.98% Reduced by 8.55% Reduced by 10.86%
2 8135 7304 7610 7420 7498 7226
Baseline value Reduced by 10.22% Reduced by 6.45% Reduced by 8.79% Reduced by 7.83% Reduced by 11.17%
4 14568 13031 13427 13178 13164 12754
Baseline value Reduced by 10.55% Reduced by 7.83% Reduced by 9.54% Reduced by 9.64% Reduced by 12.45%
8 24658 21933 22663 21854 22130 21250
Baseline value Reduced by 11.05% Reduced by 8.09% Reduced by 11.37% Reduced by 10.25% Reduced by 13.82%
16 31562 28119 28443 27629 27936 26957
Baseline value Reduced by 10.91% Reduced by 9.88% Reduced by 12.46% Reduced by 11.49% Reduced by 14.61%
32 37999 33170 33568 33056 32698 32280
Baseline value Reduced by 12.71% Reduced by 11.66% Reduced by 13.01% Reduced by 13.95% Reduced by 15.05%
64 39120 33311 33948 33068 33659 31605
Baseline value Reduced by 14.85% Reduced by 13.22% Reduced by 15.47% Reduced by 13.96% Reduced by 19.21%
128 41687 35742 35334 35451 35488 32899
Baseline value Reduced by 14.26% Reduced by 15.24% Reduced by 14.96% Reduced by 14.87% Reduced by 21.08%
256 42654 36243 33570 36115 35949 33133
Baseline value Reduced by 15.03% Reduced by 16.14% Reduced by 15.33% Reduced by 15.72% Reduced by 22.32%
512 43156 36626 36087 36302 36286 32518
Baseline value Reduced by 15.13% Reduced by 16.38% Reduced by 15.88% Reduced by 15.92% Reduced by 24.65%
1024 43658 36699 36625 36585 36756 32359
Baseline value Reduced by 15.94% Reduced by 16.11% Reduced by 16.20% Reduced by 15.81% Reduced by 25.88%