Bobcares

How to fix MySQL high IOWait

by | Mar 30, 2016

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.

MySQL pulling down your server?

CLICK HERE TO RESCUE YOUR SERVER NOW!

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.]

Fixing MySQL high IOWait

Depending on what exactly caused the IOWait, here are the ways in which high IOWait can be resolved.

1. Move MyISAM to InnoDB

InnoDB uses an adaptive flushing algorithm to perform write operations in an efficient manner. InnoDB logs the transactions instead of flushing the pool to disk everytime. This background flushing helps to complete the queries faster and thus reduce the I/O spikes.

2. Perform write transactions mostly during off-peak hours

Scheduled write transactions can be performed during off-peak hours, such as stats compilation, report generation, migrations and copies.

3. Tuning the MySQL parameters

1. innodb-flush-log-at-trx-commit – this parameter can be used to control the frequency of flushing – how often the server writes the transactions to the disk. Default value of 1 writes the log buffer to the log file and flush the data from memory to disk, after every transaction. This parameter can be configured with various options to control the flushing frequency to reduce the I/O.

2. tmp_table_size – To perform complex join queries, MySQL creates a temporary table. If the table size is small, the operations are performed in the disk, which increases I/O overhead. To avoid that, this parameter is kept at a higher value, especially for large databases. This allots memory to perform such operations and thus reduce I/O. The calculation for this value = RAM size / max no of connections.

3. Enable caching – To minimize the I/O, caching can be done in buffer pool to keep frequently accessed data and indexes in the memory. Making the buffer pool larger can improve performance by reducing the amount of disk I/O needed during transactions.

To control the size of the key cache, MyISAM has the key_buffer_size system variable. The innodb_buffer_pool_size system variable specifies the size of the InnoDB buffer pool.

4. Improved flushing – Increase the innodb_io_capacity configuration option to perform more frequent flushing, which will avoid the drops in throughput. But the value should not be too high that too much flushing would slow down the performance. So an optimal value is set.

5. innodb_log_buffer_size – A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O.

[ You don’t have to be a MySQL expert to keep your sites fast and stable. Don’t let MySQL ruin your server stability. Click now for an immediate solution from our server experts. ]

4. Optimizing the application code

  1. The slow queries in the applications that lead of I/O waits, are identified from the slow-query-log and optimized.
  2. Queries that use the wrong index are identifed by monitoring their execution time and the inefficient queries are corrected.
  3. Optimize the queries and sub-queries to reduce complex join, insert, update and search operations.

5. Hardware upgrade or migrations

  1. Verify and correct any problems with the RAID array
  2. Moving to a dedicated server or a cluster with better disk performance (replication solutions)
  3. Upgrading the harddisk to use multi-disk RAID arrays
  4. Splitting the application among different nodes (sharding)
  5. Installing disks with higher RPMs or SSD with high IOPS
  6. Upgrading the RAM in the server
  7. Vary the RAID level according to the criticality of the data. For example, store semi-important data that can be regenerated on a  RAID 0 disk, but store really important data such as host information and logs on a RAID 0+1 or RAID N disk. RAID N can be a problem if you have many writes, due to the time required to update the parity bits.

In short

MySQL high IOWait can happen due to anything from a hardware issue to unoptimized server configuration. Here we’ve covered what causes MySQL high IOWait, how to detect it, and the various ways to fix it.

Bobcares helps business websites of all sizes achieve world-class performance and uptime, using tried and tested website architectures. If you’d like to know how to make your website more reliable, we’d be happy to talk to you.

 

 

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Sign Up once. Enjoy Peace Of Mind For Ever!

PROTECT YOUR SERVER NOW.

var google_conversion_label = "owonCMyG5nEQ0aD71QM"; 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.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

_reb2bgeo - The visitor's geographical location

_reb2bloaded - Whether or not the script loaded for the visitor

_reb2bref - The referring URL for the visit

_reb2bsessionID - The visitor's RB2B session ID

_reb2buid - The visitor's RB2B user ID

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid
_reb2bgeo, _reb2bloaded, _reb2bref, _reb2bsessionID, _reb2buid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF