0

We have MySQL (version 8.x) with InnoDB engine. We were doing weekly MySQL restart (for rotating log files, like slow-query, error log etc) earlier and our RAM usage was in under control. Sometime back we started using mysqladmin flush-logs without restarting MySQL process.

After this we are seeing the memory usage increases proportional to time. Does flush-logs remove's slow-query.log and host_name.err log files from RAM or keep them as cached?

We are keeping 4 old log files and only renaming (mv command) them in flush-log operation. Will moving them to another folder helps?

Improve this question
7
  • doing weekly MySQL restart that's the problem. That only slows things, as the database has to load all cached data all over again. Database servers do use all available memory to cache data so they don't have to read from slow disks.
    – Panagiotis Kanavos
    Commented Sep 23 at 13:16
  • If you want to limit the server's RAM usage (very, very big WHY?), do so through server settings. The server will decide what needs to be cached and evict infrequently used data. The real way to reduce memory usage is to ensure there are proper indexes and well written queries so the server doesn't have to load a lot of data in memory. A query with Name LIKE '%banana%' can't use indexes and has to load everything to find matches. A query with Name LIKE 'banana%' on the other hand can use indexes and only load matches. The server only caches the index data pages
    – Panagiotis Kanavos
    Commented Sep 23 at 13:19
  • Could it be because the data is increasing day by day, hence table sizes, index sizes are increasing and DBs tend to load as much data as possible in RAM.
    – Allen King
    Commented Sep 23 at 13:27
  • No, DB data is increase is insignificant in this case. When we restart MySQL server, RAM usage goes back to normal and then increases continuously and so on.
    – Kishor
    Commented Sep 23 at 15:39
  • What OS are you using?
    – Rick James
    Commented Sep 24 at 2:02

1 Answer 1

Reset to default
0

The log files are not cached by MySQL Server. They may be partially cached by the operating system, as any buffered I/O is cached. But this is flushed to disk promptly by the OS.

Some releases of MySQL 8.0.x have reported memory leaks, i.e. memory is allocated but not freed, and the process grows over time. Perhaps none of the queries or other operations are especially high consumers of RAM, but as time goes on they incrementally "waste" more and more RAM. Restarting the MySQL Server process resets all RAM, so this explains why you didn't notice it while you were doing weekly restarts.

Memory leaks are bugs. They shouldn't happen in a properly engineered product. MySQL Server has unfortunately had some of these bugs, and you can search https://bugs.mysql.com for bugs reporting memory leaks that affect the version of MySQL Server you use. You should go research that bugs database.

If you find a bug that has been fixed in a subsequent version of MySQL Server, then you may want to upgrade to get the fix.

If you find a bug that has not yet been fixed, you can click the "Affects Me" button on that bug screen and add your voice to help increase the priority of fixing the bug.

If you are a customer paying for a support contract with Oracle, you should talk to your support representative and ask if or when the bug will be fixed.

In the meantime, memory leaks are difficult to manage. You may have to return to your practice of restarting the MySQL Server when you see the RAM usage increasing.

You could also try to debug the RAM usage of MySQL Server yourself, and develop a fix for the code. MySQL is open source, so you can patch the code yourself, or even contribute your patch back to the project. But this is more work than most users of MySQL want to put in.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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