1

I am using Mysql 8 on ubuntu 18 , the default value of innodb_io_capacity_max is 2000 . If I reduce it to 500 . will it be any issue if I load some big size file 5GB . Consider my setup 2 CPU vcore, 8GB RAM Azure instance ( Ubuntu 18) . . My consideration is that it should not failed rather due to innodb_io_capacity_max value change than slow loading has been accepted

Debashis Mallick
  • 104
  • 6

1 Answers1

1

First, let's take a look at what is innodb_io_capacity_max in office doc:

If flushing activity falls behind, InnoDB can flush more aggressively, at a higher rate of I/O operations per second (IOPS) than defined by the innodb_io_capacity variable. The innodb_io_capacity_max variable defines a maximum number of IOPS performed by InnoDB background tasks in such situations.

If you specify an innodb_io_capacity setting at startup but do not specify a value for innodb_io_capacity_max, innodb_io_capacity_max defaults to twice the value of innodb_io_capacity, with a minimum value of 2000.

Therefore, innodb_io_capacity_max is a maximum number of IOPS performed by InnoDB background tasks when flushing activity falls behind

When you reduced it to 500, it means maximum number of IOPS when flushing activity falls behind is changed to 500.

It may slow your loading operations. However, the performance of loading big file is not only related to number of IOPS of flushing to disk. There would be other key points:

  1. Hardware Performance: How about the iops of your Azure instance's disk.
  2. Change buffer size: Benifit for secondary index
  3. Redo log file size and file count: if redo log file size or file count is too small, mysql have to stop loading new insert data till enough dirty pages are flushed to disk.
  4. innodb_max_dirty_pages_pct value: if this value is too low, the flushing rate will automatically increase,
  5. concurent thread number of loading: Too have load will cause page_cleaner warning. ....

For Question, will it be any issue if I load some big size file 5GB, I think there would be some warnings of loading performance and should have no failures.

When trying to answer your question, I found 2 other questions which may be helpful for you. The links are below. This gentleman, @Bill Karwin give very helpful explainations in the answers of both questions.

Zhiyong
  • 488
  • 2
  • 5