0

It is well known that query cache was deprecated in MySQL 8, which was a great news as I prefer to benchmark my queries based on execution time.

Unfortunately query results sre still cached in other ways. As far as I know there are operating system's, hard drive's and maybe other types of caching.

I am using MySQL Community 8.0.35 running on Ubuntru 22.04.3. Edit: The engine used is MyISAM.

When a specific query is run for first time it typically takes about 3 seconds. Each run of the same query after that takes no more than 0.015 seconds. If I change the time period of the query, so that to make it look like "new" query then execution again takes 3 seconds. Each following execution is 0.015 seconds.

Is there some approach to force caching not to happen other than rebooting the system?

Ivaylo
  • 133
  • 5
  • 2
    The query cache was removed in MySQL 8.0.3. See: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_have_query_cache – Raptor Jan 22 '24 at 10:08
  • 1
    Any caching done outside of mysql cannot be controlled from within mysql. Serverfault sister-site of SO can help with operating system and hardware level configurations. In mysql the various table engines have caches to keep the most frequently used data and index pages in memory. These are not query caches, but will be used by mysql to speed up retrieving the data. For example for innodb see https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html However, the DBA sister site of SO is better suited for handling database configuration related questions. – Shadow Jan 22 '24 at 10:22
  • 1
    When a specific query is run for first time it typically takes about 3 seconds. Each run of the same query after that takes no more than 0.015 seconds. This is data and index caching effect. The most part of 3 sec. during the 1st run is used for data and indices loading from the disk to the memory, and all the next executions does not need in this. Also you need in query execution time - so you do not need in data caching time, and 15ms which you see for the next executions is real query execution time. – Akina Jan 22 '24 at 10:48
  • Efficient caching is part of the database's job, so tuning queries without taking caching into account is misleading; it's like tuning your race car while running cheap all-season tyres and low-grade petrol. – mustaccio Jan 24 '24 at 14:30

0 Answers0