public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Mullineux <[email protected]>
To: James Pang <[email protected]>
Cc: [email protected]
Subject: Re: huge shared_blocks_hit one select but manually run very fast
Date: Wed, 25 Dec 2024 14:07:54 +0000
Message-ID: <CAGsyd8WyZakoq9zu_iuXCPbk2y8h9ey_RDEXvOjEtD7RCKijMg@mail.gmail.com> (raw)
In-Reply-To: <CAHgTRfcO-2ExhpOccq8RBRSgFOBA8xYFA5Qt=gdkDWyZpupxbQ@mail.gmail.com>
References: <CAHgTRff_1mTsU-QSaDBY5k9+D-i9sV0aQeRY9ShffV=6b7jOWQ@mail.gmail.com>
<CAGsyd8U0cgD=2wTG5HAsbs=e=9ynz3r55tH56xzQeUQYju94Yw@mail.gmail.com>
<CAHgTRfcO-2ExhpOccq8RBRSgFOBA8xYFA5Qt=gdkDWyZpupxbQ@mail.gmail.com>
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 <[email protected]> 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 <[email protected]> 於 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, <[email protected]> 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
>>>
>>
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: huge shared_blocks_hit one select but manually run very fast
In-Reply-To: <CAGsyd8WyZakoq9zu_iuXCPbk2y8h9ey_RDEXvOjEtD7RCKijMg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox