Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Recommendation to turn on performance_schema #579

Closed
MichaIng opened this issue Jan 24, 2022 · 7 comments
Closed

Recommendation to turn on performance_schema #579

MichaIng opened this issue Jan 24, 2022 · 7 comments
Assignees

Comments

@MichaIng
Copy link
Contributor

MySQLTuner generally recommends to enable performance_schema for "better diagnostics". However, while it shows PF memory usage, it uses it only to check for unused indexes when table metrics are enabled, right? Probably the recommendation could somehow indicate this, as without enabling table metrics or manually analysing performance_schema, enabling it is useless and only consumes a bit of memory.

jmrenouard added a commit that referenced this issue Jan 30, 2022
@jmrenouard jmrenouard self-assigned this Jan 30, 2022
@jmrenouard
Copy link
Collaborator

HI @MichaIng

Can you give us your feedback ?

@MichaIng
Copy link
Contributor Author

There has been something confused in the code now, please see my comment and suggestion: b164a9b#r65203351

But the general question has not been addressed, i.e. does it really help that much for MySQLTuner diagnostics and does not really have no performance downsides with new MySQL/MariaDB versions? The recommendation could be more precise and say that it is required for table metrics only, and of course when manually used, but not sure whether it is correct to advise enabling PFS generally. It is disabled by default (when not overwritten by distribution package, but not done on Debian and Ubuntu at least), which may have some reason, even on newer MySQL/MariaDB versions.

@jmrenouard jmrenouard reopened this Jan 30, 2022
jmrenouard added a commit that referenced this issue Feb 7, 2022
Recommendation to turn on performance_schema #579
@jmrenouard
Copy link
Collaborator

Hi @MichaIng
Some config elements on my server, PFS impact performance for sure but ...

You should activate by PFS using performance_schema=ON
because:

  • You can't activate it without restarting your server
  • AND you can configure all you consumers to OFF by default and activate it when you think it is necessary

Some readings;

Best regards,
Jean-Marie

@MichaIng
Copy link
Contributor Author

MichaIng commented Feb 7, 2022

Restarting the database server is not a big issue in many cases. Of course it enables additional monitoring abilities, but the question is whether admins actively use them or not. If someone is actively using them, then (s)he will be surely aware that for this is needs to be enabled, hence MySQLTuner does not need to give that advice for those. For anyone who is not actively using PFS, the recommendation is then somehow wrong, since it remains unused but consumes resources.

Another idea would be to show the recommendation only when --idxstat is set, where MySQLTuner itself reads PFS to detected unused indexes (?).

But most importantly, the current doubled + contradicting checks and info needs to be merged/solved, as of the code comment I linked above. I can open a PR soon.
EDIT: Ah, sorry, I see you did that already, indeed works now 👍.

@MichaIng
Copy link
Contributor Author

MichaIng commented Feb 7, 2022

Hmm, the PFS check is still a mess. There is a dummy if-then-else block now. And MySQL <=5.5 + MariaDB == 10.0 get a warning now regardless whether it is on or off:

  • When it is OFF, all systems get a recommendation to enable it.
  • When it is ON, MySQL <=5.5 + MariaDB == 10.0 get a recommendation to disable it.

I can only recommend to check my comment with a suggestion how to structure the block: b164a9b#r65203351

@jmrenouard
Copy link
Collaborator

New fix with all your recommandations

@MichaIng
Copy link
Contributor Author

Many thanks. I see you removed the differentiation for old MySQL and MariaDB versions, which I think is fine 👍.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants