Thread: huge shared_blocks_hit one select but manually run very fast

Hi, 

   we have a simple select .... from table where ... (that mache the index) , table has 80million rows.  when many application sessions run the query and at the same time some other sessions doing insert into ... this table. from pg_stat_statements, shared_blks_hit show 31652 / per call.   we see very high cpu almost 100% cpu during application workload test, and high LWLock BufferMapping waiting for these querys.  But manually run the sql show only 2148 shared_blks_hit/ per call.  this is a simple sql, from pg_profile we did see it use same index scan as manually running.  What could be possible reason leading so big difference with shared_blks_hit ?  

 PGv14.8 

Thanks,

James

   Yes, we have primary key and another index that include bigint and uuid that stored in database as varchar(64),   many sessions inserting ... values  and   at the same time, many sessions running the select query.   table size 13GB, primary is 6GB,   the other index is 13GB too, the select query use the 13GB index only scan.  

   in addition to this table and query,  during the workload, we have other two hot tables with similar bigint and uuid composite index, inserting by many sessions and select in many sessions too, but the table size is smaller,  table 3790MB, index 1445MB. 

   when the workload started, database server cpu got increased very fast and in tens of seconds, cpu is almost 100% used, and then buffermapping LWLock started for these selects , even the inserts started see buffermapping contention too. 

  attached please find top 1 table and sql details, another two tables and it's query are similar.

Thanks,

James 

David Mullineux <dmullx@gmail.com> 於 2024年12月22日週日 上午12:41寫道：

Depends on a lot of thongs...Visibility map sounds like it's impacted here. Are your inserts towards the index (like a monotonically increasing serial id)  or scattered around the index values ?   How big is the table  index and shared buffers ?   An example would really help

On Sat, 21 Dec 2024, 11:51 James Pang, <jamespang886@gmail.com> wrote:

Hi, 

   we have a simple select .... from table where ... (that mache the index) , table has 80million rows.  when many application sessions run the query and at the same time some other sessions doing insert into ... this table. from pg_stat_statements, shared_blks_hit show 31652 / per call.   we see very high cpu almost 100% cpu during application workload test, and high LWLock BufferMapping waiting for these querys.  But manually run the sql show only 2148 shared_blks_hit/ per call.  this is a simple sql, from pg_profile we did see it use same index scan as manually running.  What could be possible reason leading so big difference with shared_blks_hit ?  

 PGv14.8 

Thanks,

James