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

The main content of this article is to explain "how to judge whether your MySQL is read or written". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to judge whether your MySQL is read or written too much".

Clumsy method (inaccurate)

Check all the sql statements in your project and make a statistic to find out whether there are more select statements or more insert, update and delete statements.

But this is still not accurate, because even if the select statement is written too much, it does not mean that it is executed much, and there may be a large number of batch update operations in our project.

Correct method (absolutely accurate)

Through the show status command, you can view the number of additions, deletions, modifications and queries in the database.

SHOW GLOBAL STATUS WHERE variable_name LIKE'% Com_insert%' OR variable_name LIKE'% Com_update%' OR variable_name LIKE'% Com_delete%' OR variable_name LIKE'% Com_select%'

Query the number of additions, deletions, changes and queries in the database

As shown in the figure above, it is explained as follows:

Com_delete represents the number of deletions Com_delete_multi represents the number of batch deletions Com_insert represents the number of inserts Com_insert_select represents the number of select into Com_select represents the number of select Com_update represents the number of update operations Com_update_multi represents the read-write ratio formula of batch update times

Read-write ratio = number of reads / writes

Read-write ratio =

Com_select/ (Com_select+Com_delete+Com_delete_multi+Com_insert+Com_insert_select+Com_update+Com_update_multi) * 100%) Note

If you use the SHOW STATUS or SHOW SESSION STATUS command, it means only querying the state data of the current session. If you want to see the state of the entire database, be sure to add the GLOBAL parameter and use the SHOW GLOBAL STATUS command.

Expand knowledge

SHOW GLOBAL STATUS can view a lot of data (windows mysql version 5.7, there are as many as 354), covering all aspects, you can explore on your own, with a picture.

Partial show status command data display

At this point, I believe you have a deeper understanding of "how to judge whether your MySQL is read more or write more". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.