Performance Schema is used extensively both internally and within the MySQL community, and I expect even more usage with the new SYS Schema and the Performance Schema enhancements in 5.7. Performance Schema is the single best tool available for monitoring MySQL Server internals and execution details at a lower level. Having said that, we are also no stranger to the fact that any monitoring tool comes with an additional cost to performance. Hence It has always been an important question to find out just how much it costs us when Performance Schema is turned ON and to see what we can do to make it perform as fast as possible.

I have been using MySQL Performance Schema for the past year. This is my first blog on Performance Schema and here I am primarily concerned with the performance impact and characteristics around the default values.

Performance Schema Default ON vs OFF

The question I will try and answer here is this: How well does MySQL scale with performance_schema=ON (default parameters and configuration) compared to performance_schema=OFF?

Test Details

I used Sysbench to get TPS benchmarks. Just to ensure that I have some variation in my tests I ran Sysbench in both the CPU bound and the Disk bound environments to gather the stats. All tests were performed on the same server and with the same MySQL Server build. I used the latest development release (at the time), 5.7.6-m16, for my tests. With MySQL 5.7.6, 311 instruments are enabled by default.

Test Results

I was very happy to see the answers after the tests ran! They clearly show that the numbers are quite reasonable (within 1% – 3%) if we consider the huge benefits offered by Performance Schema with the default settings. More Details below:

CPU Bound

Sysbench Read Only Mode

Sysbench Read Write Mode

Disk Bound

Sysbench Read Only Mode

Sysbench Read Write Mode

performance_schema=ON shows a dip of 0.45%

Conclusion

In my tests we see average overhead results of:

• Just over 2% in the CPU Bound OLTP RW (2.23%) and OLTP_RO (2.40%) runs
• Less than 1% in the Disk Bound OLTP RW (0.41%) and OLTP_RO (0.92%) runs

Overall, as we see I think you can expect a performance impact of approximately 1-3% with performance_schema=ON vs OFF (again, using the default configuration). The most important takeaway is that while there are still some dips in raw performance numbers—which is expected—the overall impact is relatively small. We will also continue to do all that we can to lower the overhead and performance impact of Performance Schema even further in the future! I hope that these benchmarks may help anyone looking forward to using Performance Schema extensively, but are concerned about what performance impact may come with it.

As I’ve demonstrated here—due to all of the helpful input from the MySQL Community, and the work that we’ve done to lower the related impact and overhead—the Performance Schema can truly be said to offer great power, and do so without great cost!

That’s all for now. Thank you for using MySQL!

Appendix: Test Details.

Table1: 311 Instruments

Table2: MySQL Details

Table3: Test Machine Details

Table4: Sysbench Configuration Details

Table5: MySQLD Configuration Details