Performance Schema Benchmarks: OLTP RW

In this post, we’ll look at Performance Schema benchmarks for OLTP Read/Write workloads.

I am in love with Performance Schema and talk a lot about it. Performance Schema is a MySQL troubleshooting instrument. Earlier versions had performance issues. Many of these issues are fixed now and the default options work quickly and reliably. However, there is no free lunch! It is expected that the more instruments you turn ON, the more overhead you’ll have.

The advice I give my customers is that when in doubt, only turn on the instruments that are required to troubleshoot your issue. Many of them ask, "What exactly are the overhead costs for one instrumentation or another?" I believe the best answer is, "Test on your system!" No generic benchmark can exactly repeat a workload on your site. However, while I was working on the OpenSource Databases on Big Machines project, I decided to test the performance of Performance Schema as well.

I only tested a Read/Write workload. I used the same fast machine (144 CPU cores), the same MySQL options and the same SysBench commands that I described in this post. The option innodb_flush_method was changed to O_DIRECT, because it’s more reasonable for real-life workloads. I also upgraded the MySQL Server version to Oracle’s MySQL 5.7.17. The reason for the upgrade was to test if the issue described in this post is repeatable with latest Oracle MySQL server version. However, since I tested Performance Schema, the effect on Percona Server for MySQL should be same.

I tested nine different scenarios.

1. All disabled. Performance Schema is on, but all instruments and consumers are disabled.
   

   update setup_consumers set enabled='no';
   update setup_instruments set enabled='no';

2. All enabled. Performance Schema is on and all instruments and consumers are enabled.
   

   update setup_instruments set enabled='yes';
   update setup_consumers set enabled='yes';

3. Default. Performance Schema is on and only default instruments and consumers are enabled.
4. “MDL only”: only Metadata Lock instrumentation is enabled.
   

   update setup_consumers set enabled='no';
   update setup_instruments set enabled='no';
   update setup_consumers set enabled='yes' where name= 'global_instrumentation';
   update setup_consumers set enabled='yes' where name= 'thread_instrumentation';
   update setup_instruments set enabled='yes' where name='wait/lock/metadata/sql/mdl';

5. Memory only. Only Memory instrumentation enabled.
   

   update setup_consumers set enabled='no';
   update setup_instruments set enabled='no';
   update setup_consumers set enabled='yes' where name= 'global_instrumentation';
   update setup_consumers set enabled='yes' where name= 'thread_instrumentation';
   update setup_instruments set enabled='yes' where name like 'memory%';

6. Stages and statements. Only Stages and Statements instrumentation enabled.
   

   update setup_consumers set enabled='no';
   update setup_instruments set enabled='no';
   update setup_consumers set enabled='yes' where name= 'global_instrumentation';
   update setup_consumers set enabled='yes' where name= 'thread_instrumentation';
   update setup_instruments set enabled='yes' where name like 'statement%';
   update setup_consumers set enabled='yes' where name like 'events_statements%';
   update setup_instruments set enabled='yes' where name like 'stage%';
   update setup_consumers set enabled='yes' where name like 'events_stages%';

7. Stages only. Only Stages instrumentation enabled.
   

   update setup_consumers set enabled='no';
   update setup_instruments set enabled='no';
   update setup_consumers set enabled='yes' where name= 'global_instrumentation';
   update setup_consumers set enabled='yes' where name= 'thread_instrumentation';
   update setup_instruments set enabled='yes' where name like 'stage%';
   update setup_consumers set enabled='yes' where name like 'events_stages%';

8. Statements only. Only Statements instrumentation enabled.
   

   update setup_consumers set enabled='no';
   update setup_instruments set enabled='no';
   update setup_consumers set enabled='yes' where name= 'global_instrumentation';
   update setup_consumers set enabled='yes' where name= 'thread_instrumentation';
   update setup_instruments set enabled='yes' where name like 'statement%';
   update setup_consumers set enabled='yes' where name like 'events_statements%';

9. Waits only. Only Waits instrumentation enabled.
   

   update setup_consumers set enabled='no';
   update setup_instruments set enabled='no';
   update setup_consumers set enabled='yes' where name= 'global_instrumentation';
   update setup_consumers set enabled='yes' where name= 'thread_instrumentation';
   update setup_instruments set enabled='yes' where name like 'wait%' ;
   update setup_consumers set enabled='yes' where name like 'events_waits%';

Here are the overall results.

As you can see, some instrumentation only slightly affects performance, while others affect it a lot. I created separate graphs to make the picture clearer.

As expected, enabling all instrumentation makes performance lower:

Does this mean to use Performance Schema, you need to start the server with it on and then disable all instruments? No! The default options have very little effect on performance:

The same is true for Metadata Locks, Memory, and Statements instrumentation:

Regarding statements, I should note that I used prepared statements (which are instrumented in version 5.7). However, it makes sense to repeat the tests without prepared statements.

The Stages instrumentation starts affecting performance:

However, the slowdown is reasonable and it happens only after we reach 32 concurrent threads. It still provides great insights on what is happening during query execution.

The real performance killer is Waits instrumentation:

It affects performance close to the same way as all instruments on.

Conclusion

Using Performance Schema with the default options, Memory, Metadata Locks, and Statements instrumentation doesn’t have a great impact on read-write workload performance. You might notice slowdowns with Stages instrumentation after reaching 32 actively running parallel connections. The real performance killer is Waits instrumentation. Even with it on, you will start to notice a performance drop only after 10,000 transactions per second.