How to fix MySQL high IOWait
MySQL is the most popular database used in web applications. It is supported by all hosting providers, is easy to administer, and free. However, MySQL servers often face high server load due to high disk IOWait.
Is it really high IOWait?
MySQL load can be due to high CPU usage, high memory usage or I/O usage. Before thinking about solutions, it is important to make sure the load is indeed caused by high I/O. Here’s how you can do it.
1. Check “%wa” in “top”
Take a look at the ‘top’ command output for server load. If the CPU and memory usage is less, even though the load is high, then disk I/O would be the culprit.
Check the I/O wait percentage (%wa component) and see if its higher than 1/n, where n is the no. of CPU cores. If so, then the CPU is waiting for a long time for the disk I/O to be completed.
2. Check “iowait%” in “iostat”
Take a look at the results of ‘iostat’ command to see if the ‘iowait%’ is high. This is the percentage of time that the CPU had to wait for a disk I/O request to complete.
Also check the partition where the I/O wait is high, and confirm that it is in the partition where the databases are stored. If the I/O wait % is more than 50% in a dual-core CPU, it is considered as high I/O wait.
What causes MySQL high IOWait
There’s no single reason for I/O bottle necks. It can range from hardware issues to unoptimized server configurations. Here are the top issues.
1. Slow disk or degraded RAID array
Since disk access takes more time and is slower than accessing the memory, write and read operations slows up the MySQL performance., especially if the disk is slow. I/O wait can increase if the disk is degraded or corrupted.
2. Low system memory
As MySQL perform its transactions in the RAM, fetching the data each time between the RAM and the disk. If the RAM size is less, this can lead to many I/O wait cycles when dealing with large databases.
3. High number of transactions and size of data
The I/O overhead can further increase depending on the size of the database and number of transactions. Moreover, a lot of time is wasted especially during insert operations in large data sets, as write operations are slower than reads.
4. Unoptimized MySQL configuration
No write cache to store frequently accessed data sets, improper flushing mechanism, inadequate temporary table size for doing database operations, etc. are some issues that can lead to high I/O.
5. Complex queries
When the code in the application involves operations such as complex joins and quering over a large range in large data sets, the memory usage increases and so does the I/O overhead involved.
[ Don’t lose your customers to slow server. We’ll keep your MySQL server optimized for best performance. Our emergency server admin team is available 24/7 with instant solutions for all your server issues.]
Bobcares provides Outsourced Web Hosting Support and Outsourced Server Management for online businesses. Our services include 24/7 server support, help desk support, live chat support and phone support.