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

This article will explain in detail how mysql5.7 can quickly locate the bottleneck of IO. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Background

Environment

1. MySQL5.7+ lower version MySQL is no longer considered here, just like there are companies using SAS disk, which is time-consuming and laborious, and MySQL5.7+ comes standard with 2. InnoDB storage engine 3. Centos 6

With regard to IO, what monitoring tools can you think of?

Iostat

Dstat

Iotop

Yes, all of the above are artifacts. You can directly use iotop to find the processes that take up the most resources.

Take a picture first.

Yes, according to this picture, what you can find is that one of the iothreads in MySQL takes up more disk resources, and then what?

Then, look for it in MySQL. Experienced DBA will look at slow log, or look for relevant sql statements in processlist.

Usually, DBA will only look blankly at a pile of MySQL query statements, a pile of slow log to analyze, like looking for a needle in a haystack, locating the problem is tedious and inefficient

If you are using the MySQL5.7+ version, then you will have an artifact (said many times) that can quickly and accurately locate problems

How to quickly locate where the bottleneck of IO is consumed

Iotop + threads

Dba:lc > select * from performance_schema.threads where thread_os_id=37012\ gateway * 1. Row * * THREAD_ID: 96 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 15 PROCESSLIST_USER: dba PROCESSLIST_HOST: NULL PROCESSLIST_DB: sbtestPROCESSLIST_COMMAND: Query PROCESSLIST_TIME: 0 PROCESSLIST_STATE: query end PROCESSLIST_INFO: INSERT INTO sbtest1 (k C, pad) VALUES(25079106, '33858784348-81663287461-16031064329-06006952037-79426243027-69964324491-90950423034-40185804987-62166137368-06259615216', '47186118229-42754696460-81034599900-41836403072-66805611739'),(24907169, '77074724245-16833049423-38868029911-54850236074-63700733526-39699866447-52646750572-85552352492-59476301007-32196580154', '79013412600-99031855741-69698796712-65630963686-19653514942'),(24896311, '28403978193-66350947863-03931166713-97714847962-65299790981-39948912629-14070597101-63277652140-34421148430-61801121402', '05239379274-22840441238-37771744512-92347741972-52847679847'),(18489383, '89292717216-01584483614-67433536730-45584233994-29817613740-77179131661-10692787267-83942773303-14971155500-36206705010 miles,' 55201342831-85536327239-84383935287-06948377235-96437333726'), (24790463, '99362943588-41160434740-6278364419-16002643-04761662097-942788379-52564232648-19738707042-147932768-8976811391391791391391391391362764461-1900102483-1977668368') (2 PARENT_THREAD_ID: NULL ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 370121 row in set (0.00 sec) this is the end of the article on "how to quickly locate the bottleneck of IO by mysql5.7" Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

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.