0

How can we check if the MySQL server is utilizing high memory/RAM ?

How much memory is occupied in total by query cache ? How much memory is fragmented ?

Basically what is occupying xGB off memory by MySQL server ?

Currently running MySQL Server version 5.7.25.

1
  • Please post TEXT results of SHOW GLOBAL STATUS LIKE 'QC%'; and SHOW GLOBAL VARIABLES LIKE 'query_c%'; Jul 28, 2020 at 21:28

1 Answer 1

0

It uses the amount specified by query_cache_size.

If that value is more than about 50M, then it is probably slowing down the server.

The Query cache is going away; it cannot be used in any Clustering setup, and it has been removed from MySQL 8.0.

Every modification to a table triggers a purge of all entries for that table. Therefore the QC, especially a big one, is costly in busy production servers.

If you must use the QC, follow these tips:

  • No more than 50M (except on Aurora)
  • Use SQL_CACHE or SQL_NO_CACHE in each SELECT, based on whether the query is likely to be repeated before it is purged.

(A small percentage of production system do benefit from the QC.)

There are cryptic metrics in SHOW GLOBAL STATUS LIKE 'Qc%'; but I can't explain them.

5
  • query_cache_size=1M for me. But still as query_cache_type=0 (off), non of the queries were getting cached. But, i suspect some kind of memory fragmentation happened around the QC. After FLUSHING QC, memory consumption came down by 10GB. But now, it has starting rising again. So, is there any way of checking the fragmented memory or MySQL memory footprint ? Jul 28, 2020 at 16:02
  • @PrateekPande - Is the OS using "huge pages"? If not, RAM is broken up into 4KB pages; no fragmentation possible.
    – Rick James
    Jul 28, 2020 at 20:48
  • If query_cache_type = 0, you may as well set query_cache_size = 0.
    – Rick James
    Jul 28, 2020 at 20:48
  • See OS routines top and innotop.
    – Rick James
    Jul 28, 2020 at 20:49
  • query_cache_type = 0, you may as well set query_cache_size = 0 this was already done. Jul 29, 2020 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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