MySQL Shell Basic Configuration Management (Part 3 – Disk I/O and Storage Optimization)

This is part of a series of posts from Chisom Kanu about using MySQL Shell in the management of MySQL. You can see the entire series here.

So far, in our previous articles on MySQL optimization, we’ve looked at basic configuration management and memory and CPU tuning using MySQL Shell. In the first article, we looked into how to optimize your MySQL database’s configuration with MySQL Shell. In the second article, we focused on memory and CPU usage, showing how to monitor and improve performance by adjusting important settings. In this third installment in the series, we’ll continue our series by going into another important aspect of database optimization—disk I/O and storage.

Disk I/O plays an important role in how fast MySQL can read and write data. Even if your memory and CPU are optimized, poor disk I/O performance can cause issues in the entire system. This is especially important for write-heavy databases or those handling large datasets, as the speed of disk operations can dramatically affect overall performance. InnoDB, the default storage engine in MySQL, is highly reliant on efficient disk I/O. By tweaking its settings, you can reduce latency and improve throughput. We will cover the essential MySQL Shell commands and scripts to monitor, configure, and optimize your disk I/O and storage for maximum performance.

We begin by accessing MySQL Shell. If you’ve been following along in this series, you’re likely familiar with how to do this. But just to make sure we’re on the same page, here’s how to get started. Open your terminal and type:

1	mysqlsh --uri root@localhost

If you need to enter a password, you’ll be prompted.. If you’re unsure about connection details, please refer back to Part 1 of this series.

Note that this will connect with the very powerful root user, so be careful doing this on a production server if you are not sure what you are doing.

After connecting, switch to JavaScript mode (since we’ll be using JavaScript commands in MySQL Shell) by typing \js. Now, we’re ready to work with disk I/O settings.

Why We’re Using MySQL Shell

Now, you might be wondering, “Can’t we just run SQL commands to do most of what is in this article?” And you’re right, in many cases we could. But MySQL Shell offers several advantages that make our optimization journey smoother:

Reusability: We can create functions that package complex operations into easy-to-use commands.
Scripting: We can combine multiple operations and add logic, making our optimizations smarter.
Consistency: By saving our functions, we ensure we’re always running optimizations the same way.
Automation: We can easily schedule and run complex monitoring and optimization tasks.

It allows us to build tools around basic SQL commands, making our optimization process more efficient and less error prone.

Configuring InnoDB I/O Settings with MySQL Shell

The most important settings for controlling disk I/O in MySQL’s InnoDB engine are innodb_io_capacity and innodb_io_capacity_max. These parameters dictate the maximum number of I/O operations InnoDB will perform per second. Configuring these appropriately for your hardware is crucial. For instance, if you’re using fast SSD storage, you can set a higher value, allowing InnoDB to perform more operations per second.

Let’s first check the current settings by running the following script in MySQL Shell:

var result =

session.runSql('SHOW VARIABLES LIKE "innodb_io_capacity"');

var row;

while (row = result.fetchOne()) {

print(row[0] + ": " + row[1]);

}

When you run this script, you’ll get the current configuration for both innodb_io_capacity and innodb_io_capacity_max. For example, you might see output like this:

This tells us that InnoDB is currently set to handle up to 10000 I/O operations per second and can go as high as 20000 if necessary. If you have faster hardware, like an SSD that can handle a higher I/O load, you’ll likely want to increase these values.

To adjust them, we can run the following commands:

session.runSql('SET GLOBAL innodb_io_capacity = 20000');

session.runSql('SET GLOBAL innodb_io_capacity_max = 40000');

print("InnoDB I/O capacity settings updated.");

After changes these settings higher, InnoDB will be able to handle more read and write operations per second, which can improve performance, especially for databases with high I/O demand.

Monitoring Disk I/O Performance

After adjusting the I/O capacity, we want to monitor whether the system is effectively using the available I/O resources. Let’s create a more comprehensive script to check InnoDB’s disk I/O performance:

function checkDiskIOPerformance() {

var metrics = [

"Innodb_data_reads",

"Innodb_data_writes",

"Innodb_data_fsyncs",

"Innodb_data_pending_reads",

"Innodb_data_pending_writes",

"Innodb_data_pending_fsyncs"

];

var values = {};

for (var i = 0; i < metrics.length; i++) {

var result = session.runSql('SHOW GLOBAL STATUS LIKE "' + metrics[i] + '"');

values[metrics[i]] = parseInt(result.fetchOne()[1]);

}

print("InnoDB Disk I/O Performance:");

print("Data reads: " + values.Innodb_data_reads);

print("Data writes: " + values.Innodb_data_writes);

print("Data fsyncs: " + values.Innodb_data_fsyncs);

print("Pending reads: " + values.Innodb_data_pending_reads);

print("Pending writes: " + values.Innodb_data_pending_writes);

print("Pending fsyncs: " + values.Innodb_data_pending_fsyncs);

var readRatio = values.Innodb_data_pending_reads / values.Innodb_data_reads;

var writeRatio = values.Innodb_data_pending_writes / values.Innodb_data_writes;

print("\nDisk Pressure Analysis:");

print("Read pressure: " + (readRatio * 100).toFixed(2) + "%");

print("Write pressure: " + (writeRatio * 100).toFixed(2) + "%");

if (readRatio > 0.1 || writeRatio > 0.1) {

print("\nWARNING: Your I/O subsystem may be under pressure.");

print("Consider increasing innodb_io_capacity or upgrading your storage.");

} else {

print("\nYour I/O subsystem appears to be handling the load well.");

}

};

module.exports.checkDiskIOPerformance = checkDiskIOPerformance;

Here, we’re collecting six key metrics related to InnoDB’s disk operations. The script calculates “pressure ratios” for both reads and writes; these ratios represent the proportion of pending operations to total operations. We interpret these ratios as percentages to give a clearer picture of disk pressure.

When you run the `checkDiskIOPerformance()` function, the first six lines show the raw numbers of operations performed and pending. The “pressure” percentages give you a quick way to gauge system performance:

If these percentages are below 10% (0.1), your system is likely handling I/O well.
If they exceed 10%, it might indicate that your storage is struggling to keep up with demand.
The script provides an interpretation based on these percentages.

Remember, these numbers have been accumulating since the server started. To get a more accurate picture of current performance, you should run this function at regular intervals (e.g., every hour). Compare the differences between runs to see how many operations occurred in that time period and also look for trends over time. Are the pressure percentages increasing?

By monitoring these metrics regularly, you can identify when your system is approaching its I/O limits, make informed decisions about when to increase `innodb_io_capacity` or upgrade your storage, also correlate I/O performance with other system changes or increased load.

Optimizing Data and Log File Storage with MySQL Shell

In addition to adjusting I/O capacity, it’s essential to look at how MySQL manages its data and log files. The size and location of these files can have a significant impact on performance.

To see the current location of the data file, use the following commands:

var result = session.runSql('SHOW VARIABLES LIKE "innodb_data_file_path"');

print("Data file path: " + result.fetchOne()[1]);

This will return something like:

This will tell you where MySQL is storing the InnoDB data file. If the file is located on a slow disk, moving it to faster storage, such as an SSD, can help.

We can also check the size of the log file, which plays an important role in how quickly MySQL can write changes to disk:

result = session.runSql('SHOW VARIABLES LIKE "innodb_log_file_size"');

print("Log file size: " + result.fetchOne()[1] + " bytes");

A larger log file can improve performance by reducing the frequency of flushes to disk, especially for write-heavy workloads. If your database has grown or you’ve increased your innodb_io_capacity, increasing the log file size might be a good next step.

Fine-Tuning InnoDB Log Files with MySQL Shell

The number of InnoDB log files can significantly impact performance, especially for databases with frequent write operations. Let’s create a function that not only checks the current configuration but also provides guidance on potential adjustments:

function analyzeInnoDBLogFiles() {

var logFilesResult = session.runSql('SHOW VARIABLES LIKE "innodb_log_files_in_group"');

var logFileSize = session.runSql('SHOW VARIABLES LIKE "innodb_log_file_size"');

var bufferPoolSize = session.runSql('SHOW VARIABLES LIKE "innodb_buffer_pool_size"');

var numLogFiles = parseInt(logFilesResult.fetchOne()[1]);

var logSize = parseInt(logFileSize.fetchOne()[1]);

var bufferSize = parseInt(bufferPoolSize.fetchOne()[1]);

print("Current InnoDB Log File Configuration:");

print("Number of log files: " + numLogFiles);

print("Size of each log file: " + (logSize / (1024 * 1024)).toFixed(2) + " MB");

print("Total log file size: " + (numLogFiles * logSize / (1024 * 1024)).toFixed(2) + " MB");

print("InnoDB buffer pool size: " + (bufferSize / (1024 * 1024)).toFixed(2) + " MB");

var totalLogSize = numLogFiles * logSize;

var recommendedLogSize = Math.min(bufferSize / 4, 4 * 1024 * 1024 * 1024); // 25% of buffer pool, max 4GB

print("\nAnalysis:");

if (totalLogSize < recommendedLogSize) {

print("Your total log file size might be too small. Consider increasing it.");

print("Recommended total log size: " + (recommendedLogSize / (1024 * 1024)).toFixed(2) + " MB");

print("You can achieve this by either:");

print("1. Increasing the number of log files, or");

print("2. Increasing the size of each log file");

} else if (totalLogSize > recommendedLogSize * 1.5) {

print("Your total log file size might be larger than necessary.");

print("Consider reducing it to save disk space and potentially improve performance.");

} else {

print("Your current log file configuration seems appropriate.");

}

print("\nTo make changes, add or modify these lines in your MySQL configuration file:");

print("innodb_log_files_in_group = " + numLogFiles);

print("innodb_log_file_size = " + logSize);

print("\nThen restart MySQL for changes to take effect.");

print("\nRemember: Changing log file size requires MySQL to rebuild the log files, which can take time.");

}

module.exports.analyzeInnoDBLogFiles = analyzeInnoDBLogFiles;

This function retrieves the current settings for the number of log files, size of each log file, and the InnoDB buffer pool size, it calculates the total log file size and compares it to a recommended size (25% of the buffer pool size, up to a maximum of 4GB) and based on this comparison, it provides specific recommendations.

When you run `analyzeInnoDBLogFiles()`, the first section shows your current configuration, giving you a clear picture of your log file setup. The analysis section provides a recommendation based on best practices: If your total log size is less than 25% of your buffer pool (up to 4GB), it suggests increasing it. If it’s significantly larger, it suggests you might be able to reduce it, and if it’s within a reasonable range, it confirms your configuration is appropriate. It provides the exact lines you’d need to add or modify in your MySQL configuration file to implement any changes. This approach is more flexible and informative:

It doesn’t assume a one-size-fits-all solution.
It bases recommendations on your current buffer pool size, which is a key factor in determining optimal log file size.
It explains the reasoning behind the recommendations, helping you make an informed decision.

Also, while this function provides guidance, the optimal configuration can vary based on your specific workload and hardware. Always test changes in a non-production environment first and monitor performance before and after any modifications.

Optimizing Tables for Better Disk Performance

In addition to tuning settings, periodically optimizing your tables can improve disk I/O performance by reducing fragmentation and reclaiming unused space. Here’s a script to optimize all tables in a given database:

function optimizeTables(database) {

var tables = session.runSql('SHOW TABLES IN ' + database).fetchAll();

print("Found " + tables.length + " tables in database '" + database + "'");

print("Starting optimization process...");

for (var i = 0; i < tables.length; i++) {

var tableName = tables[i][0];

print("Optimizing table " + (i + 1) + " of " + tables.length + ": " + database + "." + tableName);

var result = session.runSql('OPTIMIZE TABLE ' + database + '.' + tableName);

var status = result.fetchOne()[3]; // The status message

print("Status: " + status);

}

print("Table optimization complete for database '" + database + "'");

}

module.exports.optimizeTables = optimizeTables;

The function takes a `database` parameter, which is the name of the database you want to optimize.

It first retrieves a list of all tables in the specified database, for each table, it runs the `OPTIMIZE TABLE` statement and reports the status and it provides progress updates, showing which table is being optimized and how many are left.

To use this function, you would call it like this:

1	optimizeTables('your_database_name');

Replace 'your_database_name' with the actual name of your database. When you run this function, you’ll see the total number of tables in your database, the progress of the optimization process, showing which table is currently being optimized, and the status of each optimization operation. “OK” means the table was successfully optimized, while “Table is already up to date” means no optimization was necessary.

It’s important to note a few things about table optimization:

The `OPTIMIZE TABLE` command can be resource-intensive, especially for large tables. It’s best to run this during off-peak hours.
For InnoDB tables, this command is essentially equivalent to recreating the table, which can take a while for large tables.
Regular optimization is most beneficial for tables that experience frequent deletions or updates.

Long-Term Performance Tracking

You know how we’ve been looking at all these performance metrics? Well, it’s great to check them now and then, but wouldn’t it be even better if we could see how they change over time? That’s where long-term performance tracking comes in. Let’s set up a system to capture these stats regularly and store them in a table. This way, we can spot trends and catch potential issues before they become real problems.

First, let’s create a table to store our performance metrics:

function setupPerformanceTracking() {

session.runSql(`

CREATE TABLE IF NOT EXISTS performance_metrics (

id INT AUTO_INCREMENT PRIMARY KEY,

timestamp DATETIME,

innodb_data_reads BIGINT,

innodb_data_writes BIGINT,

innodb_data_fsyncs BIGINT,

innodb_data_pending_reads INT,

innodb_data_pending_writes INT,

innodb_data_pending_fsyncs INT

)

`);

print("Performance tracking table created. You're all set to start collecting data!");

}

module.exports.setupPerformanceTracking = setupPerformanceTracking;

Run this function once to set up your tracking table.

Now, let’s create a function to capture these metrics regularly:

function trackPerformance() {

var metrics = [

"Innodb_data_reads",

"Innodb_data_writes",

"Innodb_data_fsyncs",

"Innodb_data_pending_reads",

"Innodb_data_pending_writes",

"Innodb_data_pending_fsyncs"

];

var values = [];

for (var i = 0; i < metrics.length; i++) {

var result = session.runSql('SHOW GLOBAL STATUS LIKE "' + metrics[i] + '"');

values.push(parseInt(result.fetchOne()[1]));

}

session.runSql(`

INSERT INTO performance_metrics

(timestamp, innodb_data_reads, innodb_data_writes, innodb_data_fsyncs,

innodb_data_pending_reads, innodb_data_pending_writes, innodb_data_pending_fsyncs)

VALUES (NOW(), ?, ?, ?, ?, ?, ?)

`, values);

print("Performance metrics recorded. Keep it up!");

}

module.exports.trackPerformance = trackPerformance;

You’ll want to run this regularly – maybe every hour or so. You could schedule a task to call this function automatically using whatever tools you have in the OS you are using.

Finally, let’s create a function to analyze these trends:

function analyzePerformanceTrends() {

var result = session.runSql(`

SELECT

DATE(timestamp) as date,

AVG(innodb_data_reads) as avg_reads,

AVG(innodb_data_writes) as avg_writes,

AVG(innodb_data_pending_reads) / AVG(innodb_data_reads) as avg_read_pressure,

AVG(innodb_data_pending_writes) / AVG(innodb_data_writes) as avg_write_pressure

FROM performance_metrics

GROUP BY DATE(timestamp)

ORDER BY DATE(timestamp) DESC

LIMIT 7

`);

regularly—maybe

print("Performance Trends (Last 7 Days):");

print("Date\t\tAvg Reads\tAvg Writes\tRead Pressure\tWrite Pressure");

while (row = result.fetchOne()) {

print(`${row[0]}\t${row[1]}\t\t${row[2]}\t\t${(row[3]*100).toFixed(2)}%\t\t${(row[4]*100).toFixed(2)}%`);

}

module.exports.analyzePerformanceTrends = analyzePerformanceTrends;

This function summarizes our collected data. It shows the average daily number of reads and writes and calculates the “pressure” on your system, which is the percentage of operations that were pending.

To use these functions, you’d do something like this:

Run `setupPerformanceTracking()` once to create your metrics table.
Set up `trackPerformance()` to run every hour (or however often you like).
Once a week (or whenever you want), run `analyzePerformanceTrends()` to see how things have been going.

By tracking these metrics over time, you’ll be able to spot patterns. The key to good database management is being proactive. By keeping an eye on these trends, you’ll be able to address potential issues before they become real problems.

Conclusion

We’ve covered a lot of ground today, exploring how to optimize disk I/O and storage using MySQL Shell. From configuring InnoDB’s I/O settings to adjusting data and log file sizes, monitoring disk performance, and optimizing individual tables, these optimizations can have a major impact on your MySQL server’s performance.

Remember, optimization is an ongoing process. As your database grows and its usage patterns change, regularly revisit these settings and adjust them as needed to maintain optimal performance. In the next article, we’ll look at network and query performance optimization. We’ll use MySQL Shell to fine-tune network settings and learn how to identify and optimize slow queries. Stay tuned.

Register for Simple Talk

MySQL Shell Basic Configuration Management (Part 3 – Disk I/O and Storage Optimization)

Why We’re Using MySQL Shell

Configuring InnoDB I/O Settings with MySQL Shell

Monitoring Disk I/O Performance

Optimizing Data and Log File Storage with MySQL Shell

Fine-Tuning InnoDB Log Files with MySQL Shell

Optimizing Tables for Better Disk Performance

Long-Term Performance Tracking

Conclusion

Further Reading

Article tags

About the author

Chisom Kanu

Chisom's contributions

Articles

Books

Top topics

Chisom's latest contributions:

MySQL Shell Basic Configuration Management (Part 2 – Memory and CPU)

MySQL Shell Basic Configuration Management (Part 1 – Introduction)

Consistency and Concurrency in NewSQL Database Systems