Re: Relation between parameters in postgresql.conf file!!

> On Nov 2, 2018, at 2:39 AM, pavan95 <pavan(dot)postgresdba(at)gmail(dot)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 parameters would 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 overhead of 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.