Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Best Practices for Setting the Parameter performance_schema: Overview

Best Practices for Setting the Parameter performance_schema: Overview

This article compares PFS and other tools and the performance differences between scenarios where PFS is enabled and disabled.

The open-source MySQL has supported performance_schema (hereinafter referred to as PFS) since 5.5. After continuous improvement and optimization in subsequent versions, PFS has become very powerful for performance diagnosis and optimization, so that SQL problems and lock waiting events can be displayed clearly. However, it incurs certain performance costs to enable PFS. This article compares PFS and other tools and the performance differences between scenarios where PFS is enabled and disabled.

PFS Features

First of all, PFS provides more features as a built-in problem diagnosis and analysis tool. DAS is an external tool. If you want to analyze it in detail, you can open the audit log and enable DAS as needed.

1

Performance

  • Testing parameter template: asynchronous parameter template
  • Testing specifications:

4C16G, rds.mysql.m1.medium

2

  • Testing scenario:
  • Table quantity: 20
  • Table Size: 10,000,000 records
  • Concurrency level of stress testing: 8, 16, 32, 64, 128, 256, 512

Results

  • Unless you want to check for problems that are difficult to find, it is generally not recommended to enable PFS. The impact of DAS on the DB Server is negligible.
  • The performance is roughly the same before the CPU reaches the bottleneck. After the CPU reaches the bottleneck, the performance advantages of pfsoff are highlighted gradually. The performance of pfson_conon_inson is the worst. The performance of the other three scenarios is roughly the same. Performance: pfsoff > pfson_conoff_insoff ≈ pfson_conon_insoff ≈ pfson_conoff_inson > pfson_conon_inson

After the CPU reaches the bottleneck, take the average value of the performance with the concurrency level of 32. The performance differences under other concurrency levels are shown in the following table.

3

Note:

  • pfsoff: The performance schema is disabled.
  • pfson_conon_insoff: Performance Schema is enabled, all consumers are enabled, and all instrumentation is disabled.
  • pfson_conoff_inson: Performance Schema is enabled, all consumers are disabled, and all instrumentation is enabled.
  • pfson_conon_inson: Performance Schema is enabled, all consumers are enabled, and all instrumentation is enabled.
  • pfson_conoff_insoff: Performance Schema is enabled, all consumers are disabled, and all instrumentation is disabled.
  • The difference is based on the base value of performance with pfsoff and concurrency level of 32.

read-only

4
5

read-write

6
7

write-only

8
9

Memory Usage Changes

After PFS is enabled, the memory applied by PFS will not be released. The memory usage increases by 5%. It is estimated that memory usage will increase more in complex scenarios.

Additional Information

Alibaba Cloud ApsaraDB RDS for MySQL has developed Performance Insight, a tool for instance load monitoring, association analysis, and performance tuning. For more information, please see the documentation below: https://www.alibabacloud.com/help/en/doc-detail/132200.html

MySQL Database performance Open Source ApsaraDB RDS for MySQL Best Practice DAS PFS
0 0 0
Share on

Read previous post:

Create an E-Commerce Web App with Node.js Express Framework and MongoDB on Alibaba Cloud

Read next post:

Creating a Web Game Map App Using Leaflet, PostgreSQL/PostGIS, and Redis on Alibaba Cloud

ApsaraDB

462 posts | 101 followers

You may also like

Comments

ApsaraDB

462 posts | 101 followers

Related Products

More Posts by ApsaraDB

See All