0

I have question about MySQL caching.

We used mysqltuner.pl in past and enabled caching parameters and it decreased resource usage specially CPU usage.

But our main disaster was when server restart suddenly! After restart, MySQL was crashed and there were some InnoDB errors (and it offered recovery), I think because some data were located in memory and was not written into tables or log files.

Now I'm thinking about caching again because of MySQL huge resource usage. Please let me know about your experiences.

Does caching affect MySQL crashing?

mforsetti
  • 2,666
  • 2
  • 16
  • 20
bahram
  • 1
  • If you have "slow" queries, let's focus on the indexes and their query formulation. If you are running out of RAM, let's look at the configuration. MySQL does some caching internally; you are unlikely to get much benefit from adding another layer of caching. – Rick James Feb 16 '21 at 23:04

1 Answers1

2

Left to their own devices, Databases do not consume vast amounts of CPU and memory.
However, they will do so, quite happily, if asked to do so on behalf of poorly-written or poorly-performing queries.

I suspect what's happened here is that you turned on cacheing, which requires additional memory for the mysql instance which, in turn, woke the "dragon" that is the machine's Out of Memory Manager and that killed your mysql instance stone dead!

Look first to fixing the poorly performing queries, not at the database or the server on which they're running. Get the Execution (Explain) Plan for those queries and tune them. I often find that it takes just a handful of errant queries to cause a huge impact on the database.

Here's something I [all too] regularly horrify Developers with:

  • A DBA spending several days mucking about with database and server settings might possibly get you ...
    Two or three percentage points improvement in performance.
  • A Developer spending several days tuning a handful of errant queries would probably get you ...
    Two or three Orders of Magnitude improvement in performance.
Phill W.
  • 1,479
  • 7
  • 7