Help Center/ GaussDB(DWS)/ More Documents/ Developer Guide (Kuala Lumpur Region)/ Resource Load Management/ Priority-based Scheduling/ I/O
Updated on 2022-08-16 GMT+08:00
View PDF
Share

I/O

Context

Control I/O resources for data import or complex queries.

I/O resources can be managed based on priority or upper limits at session- or user-level.

Figure 1 illustrates how I/O resources are managed.

Figure 1 Managing I/O resources

I/O resource management complies with the following rules:

  • Not all statement I/O is controlled. The write I/O of only INSERT INTO SELECT, COPY FROM, and CREATE TABLE AS statements can be controlled. Read I/O will be controlled if the amount of data to be scanned on a single DN exceeds 500 MB. The data volume is estimated by the optimizer. VACUUM FULL, CREATE INDEX, CLUSTER TABLE USING INDEX, ANALYZE, and MERGE INTO are read/write operations and will possibly trigger I/O control under certain conditions.
  • You need to manually set GUC parameter or resource pool settings to enable I/O control.
  • The GUC parameters io_priority and io_limits can control I/O based on priority and upper limit, respectively, at session level.
  • You can set io_limits or io_priority, and associate a user with a resource pool to implement control I/O at user level.
  • io_limits controls the maximum number of I/O requests that can be triggered per second. The default value is 0, indicating no control. The number of requests is counted by ones for column storage and by thousands for row storage.
  • When the disk usage reaches 90%, the I/O control specified by io_priority is triggered. The priority can be High, Medium, or Low, which indicate that 80%, 50%, or 20% of I/O triggered by jobs are controlled. The default value is None, indicating no control.
    • According to the buffer policy, read/write I/O requests are controlled for row storage during buffer read/write instead of disk read/write. The unit of io_limits is ten thousand times per second. The value can be set to around 100. You are advised to use io_priority for row storage, so that I/O resources can be fully utilized when they are sufficient.
    • The mechanisms for querying and importing data in column-store storage are different from those in row storage. The value of io_limits can be set to around 100. Values much larger may fail to control I/O.

Querying I/O Resource Management Views

  • pg_session_iostat monitors I/O resources at the session level. 
    1
    		 
    select * from pg_session_iostat;
  • pg_user_iostat monitors I/O resources at the user level. 
    1
    		 
    select * from pg_user_iostat('username');