3

I'm trying to optimise the Digital Ocean droplet that my Laravel web app is running on, and have noticed that MySQL is constantly using ~50% of its 1GB RAM. By far the most common and well-attested method for decreasing MySQL's memory footprint is to disable its Performance Schema feature by setting performance_schema = 0 in /etc/mysql/my.cnf.

However, no answer I've seen yet makes any mention of what exactly this feature does, why it's enabled by default, and the implications of disabling it. To me it seems too be good to be true, and while I'm all for optimisation, I also don't want to compromise the integrity of my web app's server.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Hashim Aziz
  • 4,074
  • 5
  • 38
  • 68
  • 2
    Its documented https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html – RiggsFolly Feb 03 '23 at 14:29
  • @RiggsFolly I did come across that link after digging deeper into the documentation (Google didn't bring it up), but while it answers what PerformanceSchema does, it doesn't answer the more important parts of the question; why it's enabled by default and what disabling it might do. – Hashim Aziz Feb 03 '23 at 14:38
  • I have never run with it on, so I would guess it can happily be turned off if you want. Oh and on again if you need to do any troubleshooting – RiggsFolly Feb 03 '23 at 14:41
  • 1
    Oh and you really must have been asking [the wrong question of google](https://www.google.com/search?client=firefox-b-d&q=What+does+MySQL%27s+performance_schema+do) – RiggsFolly Feb 03 '23 at 14:43
  • @RiggsFolly Did your detective's eye also notice that not a single one of those links goes to the link you provided in your first comment, the one that actually explains what PerformanceSchema is? Hence the digging through the documentation. Case in point, even this question, asked 30 mins ago, now ranks higher on Google than the documentation link you provided. – Hashim Aziz Feb 03 '23 at 14:54
  • Then someone is blocking information flow to your location. Sensorship is a real pain – RiggsFolly Feb 03 '23 at 14:56
  • @RiggsFolly We're in the same location, and I can only hope that was an attempt at irony and not real paranoia. – Hashim Aziz Feb 03 '23 at 14:57
  • 1
    I have noticed the SEO of MySQL.com changed over the past year or two, and Google does not provide links to the MySQL manual in the first page of search results. I have found that if I include the MySQL version number (e.g. "8.0") as a search term, it links to the MySQL manual more reliably. The link to the PS is the first result in this search: https://www.google.com/search?q=what+is+the+performance+schema+mysql+8.0 – Bill Karwin Feb 03 '23 at 15:01
  • I prefer the slowlog (with a low value for `long_query_time`) for finding the queries that impact performance the most. – Rick James Feb 03 '23 at 23:49
  • A 1G server today is admirable. Consider springing for a 4G version and you will be able to do more amazing things and not break up your bank account. – Wilson Hauck Feb 06 '23 at 00:32
  • @WilsonHauck That makes a big assumption about my bank account. – Hashim Aziz Feb 06 '23 at 00:39
  • Consider looking for FREE hosting services. They are available in many locations. Bank account will not be an issue. – Wilson Hauck Feb 06 '23 at 00:41

2 Answers2

4

The performance_schema is for monitoring and instrumenting the MySQL Server. Many types of monitoring tools may depend on it. I won't describe the specific events it monitors, because that's in the manual.

You can run MySQL Server without the performance_schema enabled, but monitoring will be compromised. If you disable monitoring, you will not be able to diagnose performance problems or resource usage.

The IT industry is becoming increasingly aware that monitoring is an important feature of servers and infrastructure. I don't think it's a good tradeoff to disable the performance_schema in MySQL Server to gain a mere 512MB of memory. If you are that constrained on memory, then you should reconsider if MySQL Server is the right technology choice for your platform.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • There are many reasons why someone may want to run mysql on a server with limited resources, and not have either the ability or the desire to switch the database technology used. Personal use and large scale enterprise situations are often completely different. – Frug Mar 05 '23 at 04:09
3

Disabling performance schema is perfectly fine and will save you some of that memory.

No, it's not too good to be true. To say that you're compromising something is misleading. It's left on by default because it can be useful and in most cases people can spare the memory, but like any tool you only need it if you're going to use it. If you're not going to use it, you're absolutely free to turn it off with no negative impact at all.

A small droplet that you run for a personal project is exactly the kind of use case where you would want to turn it off.

Plus, you can always turn it on if you decide you need it (with the caveat that you wouldn't have historical data).

Frug
  • 343
  • 3
  • 10