I have executed a Snowflake query and the Profile output looks like this:
What is the difference between the Local Disk IO and Remote Disk IO? What is happening at each level?
4 Answers
'Local Disk' IO refers to processing blocked by local disk access (i.e. local to the worker nodes of the virtual warehouse), whereas 'Remote Disk IO' refers to the processing time blocked by remote disk access. In essence, when the amount of memory available on the underline EC2 instance running each of the virtual warehouse nodes is not enough, the query processing engine starts spilling the data to the local disks or remote disks if the local disks are not sufficient.
The Snowflake documentation is self-explanatory:
Remote disk is reading data from the database, not "spilling". Local Disk I/O is local disk usage that is part of normal processing. Spilling occurs when the warehouse can't fit all the data into memory and local disk cache. In you example, neither is "spilling". Remote disk I/O is reading from the database. You can improve performance of this query by using the next larger warehouse size; data will fit most likely fit in memory, eliminating most of the local storage. "Spilling" will be shown in the profile with the word "Spilling".
Because all of the data doesn't necessarily fit in memory. You can think of it much like virtual memory on the server with pages swapped to local disk due to memory limitation.. If you increase the warehouse one size, you should see the local disk I/0 decline by about 50%.
I agree with #3, from the screenshot, we can't determine if the spillage actually happened, since it shows up as a separate section in the query profile. So, I would think that local I/O is the data that has already been read from the remote storage, and remote I/O is directly reading it from the storage layer, and hence it takes much longer. On the second execution of the same query/against the same table, the query should show local I/O.
Related