MySQL how to set cache after installation?


Is MySQL 5.7.19, as I understood, after installation it is set up. All settings default, including query_cache_type = 0. For me it was a surprise.

I rarely do a setup. Last time I did it about 5 years ago at least, on older versions in General. Then I remember there set up different cache parameters depending on the load profile. But I do not remember such that the cache has ever been disabled. Without him always (at least used to) all inhibited.

While mysqltuner says everything is OK:

[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_type.

What does that mean? Caching now no longer used? For the second hour looking for information on a subject, anything sensible I can not find.
March 19th 20 at 09:12
1 answer
March 19th 20 at 09:14
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_type.

Quite a definite line. Refused for quite objective reasons
Thank you, revered. Restrictions known. The article says that it is necessary to do indexes, optimize queries, put something like ProxySQL.

And now the question: if there are indexes, queries vylizyvanie than one month. Everything is already optimized well enough - what's next, only ProxySQL?

If I test include query_cache_type, DB will not be damaged from this? Ie, the only question is that the cache can work just not optimally and all or is there any danger for my data?

And what is "mutex contention"? - Bernita.Mitchell23 commented on March 19th 20 at 09:17
@Bernita.Mitchell23, I would be difficult to answer accurately, because long migrated from MySQL to MariaDB and PostgreSQL.
If You have MySQL 5.7.20 or older, the functionality still exists and you can activate it without fear, but when you upgrade to a newer cache will be disabled.
ProxySQL is not the fact that will help, quite specific thing.
mutex contention is a situation when multiple "processes/entities" are fighting over a mutex calling mutex locking and brakes - belle.Crooks commented on March 19th 20 at 09:20
@Bernita.Mitchell23, enable the cache and see if anything data will not, at the same time then see how much it is effective in your case. Only query_cashe_size and query_cache_limit adjust.
But it is necessary to look specifically in your case, if the database is not written a lot, enable the query cache is justified. The point is that as the table changes, the cache is reset for the table, while the select it again under construction, and all this consumes resources.
In General, in addition to the cache in the muscle many other settings that will be useful, the size of buffers, temporary tables, number of temporary tables, the connection cache and so on - Anastacio commented on March 19th 20 at 09:23
@belle.Crooks, MySQL 5.7.19.

@Anastacio, thank you, I will try. Overall experience cache settings in the previous versions, and understanding how it works. Just surprised that he is now disabled by default in 8.0 and is removed.

Thank you all, again data nothing happens, I will be testing. - Bernita.Mitchell23 commented on March 19th 20 at 09:26

Find more questions by tags CachingMySQL