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.
I would have rather seen a default with PS=on, but with all instrumentation and consumrs turned off. Then you don’t need to restart to turn the instrumentation on.
Hi @danilvaneeden:disqus – I’m willing to bet that even with P_S=on, and instrumentation/consumers turned off, you see some degradation – though I haven’t tried this personally yet – have you?
There ofcourse is some overhead. I haven’t tested the amount of overhead/degradation myself.
Dimitri did write a blog post about it: http://dimitrik.free.fr/blog/archives/2013/07/mysql-performance-why-performance-schema-overhead.html
I do like to use P_S, it is a source of very useful information. And with the userstats, ‘pert top’ and the slow query log most of the P_S information is also available without P_S. But tools like Workbench and HeidiSQL can’t easily get all data from the server. So P_S might work better if the DBA and sysadmin roles are split.
I think the userstats is a bit more user friendly, but can’t do everything P_S can. And P_S is much more usable with the SYS schema and the support in Workbench etc.
And userstats + slow query log will also have overhead.
My main points are:
– P_S has more settings than ON/OFF
– I don’t like to restart MySQL, I would like to do everything online.
– yes, P_S has more settings that on/off, so lets presume you are using them, you will have more settings than just on, so adding an extra line should be easy? — even oracle tells you to configure it: http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html
– i agree about the restarts, its kinda annoying – much better if it was dynamic.
– Does it make sense to extend user statistics?
Configuring P_S is not a problem as it should be configured indeed. But I would like to be able to configure without a reboot.
I don’t think extending user statistics would be useful as that would result in user stats being more similar to P_S and having similar drawbacks.
It would be helful to have some easy way for client utilities to get some info and not having to known if it’s backed by user stats or P_S.
thanks for your feedback, I think some of this is worth filing upstream as well.
It is noteworthy that Oracle’s Enterprise Monitor depends on P_S in recent versions (as it does not support ‘mysql proxy’ anymore). So decisions about P_S developments could very well have been influenced by Oracle sales department. PS is pretty much ‘tailored for Enterprise Monitor’ more than it is ‘tailored for Community’ IMO.
yes, this is absolutely true. That said I doubt people using Enterprise Monitor will be using MariaDB
“We discussed this on the mailing list, nobody objected” is an interesting approach to making such a drastic change within a GA release. Exactly the kind of surprises that customers want to have when upgrading their servers…
This is why I said I don’t like such decisions in GA releases (read paragraph 2). Also, there is data from the user feedback plugin. And then let’s not forget that the data is in the release notes too — which is something all are encouraged to read even in point releases.
As I said, while I may not like such decisions, it was asked in public and people had some weeks to discuss this. This is a much better decision making process than just switching it off and then telling users about it later.
All things considered, its a pretty easy thing to turn on. And if you are using it, you have to add more options, anyway…
I agree with others.
* Do NOT make functional or behavioural changes in a minor version upgrade. This bites, causes pain and then people get stuck trying to convince you to change things back. It does not affect developers it affects people that support and upgrade production systems and where downtime is painful and upgrades are kept to the minimum number possible. Unexpected changes, of whatever sort, should really be avoided.
* P_S is good. Yes, it has an overhead and it is true that most people do not know exactly what that overhead is, which is unfortunate. It provides a wealth of information which is something most DBAs have been asking Oracle/MySQL/MariaDB etc for for some time. It is good in MySQL 5.6 and it is good in MariaDB 10.0. If you are blind you’ll find it much harder to see where your poblems are, and this is exactly what P_S helps with. For those few people running with their system at close to 90% and needing this extra gain, they probably know how to configure P_S with most or all instrumentation turned off. Of course that does not actually remove all of the overheads and perhaps what is good from this post is that it suggests that it might be good to build two versions of MySQL, from the same source:
A P_S-enabled version and a P_S-free version, both packages configured identically apart from having P_S enabled or not so the DBA can choose one or the other and interchange them at will. _That_ would be good, and may give a bit more help, but it might just be too much effort and the extra few percentage points gained in performance may not in the end be enough for the difference to be worth it.
People who are new to MySQL or MariaDB often want to see what is inside their server and what it is doing and why. The more seasoned DBAs have probably learnt how to figure out some of this themselves, but do not cut off this useful tool from the people who probably most need it.
how to dynamically enable perf scheme?