Tuning PostgreSQL with pgbench

The recommended size of shared_buffer is 25%. We have dedicated database server with 312G, a recommended starting value of shared_buffer is 25% of the memory in your system.
Larger settings for shared_buffers usually require a corresponding increase in checkpoint_segments, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.

I have updated the parameter shared_buffer in postgresql.conf and restarted the service.

Before parameter change # 

A.  1000 transactions with 10 sessions using two threads

-bash-4.2$ ./pgbench -c 10 -j 2 -t 10000 db2019_pt

starting vacuum...end.

transaction type: <builtin: TPC-B (sort of)>

scaling factor: 50

query mode: simple

number of clients: 10

number of threads: 2

number of transactions per client: 10000

number of transactions actually processed: 100000/100000

latency average = 5.159 ms

tps = 1938.401955 (including connections establishing)

tps = 1938.558540 (excluding connections establishing)

After parameter change#

A.. After parameter(shared_buffer)  changes 1000 transactions with 10 client using 2 threads

-bash-4.2$ ./pgbench -c 10 -j 2 -t 10000 db2019_pt

starting vacuum...end.

transaction type: <builtin: TPC-B (sort of)>

scaling factor: 50

query mode: simple

number of clients: 10

number of threads: 2

number of transactions per client: 10000

number of transactions actually processed: 100000/100000

latency average = 4.548 ms

tps = 2198.851379 (including connections establishing)

tps = 2199.039680 (excluding connections establishing)

-bash-4.2$

Our transactions per sec went from 1938 to 2199 with 10% increase in performance, the throughput increased after increasing the value of shared_buffer.

Effective_Cache_size parameter Tuning

Effective_cache_size:

Set the planners assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index. A higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL’s shared buffers and the portion of the kernel’s disk cache that will be used for PostgreSQL data files, though some data might exist in both places. Also take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache, it is used only for estimation purposes. The system also doest not assume data remains in the disk cache between queries. The default is 4 gigabytes (4GB).

The same rough of thumb that would put shared_buffers at 25% of system memory would set effective_cache_size to between 50% and 75% of RAM. To get a more accurate estimate, first observe the size of the file system cache.

The Practical impact of effective_cache_size:-

The cost of this query is 0.43.. 99923664

we set effective_cache_size to 10000 MB size then execute the query statement.

Now you can see the 0.43..109179.59