From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-admin@lists.postgresql.org
Subject: Re: need suggestion on querying big tables
Date: Tue, 6 Dec 2022 15:59:16 +0100
Message-ID: <dcf18d66-a6ab-40ad-31dc-b18ef07be337@gmx.net> (raw)
In-Reply-To: <d926084d-63a7-854b-2d8d-459d33ec7f70@gmail.com>
References: <CAJuN1jj6NQDUjbMH=n4zry10JMJEZ8bRi=j6uA-3_108-Tq9cQ@mail.gmail.com>
<d926084d-63a7-854b-2d8d-459d33ec7f70@gmail.com>
Ron schrieb am 06.12.2022 um 15:51:
>> We can see the wait event is IO and directDatafile .
>>
>> Server Spec : 8 cores and 64GB RAM PG config : 53
>> GB(effective_cache), 12 GB(shared buffer)
>>
>> can you please suggest some ideas , how we can query on big tables
>> and fasten them to get the output??
>
> https://www.postgresql.org/docs/11/runtime-config-query.html
>
> effective_cache_size is how much RAM *a single* query gets. "When
> setting this parameter you should consider both *PostgreSQL's shared
> buffers*".
>
> https://www.postgresql.org/docs/11/runtime-config-resource.html
>
> "a reasonable starting value for *shared_buffers is 25% of the
> memory* in your system. There are some workloads where even larger
> settings for shared_buffers are effective, but because PostgreSQL
> also relies on the operating system cache, *it is unlikely that an
> allocation of more than 40% of RAM to shared_buffers will work
> better* than a smaller amount."
>
> You've set effective_cache_size to 83%, when it should be at most
> 20%.
effective_cache_size does not allocate any memory.
It's not a setting that controls "how much memory a query gets"
effective_cache_size merely a hint to the optimizer on the expected amount
of data that is likely to be in the cache.
Either the file system cache or shared_buffers.
The current setting of 12GB for shared_buffers is absolutely inside the
recommended limits. (40% would be about 25GB)
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: pgsql-admin@postgresql.org
Cc: shammat@gmx.net, pgsql-admin@lists.postgresql.org
Subject: Re: need suggestion on querying big tables
In-Reply-To: <dcf18d66-a6ab-40ad-31dc-b18ef07be337@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox