Обсуждение: Relation between parameters in postgresql.conf file!!

Hi all,

I have gone through the below parameters:

1. vacuum_cost_page_hit
2. vacuum_cost_page_miss
3. vacuum_cost_page_dirty

Got confused by reading them from the documentation. I am unable to
understand why the values 1, 10, 20 are assigned to them(on what basis those
values have been assigned).

Is there some relation between these parameters and the below parameters?

1. autovacuum_vacuum_scale_factor=0.2(20%)
2. autovacuum_analyze_scale_factor=0.1(10%)

From the below description:

>vacuum_cost_page_hit (integer)
>The estimated cost for vacuuming a buffer found in the shared buffer cache.
It represents the cost to lock the buffer pool, lookup the shared hash table
and scan the content of the page. The default value is one
1. Does the vacuum operate on the buffer pool(memory)? Assuming it is
operated on the data files!!!
2. What does the sentence "lock the buffer pool, lookup the shared hash
table and scan the content of the page" refers to??

>vacuum_cost_page_dirty (integer)
>The estimated cost charged when vacuum modifies a block that was previously
clean. It represents the extra I/O required to flush the dirty block out to
disk again. The default value is 20.
What does actually happen here?? Why does this generate extra I/O??  

What is the meaning of the sentence "It represents the extra I/O required to
flush the dirty block out to disk again"?? Why does vacuum flush the dirty
block to disk when writer process does that?

Please clarify!!!!!!  Thanks in Advance.


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

> On Nov 2, 2018, at 2:39 AM, pavan95 <pavan.postgresdba@gmail.com> wrote:
>
>
> 1. vacuum_cost_page_hit
> 2. vacuum_cost_page_miss
> 3. vacuum_cost_page_dirty
>
>

These are tunable parameters to control auto vacuum.  Look at vacuum_cost_limit and then consider how the above
parameterswould affect it. 

> 1. autovacuum_vacuum_scale_factor=0.2(20%)
> 2. autovacuum_analyze_scale_factor=0.1(10%)
>

Those are just the factors used to determine when auto vacuum or analyze should vacuum/analyze a table.

> From the below description:
>
>> vacuum_cost_page_hit (integer)
>> The estimated cost for vacuuming a buffer found in the shared buffer cache.
> It represents the cost to lock the buffer pool, lookup the shared hash table
> and scan the content of the page. The default value is one
> 1. Does the vacuum operate on the buffer pool(memory)? Assuming it is
> operated on the data files!!!
> 2. What does the sentence "lock the buffer pool, lookup the shared hash
> table and scan the content of the page" refers to??
>

To do anything with the block in the datafile it first needs to be loaded into the buffer pool thus all associated
overheadof that operation. 

>> vacuum_cost_page_dirty (integer)
>> The estimated cost charged when vacuum modifies a block that was previously
> clean. It represents the extra I/O required to flush the dirty block out to
> disk again. The default value is 20.
> What does actually happen here?? Why does this generate extra I/O??
>
> What is the meaning of the sentence "It represents the extra I/O required to
> flush the dirty block out to disk again"?? Why does vacuum flush the dirty
> block to disk when writer process does that?
>

Again it’s the same as the other tunable parameters.. it just states the cost of modifying a clean block.