PERFORMANCE_SCHEMA disabled in MariaDB 10.0.12
Astute readers of the release notes for MariaDB 10.0.12 will notice that there is a line that reads: performance_schema is now disabled by default.
We didn’t come to this decision by accident. Recently at the SkySQL company meeting in Budapest, we did have some time to break out into our usual working teams to talk about our daily operations. Team MariaDB had a debate about PERFORMANCE_SCHEMA and how it was left on by mistake in 10.0 GA as there was a decision to turn it off. Personally, I don’t like introducing such changes in a GA release, and there was no archive of such a discussion, so the next best thing to do was to ask the MariaDB developers and users via a post to both maria-developers and maria-discuss (3 June 2014) and to ensure that a Jira ticket existed (MDEV-6249).
But first, let’s delve into a little background information for context of this discussion. Elena started investigating a query performance issue reported on IRC, and she found that with default settings the query performance dropped tremendously with PERFORMANCE_SCHEMA enabled. We had seen that the WebScaleSQL folk had disabled PERFORMANCE_SCHEMA and there was discussion on the mailing list about a loss in performance (“Our perf testing agrees with your assessment (we see about a 5%-6% perf hit when it’s included and on, and a 2%-3% hit when it’s included but off)”).
On the maria-developers and maria-discuss lists, no one complained about having such a change. Hence the decision to disable it by default now. The alternative to this in MariaDB that comes without performance overhead is user statistics. I read a comment somewhere that there is constant evolution of PERFORMANCE_SCHEMA but the user statistics haven’t changed in a while (since MariaDB 5.2), though reliable sources tell me that more work is being done on this. Do MariaDB users want to see evolution of user statistics?
So from a user standpoint, the best way to find out if PERFORMANCE_SCHEMA is ON or OFF is to run SHOW VARIABLES LIKE 'perf%';. On MariaDB 10.0.11, you will see that it is ON but on 10.0.12 you will see that it is OFF. If you are planning to enable it in a development environment, all you have to do is edit your my.cnf to have performance_schema=1 and restart your server to get to using it again.
It looks like the decision might have been the right one for the time being, looking at the recently resurfaced mysql#68514.
