Tuning innodb_read_io_threads and innodb_write_io_threads in MySQL 8 for performance on modern hardware infrastructure involves several key considerations. Modern hardware typically includes multi-core processors, fast SSDs, and high-performance networking capabilities. Here's a guide to optimize these settings:

1. Understand the Default Settings

• MySQL 8 defaults innodb_read_io_threads and innodb_write_io_threads to 4. This is a conservative setting and may not fully utilize modern hardware capabilities.

2. Assess Your Workload

• Read-Intensive Workloads: Increase innodb_read_io_threads for databases with heavy read operations.
• Write-Intensive Workloads: Increase innodb_write_io_threads for databases with heavy write operations.

3. Evaluate Hardware Capabilities

• SSD vs. HDD: SSDs can handle a higher number of concurrent I/O operations compared to HDDs. If you're using SSDs, you can set higher values for these variables.
• CPU Cores: More CPU cores mean you can handle more threads. The number of I/O threads should not exceed the number of available cores.

4. Incremental Adjustments

• Gradually increase the values, e.g., from 4 to 8, then 16, and observe the performance impact.
• Over-tuning can lead to diminishing returns or even decreased performance due to increased context switching and CPU overhead.

5. Monitoring and Metrics

• Use monitoring tools to observe the impact of changes. Look for reduced I/O wait times and improved throughput.
• Tools like iostat, vmstat, or MySQL’s performance schema can be invaluable for this.

6. Balance Between Read and Write Threads

• Ensure there’s a balance. Allocating too many threads to one type of operation at the expense of the other can lead to inefficiencies.

7. Operating System and MySQL Configuration

• The operating system’s thread and file descriptor limits must be considered. Ensure these limits are not exceeded with the increased number of threads.

• Edit the MySQL configuration file (typically my.cnf or my.ini) to set these parameters:

  [mysqld]
  innodb_read_io_threads = 8 # Example value
  innodb_write_io_threads = 8 # Example value
  
  

8. Reviewing InnoDB Internals and Thread Concurrency

• For extremely high-performance systems, also consider tuning innodb_thread_concurrency. This parameter can help control the number of threads entering InnoDB.
• However, in many modern systems, leaving it at 0 (unlimited) is often the best choice.

9. Test in a Controlled Environment

• Always test changes in a staging environment that mirrors your production system as closely as possible.

10. Regularly Review and Update Configurations

• As your workload and hardware evolve, regularly revisit these settings to ensure they remain optimized.

Conclusion

The tuning of innodb_read_io_threads and innodb_write_io_threads should be tailored to the specific characteristics of your hardware and the nature of your workload. Modern hardware often allows for more aggressive tuning, but it's crucial to monitor and measure the impact of any changes you make.