-1

I have to backfill some column data in postgressql table .it contains huge amount of data (around 100 million).Now for this I am using pg-batch and reading certain batchsize and then writing it in database(in certain batch size) . In GCP there are read and write IOPS . I have observed that even if I am decreasing write batch size ( from 2 x to x) and at the same time increasing read batch size from x to 2x .Write IOPS are still increasing while only read IOPS should have increased.

SomethingDark
  • 13,229
  • 5
  • 50
  • 55
Perf2017
  • 29
  • 5

1 Answers1

2

IOPS means disk I/O operations per second and not database I/O operations. A single disk I/O can be up to 16 KB of consecutive data blocks. Smaller reads or writes will still consume one I/O operation.

To determine how many disk I/O operations would result from a database transaction is very difficult to determine and would require determining, among many other items, was the data fetched from a cache or a temporary table (no IOPS) or how the database has laid out data within the disk or file system. The complexity of the tables (indexes) also plays a significant factor. One database operation could result in dozens or even thousands of disk operations.

In other words, there is a correlation between database I/O and disk I/O but you cannot make assumptions based on the limited technique you are using.

Note: decreasing the write batch size typically would result in a higher number of IOs and not a lower number.

John Hanley
  • 74,467
  • 6
  • 95
  • 159
  • thanks john.I dint understand the note where you mentioned decreasing the write batch size typically would result in a higher number of IOs. – Perf2017 Mar 31 '22 at 03:42
  • 1
    @Perf2017 - Think of it this way. You want to update several rows of a table. Once that is finished the database must also update the journal/logs and a number of other items. Some of those items are not directly related to the size of your batch. Let's say the management I/O is 100 blocks and the row items are 1,000 for a total of 1100. Reduce the row items to 500 and then later another 500, the total block I/O is now 1200 instead of 1100. I have oversimplified but I hope you get the point. – John Hanley Mar 31 '22 at 04:29