Eventually I will repeat this test for workloads with more concurrency because there are overheads that won't be apparent at low-concurrency. The overhead is larger for simple queries, like sysbench point-query, and larger for complex queries. The overhead is also larger for in-memory workloads.
tl;dr - it isn't the perf schema
Configuration
I tested MySQL using upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. My servers are described here but I only share results for the i5 NUC.
I enabled support for the performance schema at compile time. When tests were run I set performance_schema to 1 to enable it and to 0 to disable it. Nothing else was set in my.cnf for the perf schema.
My usage of sysbench, including the tests run, and the my.cnf used for each database engine is described here for MySQL 5.6, 5.7 and 8. The my.cnf files I share were for the i3 NUC. For the i5 NUC the InnoDB buffer pool and IO capacity options were increased using these values. I use the same server for mysqld and the sysbench clients. The binlog is enabled but sync-on-commit is disabled. Sysbench is run with 4 tables and 1M rows per table for 1, 2 and 4 concurrent clients. The database fits in the InnoDB buffer pool.
Results
The QPS for all tests is here. The next table is QPS relative to MySQL 5.6.35 with the perf schema enabled for each engine/configuration. For each release there isn't much difference between enabling (*-ps) and disabling (*-nops) the perf schema.
legend:
* 56-ps, 56-nops - 5.6.35 with and without perf schema
* 57-ps, 57-nops - 5.7.17 with and without perf schema
* 80-ps, 80-nops - 8.0.1 with and without perf schema
56-ps 56-nops 57-ps 57-nops 80-ps 80-nops release/test
1.00 1.02 0.66 0.68 0.73 0.76 update-index
1.00 1.02 0.57 0.59 0.61 0.64 update-nonindex
1.00 1.03 0.59 0.61 0.58 0.60 read-write.range100
1.00 1.03 0.59 0.59 0.58 0.58 read-write.range10000
1.00 1.05 0.61 0.61 0.57 0.60 read-only.range10
1.00 1.03 0.60 0.60 0.60 0.60 read-only.range10000
1.00 1.05 0.60 0.59 0.56 0.60 point-query
1.00 1.02 0.69 0.73 0.67 0.68 insert
The next table is the relative QPS for MySQL version X without the perf schema relative to the QPS for version X with the perf schema. It shows the relative the gain in QPS from disabling the perf schema for a given release. The maximum gain is about 7% but in most cases it is less than 5%. So the perf schema overhead doesn't explain the CPU regression from MySQL 5.6 to 8. It isn't a surprise that the largest overhead occurs for the point-query test because that test has the least complex queries.
5.6 5.7 8.0 release/test
1.02 1.03 1.04 update-index
1.02 1.03 1.05 update-nonindex
1.03 1.02 1.03 read-write.range100
1.03 1.00 1.00 read-write.range10000
1.05 1.00 1.05 read-only.range10
1.03 1.01 1.00 read-only.range10000
1.05 0.99 1.07 point-query
1.02 1.06 1.02 insert
Charts
Finally I have charts of the QPS for all tests. I don't have commentary for each chart. For the results below I use -ps for configurations when the perf schema was enabled and -nops when it was disabled.
I tested MySQL using upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. My servers are described here but I only share results for the i5 NUC.
I enabled support for the performance schema at compile time. When tests were run I set performance_schema to 1 to enable it and to 0 to disable it. Nothing else was set in my.cnf for the perf schema.
My usage of sysbench, including the tests run, and the my.cnf used for each database engine is described here for MySQL 5.6, 5.7 and 8. The my.cnf files I share were for the i3 NUC. For the i5 NUC the InnoDB buffer pool and IO capacity options were increased using these values. I use the same server for mysqld and the sysbench clients. The binlog is enabled but sync-on-commit is disabled. Sysbench is run with 4 tables and 1M rows per table for 1, 2 and 4 concurrent clients. The database fits in the InnoDB buffer pool.
Results
The QPS for all tests is here. The next table is QPS relative to MySQL 5.6.35 with the perf schema enabled for each engine/configuration. For each release there isn't much difference between enabling (*-ps) and disabling (*-nops) the perf schema.
legend:
* 56-ps, 56-nops - 5.6.35 with and without perf schema
* 57-ps, 57-nops - 5.7.17 with and without perf schema
* 80-ps, 80-nops - 8.0.1 with and without perf schema
56-ps 56-nops 57-ps 57-nops 80-ps 80-nops release/test
1.00 1.02 0.66 0.68 0.73 0.76 update-index
1.00 1.02 0.57 0.59 0.61 0.64 update-nonindex
1.00 1.03 0.59 0.61 0.58 0.60 read-write.range100
1.00 1.03 0.59 0.59 0.58 0.58 read-write.range10000
1.00 1.05 0.61 0.61 0.57 0.60 read-only.range10
1.00 1.03 0.60 0.60 0.60 0.60 read-only.range10000
1.00 1.05 0.60 0.59 0.56 0.60 point-query
1.00 1.02 0.69 0.73 0.67 0.68 insert
The next table is the relative QPS for MySQL version X without the perf schema relative to the QPS for version X with the perf schema. It shows the relative the gain in QPS from disabling the perf schema for a given release. The maximum gain is about 7% but in most cases it is less than 5%. So the perf schema overhead doesn't explain the CPU regression from MySQL 5.6 to 8. It isn't a surprise that the largest overhead occurs for the point-query test because that test has the least complex queries.
5.6 5.7 8.0 release/test
1.02 1.03 1.04 update-index
1.02 1.03 1.05 update-nonindex
1.03 1.02 1.03 read-write.range100
1.03 1.00 1.00 read-write.range10000
1.05 1.00 1.05 read-only.range10
1.03 1.01 1.00 read-only.range10000
1.05 0.99 1.07 point-query
1.02 1.06 1.02 insert
Charts
Finally I have charts of the QPS for all tests. I don't have commentary for each chart. For the results below I use -ps for configurations when the perf schema was enabled and -nops when it was disabled.
%20_%20(QPS%20for%208.0.34).png)
No comments:
Post a Comment