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
- 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 (
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
- 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: