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?
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
- The slow queries in the applications that lead of I/O waits, are identified from the slow-query-log and optimized.
- Queries that use the wrong index are identifed by monitoring their execution time and the inefficient queries are corrected.
- Optimize the queries and sub-queries to reduce complex join, insert, update and search operations.
5. Hardware upgrade or migrations
- Verify and correct any problems with the RAID array
- Moving to a dedicated server or a cluster with better disk performance (replication solutions)
- Upgrading the harddisk to use multi-disk RAID arrays
- Splitting the application among different nodes (sharding)
- Installing disks with higher RPMs or SSD with high IOPS
- Upgrading the RAM in the server
- 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.
0 Comments