-3

I'm curious about which common practices or actions contribute to spikes or high CPU usage and RAM usage for SQL databases, I am trying to estimate the resources I would allocate to a new MySQL database (vertical upgrade), based on the current DB resource usage and how much this use is estimated to increase in the future.

For example: - "Highly complex join and search queries can increase CPU utilization, because..."

closed as too broad by John Eisbrener, mustaccio, jynus, Marcello Miorelli, McNets Jul 23 at 14:59

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

I have looked into perhaps more than a thousand cases of "high CPU" (or "high load average"). I would suggest that 97% of the cases can be addressed by

  • Adding a suitable composite index, or
  • Reformulating the query, such as not hiding an indexed column in a function call.

As for RAM usage, setting innodb_buffer_pool_size suitably is about the only thing that is worth changing. Perhaps in only 20% of cases will tweaking some other setting will help more than a trivial amount.

Some other patterns that are "bad" for performance:

  • Entity-Attribute-Value (Scales poorly due to heavy use of JOINs)
  • UUIDs for identifying items (Scales poorly due to randomness; in some situations, adding lots of RAM is the only remedy)
  • Poor schema for "many-to-many" mapping table
  • Explode-implode (JOIN, then GROUP BY id of one table)
  • "But I indexed every column." (and other things that novices say about indexing)
  • Thinking that PARITIONs automatically provide performance (There are only 4 use cases for PARTITIONing)
  • Pagination using OFFSET (Instead, "remember where you left off")

I discuss those and more at http://mysql.rjweb.org/

CPU spikes usually occur due to uneven actions by the users, but can often be alleviated by speeding up the queries involved. Or even eliminating some of the queries. -- This involves looking at the data flow from a higher level.

If you have a running system that will grow, then I recommend doing the two things in http://mysql.rjweb.org/doc.php/mysql_analysis :

  • Analysis of GLOBAL STATUS and VARIABLES -- This might find a few things to tweak.
  • Slowlog -- This is good at finding the 'worst' queries and alleviating bottlenecks.

Also, the information discovered from those analyses may lead to understanding how soon you will need to grow the system, and in what direction: CPU (unlikely), I/O, read scaling, write scaling, etc:

Not the answer you're looking for? Browse other questions tagged or ask your own question.