Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to configure MySQL cache to improve cache hit rate". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to configure MySQL cache to improve cache hit rate".

When will the application system get the data from the cache?

When the database reads data from the server, it can get the data from the data file of the hard disk or read the data from the database cache. What the database administrator needs to know now is, under what circumstances does the system read data from the cache rather than from the data file on the hard disk?

Simply put, a data cache is a storage area in memory that stores the user's SQL text and related query results. In general, if the user uses the same SQL text in the next query, and the relevant records have not been updated since the last query, the database will directly use the contents in the cache. From this principle, you can see that if you want to use the data in the cache directly, at least the following conditions must be met.

One is that the SQL text used is the same. When the user uses the same SQL statement twice (assuming no other conditions are considered), the server reads the result from the cache without the need to parse and execute the SQL statement. It is important to note that the SQL text here must be exactly the same at once. If there are two queries before and after, different query conditions are used. For example, no Where conditional statement was entered in the first query. Later, it was found that there was too much data, and the Where condition was used to filter the results of the query. At this point, even if the final query result is the same, the system still gets the data from the data file, not from the data cache. As another example, the field names used after Select must also be the same. If a field name is different or the number of fields used in the previous and second queries is different, the system will assume that it is a different SQL statement and re-parse and query it.

Second, from the point of view of data cache, case is not sensitive. For example, there may only be a case difference in the field names used in the two queries. If you use size for the first time and lowercase for the second time, the system thinks it's still the same SQL statement. Or keyword case, and so on, which are insensitive.

The third is to satisfy that between the second query, the data record, including the table structure, has not been changed. If the label of the record changes, such as adding a field, and so on, all buffered data systems that use this table will be emptied automatically. It is important to note here that the change referred to here is a generalized change, including any data or result changes in the table. To take a simple example, the user needs to query the number of shipments in 2010 for the first query. After the query, a user inserted a shipment information for January 2011 into this table. Then there are users who need to check the shipping information for 2010. The SQL statement used is exactly the same as the first query. In this case, will the database system use the data in the cache? The answer is no. Because when an intermediate user inserts a record, the system automatically empties all cached records associated with that table. When the second query, there is no cache information for this table in the cache. At this point, you need to re-parse and query.

Fourth, we should pay attention to the impact of the default character set on the cache hit rate. In general, if the default character set is different between the client and the server, the system still considers it to be a different query even if the query statement is the same and the record and table structure between the two queries have not been changed. We need to pay special attention to this point, which is easy to ignore.

Second, suggestions to improve the cache hit rate.

As can be seen from the above condition analysis, there are more stringent conditions for using the data in the cache. In fact, these conditions are also reasonable. The main purpose is to ensure the consistency of data. After an in-depth understanding of these conditions, what the database administrator needs to consider now is, how to improve the hit rate of this cache? The author has the following suggestions.

First, when configuring, the client side and the server side should use the same character set. If the character set used by the client (or third-party tools) is different from that used on the server side, caching will not be used in any case. Especially in China, we need to use Chinese character set. It is particularly important to note that the default character set on the client side is the same as the default character set on the server side. Note that it is the same here, not compatible. Sometimes even if a different character set is used, it can still be displayed normally on the client. This is mainly because some character sets are different but compatible with each other. In cache management, it needs to be the same, but optical compatibility is not enough.

Second, on the client side, the query statement should be solidified. For example, there are financial personnel and purchasing personnel to query November shipping data from the system at the same time. Obviously, their job responsibilities are different, and the contents of the required fields are different. At this point, on the client side, you can allow the user to set the form format he or she needs. However, the author suggests that the SQL statements used in the background should be the same. Here the data will go through three channels: the background database, the client, and the user. In the author's consciousness, the interaction between the background database and the client uses the same SQL statement. Then, when the client interacts with the user, the data is displayed to the user according to the user-defined format (including the arrangement of fields before and after, excluding the difference of query condition statements). At this time, due to the use of the same SQL statement (but users have different requirements for the display format), the query efficiency of the application system can be improved.

The third is to improve the configuration of the cache in memory to improve the hit rate. Generally, when the server starts, the operating system will negotiate the size of the cache space with the database software. When the cache is not working enough, the oldest cache record in the cache is overwritten by the latest message. It can be seen that if you can increase the cache space, you can improve the hit rate. This is like target practice, if there are more targets, the probability of hitting will be much higher. However, the more concurrency users have, the less effective this setting will be.

Fourth, the hit rate of the cache can be improved through the partition table. In the above conditional analysis, you can see that as long as a record is inserted into the queried table, the system clears the cache record. Now take querying the shipping record as an example. The shipping record sheet is updated every day, and users often need to check the shipping records of the previous year at the beginning of the year. At this time, because the data in this table is updated every hour, then the information in the cache will be kept under the circumstances. At this point, the cache hit rate is obviously not very high. In view of this situation, the author suggests that the partition table can be used. If it can be set up through the system, the shipping records for 2010 can be stored separately in a shipping partition table. That is, a separate partition table is used for each year. At this point, the 2011 record will not affect the 2010 partitioned table. At this time, if users repeatedly query the shipping information in 2010, as long as they use the same SQL statement (without using different query conditions), they can enjoy the benefits brought by the cache mechanism and improve the query effect of the application system.

Third, the impact of multiple applications on the cache.

Typically, the cache of an MySQL database is automatically allocated based on the amount of memory on the server. If there is only one MySQL application on a server, it is for the best. However, in practical work, in order to reduce the cost of information investment, multiple information applications are often arranged on the same server. As other information applications also need to use the memory space as the cache, then the cache space in the MySQL database may become smaller. In this case, the database administrator needs to negotiate with the system engineer to manually set different cache space for different applications according to different performance requirements. In this way, the cache conflicts of different information applications on the same server can be avoided.

Thank you for reading, the above is the content of "how to configure MySQL cache to improve cache hit rate". After the study of this article, I believe you have a deeper understanding of how to configure MySQL cache to improve cache hit ratio, and the specific usage needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.