After moving to MySQL with MariaDB 5.7.12 5.5.46 stopped caching the queries. What could be wrong?

Hello. After moving to a new server at the same time decided to change the database server. But I noticed that the site has become slower, although the new server should be more productive. Have got to understand - found that the queries that were previously kasioulis, caching stopped. The query cache if this is enabled, the command SHOW GLOBAL STATUS LIKE 'Qcache%' gives adequate results. Prompt, please, who faced what could be wrong?
July 12th 19 at 17:24
1 answer
July 12th 19 at 17:26
Qcache "warmed up" for some work time of service.
Maybe is not enough time between the restarts?
Maybe the users have not been able to trigger queries to the cache?

After how long of uptime previously accumulated in the cache, and how much time of starting the service you see it now?

Another option:
Default query_cache_size = 0.
You need to specify the memory available to him, so he started to work.

https://mariadb.com/kb/en/mariadb/query-cache/#set...
It is off by default due to limitations in some environments: https://mariadb.com/kb/en/mariadb/query-cache/#lim...

Another option:
Maybe on the new server is much more slow disks?
You can enable slow query log and see what bogged down the app or try on a new disk IOPS. =) Or see iowait storage for the database using the tool "ioping".
Conducted the following test: took a few queries, each chased a few times on the old and new server. The old results were as follows: first run - for example 3, subsequent to the 0.06 s. On the new all runs stable in 1.5 s. Checked after five days of uptime, and after the immediate restart of both servers.

Each time it turned out that the same query when you first run new worked somewhat faster than the old, but all of the following - old went to multiple the lead. - Fernando.Farrell37 commented on July 12th 19 at 17:29
: Updated the post with a note about query_cache_size. =) Maybe it'll help? - Wellington.Kut commented on July 12th 19 at 17:32
: Thank you, again rechecked the settings, but alas it is now all configured. - Fernando.Farrell37 commented on July 12th 19 at 17:35
: Hmmm. And will show all the indicators associated with Qcache? Let's look together. =) - Wellington.Kut commented on July 12th 19 at 17:38
: Updated the post a couple of ideas. =) - Wellington.Kut commented on July 12th 19 at 17:41
: that's what gives Qcache
Qcache_free_blocks 9987
Qcache_free_memory 79782040
Qcache_hits 1495035
Qcache_inserts 4182506
Qcache_lowmem_prunes 940447
Qcache_not_cached 406102
Qcache_queries_in_cache 40815
Qcache_total_blocks 91967

About discs - this was the main reason for the move, moved from the HDD to the SSD, I expect will only get better in some aspects, yet it turns out not quite so) Idea is only such that for some settings, some queries have ceased to satisfy the conditions of a cache hit, but is decidedly not clear. - Fernando.Farrell37 commented on July 12th 19 at 17:44
: Hmmm! A lot Qcache_lowmem_prunes.
Is the amount of memory allocated for qcache, between the first and second server? - Wellington.Kut commented on July 12th 19 at 17:47
: And another question - after moving to a new server whether the changed logic of the application itself? Was it, for example, UPDATE-ing data than before? - Wellington.Kut commented on July 12th 19 at 17:50
The difference is twofold: on the new query_cache_size 128M, 256M on the old. But before that, I tried to change, didn't help. Fundamental changes were made, the problems began immediately after moving about 2 weeks ago. Tried even the sample one on the new table, the cache is triggered periodically. - Fernando.Farrell37 commented on July 12th 19 at 17:53
Since nothing has changed? The problem is not solved? - Wellington.Kut commented on July 12th 19 at 17:56
: Sorry, I went through the old letters, just now saw the question. More or less now the problem is not so actual, presumably accumulated over time, the cache. Some queries are still not cached, but not so critical to serious the influence. Ie mainly, apparently, still the problem was not set the cache on the new server. - Fernando.Farrell37 commented on July 12th 19 at 17:59
: Great. =) - Wellington.Kut commented on July 12th 19 at 18:02
: Will show examples of the queries that are not covered in the cache? If you have at hand. - Wellington.Kut commented on July 12th 19 at 18:05

Find more questions by tags CachingMariaDBMySQL