4.3 Dirty Buffer Throttling

Table of Contents Previous Next


4 EDB Resource Manager : 4.3 Dirty Buffer Throttling

Writing to shared buffers is controlled by setting the dirty_rate_limit resource type parameter.
Set the dirty_rate_limit parameter to the number of kilobytes per second for the combined rate at which all the processes in the group should write to or “dirty” the shared buffers. An example setting would be 3072 kilobytes per seconds.
The valid range of the dirty_rate_limit parameter is 0 to 1.67772e+07. A setting of 0 means no dirty rate limit has been set for the resource group.
EDB Resource Manager utilizes dirty buffer throttling to keep the aggregate, shared buffer writing rate of all processes in the group near the limit specified by the dirty_rate_limit parameter. A process in the group may be interrupted and put into sleep mode for a short interval of time to maintain the defined limit. When and how such interruptions occur is defined by a proprietary algorithm used by EDB Resource Manager.
The ALTER RESOURCE GROUP command with the SET dirty_rate_limit clause is used to set the dirty rate limit for a resource group.
In the following example the dirty rate limit is set to 12288 kilobytes per second for resgrp_a, 6144 kilobytes per second for resgrp_b and 3072 kilobytes per second for resgrp_c. This means that the combined writing rate to the shared buffer of all processes assigned to resgrp_a is maintained at approximately 12288 kilobytes per second. Similarly, for all processes in resgrp_b, the combined writing rate to the shared buffer is kept to approximately 6144 kilobytes per second, etc.
The following query shows the settings of dirty_rate_limit in the catalog.
Changing the dirty_rate_limit of a resource group not only affects new processes that are assigned to the group, but any currently running processes that are members of the group are immediately affected by the change. That is, if the dirty_rate_limit is changed from 12288 to 3072, currently running processes in the group would be throttled downward so that the aggregate group dirty rate would be near 3072 kilobytes per second instead of 12288 kilobytes per second.
The FILLFACTOR = 10 clause results in INSERT commands packing rows up to only 10% per page. This results in a larger sampling of dirty shared blocks for the purpose of these examples.
The pg_stat_statements module is used to display the number of shared buffer blocks that are dirtied by a SQL command and the amount of time the command took to execute. This provides the information to calculate the actual kilobytes per second writing rate for the SQL command, and thus compare it to the dirty rate limit set for a resource group.
In order to use the pg_stat_statements module, perform the following steps.
Step 1: In the postgresql.conf file, add $libdir/pg_stat_statements to the shared_preload_libraries configuration parameter as shown by the following.
Step 2: Restart the database server.
Step 3: Use the CREATE EXTENSION command to complete the creation of the pg_stat_statements module.
The pg_stat_statements_reset() function is used to clear out the pg_stat_statements view for clarity of each example.
The following sequence of commands shows the creation of table t1. The current process is set to use resource group resgrp_b. The pg_stat_statements view is cleared out by running the pg_stat_statements_reset() function.
Finally, the INSERT command generates a series of integers from 1 to 10,000 to populate the table, and dirty approximately 10,000 blocks.
The following shows the results from the INSERT command without the usage of a resource group.
For this example the inserts are performed simultaneously on two different tables in two separate psql sessions, each of which has been added to resource group resgrp_b that has a dirty_rate_limit set to 6144 kilobytes per second.
Note: The INSERT commands in session 1 and session 2 were started after the SELECT pg_stat_statements_reset() command in session 2 was run.
The following shows the results from the INSERT commands in the two sessions. RECORD 3 shows the results from session 1. RECORD 2 shows the results from session 2.
In this example, two additional psql sessions are used along with the previous two sessions. The third and fourth sessions perform the same INSERT command in resource group resgrp_c with a dirty_rate_limit of 3072 kilobytes per second.
Sessions 1 and 2 are repeated as illustrated in the prior example using resource group resgrp_b. with a dirty_rate_limit of 6144 kilobytes per second.
Note: The INSERT commands in all four sessions were started after the SELECT pg_stat_statements_reset() command in session 4 was run.
The following shows the results from the INSERT commands in the four sessions. RECORD 3 shows the results from session 1. RECORD 2 shows the results from session 2. RECORD 4 shows the results from session 3. RECORD 5 shows the results from session 4.
First note that the times of session 1 (28407.435) and session 2 (31343.458) are close to each other as they are both in the same resource group with dirty_rate_limit set to 6144, as compared to the times of session 3 (52727.846) and session 4 (56063.697), which are in the resource group with dirty_rate_limit set to 3072. The latter group has a slower dirty rate limit so the expected processing time is longer as is the case for sessions 3 and 4.

4 EDB Resource Manager : 4.3 Dirty Buffer Throttling

Table of Contents Previous Next