I/O process limit on server is getting overloaded
Recently i am facing issues with my server.
Requests are getting delayed or bounced back and possible or main cause is that (which is being shared by Server Support team) is that I/O limits are exceeding. (40K to 50K I/O operation a minute)
Possible ways to resolve this
- To optimize the application.
- Increase Server Resources.
I am trying both methods, have increased server resources, and working on optimizing the application.
Meanwhile have many questions and have tried to search for the answers on internet/books etc. but somehow didn’t find any reliable answer.
Have few queries.
- How are the I/O processes being calculated?
If i am calling 5 queries from application backend one by one does that count to total of 5 I/O operation?
If i am calling 2 queries but now i have combined 3 different queries into one query so how many I/O operation will be counted? but what i have noticed is due to this total response time was increased when compared to earlier one.
-
How should this type of situation to be tackled, as traffic will keep on increasing and sooner or later even after application is optimized I/O operation will catch up with time.
-
Looking for guidance who has worked on some sort of Social Media Architecture / Platform ON backend MySQL, server resource management.
Some of these question may seem too naive or silly to you, but since i have little experience in this i am trying to learn and trying to solve by whatever means i can.
EDIT Below is the I/O log shared by server Support team I have checked your server and currently the I/O details for your container are:
[code2020-07-15 11:09 | CPU: 78.42 | IOPs-Read: 57899 | IOPs-Write: 3835 2020-07-15 11:08 | CPU: 64.63 | IOPs-Read: 57880 | IOPs-Write: 3855 2020-07-15 11:07 | CPU: 76.40 | IOPs-Read: 33240 | IOPs-Write: 4575 2020-07-15 11:06 | CPU: 93.91 | IOPs-Read: 61386 | IOPs-Write: 4463 2020-07-15 11:05 | CPU: 105.35 | IOPs-Read: 50142 | IOPs-Write: 5056 2020-07-15 11:04 | CPU: 93.25 | IOPs-Read: 55701 | IOPs-Write: 4580 2020-07-15 11:03 | CPU: 98.06 | IOPs-Read: 68514 | IOPs-Write: 4789 2020-07-15 11:02 | CPU: 123.39 | IOPs-Read: 52368 | IOPs-Write: 6383 2020-07-15 11:01 | CPU: 93.82 | IOPs-Read: 46519 | IOPs-Write: 6119 2020-07-15 11:00 | CPU: 97.86 | IOPs-Read: 69654 | IOPs-Write: 6058 2020-07-15 10:59 | CPU: 106.11 | IOPs-Read: 72819 | IOPs-Write: 6142 2020-07-15 10:58 | CPU: 68.74 | IOPs-Read: 35668 | IOPs-Write: 4630 2020-07-15 10:57 | CPU: 81.57 | IOPs-Read: 41877 | IOPs-Write: 4924 2020-07-15 10:56 | CPU: 85.56 | IOPs-Read: 59275 | IOPs-Write: 4879 2020-07-15 10:55 | CPU: 78.71 | IOPs-Read: 38883 | IOPs-Write: 4176 2020-07-15 10:54 | CPU: 53.99 | IOPs-Read: 41164 | IOPs-Write: 3068 2020-07-15 10:53 | CPU: 81.39 | IOPs-Read: 51939 | IOPs-Write: 5301[/code] 
(to long for comment)
If i am calling 5 queries from application backend one by one does that count to total of 5 I/O operation?
If i am calling 2 queries but now i have combined 3 different queries into one query so how many I/O operation will be counted? but what i have noticed is due to this total response time was increased when compared to earlier one.
You have the wrong model for how queries use I/O.
If you are selecting a single row, and you have the unique identifier for that row, it might take 1 I/O, or possibly 0.
0 IOPs — The data is already "cached" in ram. So there is some effort in getting the desired data, but it did not involve any I/O.
1 IOPs — Let’s say you have a huge table and you have not yet touched a particular area of the table (whatever all that means), then it might have to reach into disk to find the desired "row". After that, it and the nearby rows, would be "cached", thereby possibly making some future queries run without any further I/O.
Lots of I/O — Let’s say you have a huge table and you have a query that needs to look at all the rows of the table. And the table is not cached (or maybe only some of the table is cached). Now you will have lots of I/O operations.
Those are examples of "read" IOPs. There are also "writes" to consider. But first, find the slow queries so we can focus on them.
The slowlog is an excellent way to identify the slowest queries. More: mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
