Thread

1. huge shared_blocks_hit one select but manually run very fast

   James Pang <jamespang886@gmail.com> — 2024-12-21T11:50:40Z

   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
   

2. Re: huge shared_blocks_hit one select but manually run very fast

   David Mullineux <dmullx@gmail.com> — 2024-12-21T16:40:54Z

   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
   >
   

3. Re: huge shared_blocks_hit one select but manually run very fast

   James Pang <jamespang886@gmail.com> — 2024-12-22T03:38:37Z

      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
   >>
   >
   

4. Re: huge shared_blocks_hit one select but manually run very fast

   David Mullineux <dmullx@gmail.com> — 2024-12-25T14:07:54Z

   Out of interest. Why is a UUID being stored as a varchar(64) and not as a
   uuid type ?
   Ny motivation  is to makes tables much smaller and much faster to lookup a
   key if the key is of the correct data-type.
   
   On Sun, 22 Dec 2024 at 03:38, James Pang <jamespang886@gmail.com> wrote:
   
   >    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
   >>>
   >>