Re: Postgresql 14 performance

On 8/22/22 09:17, Kenny Bachman wrote:
> The huge_pages parameter is try. How can I know the huge page is used
> for shared buffers?
> Also, I would like to say one more thing. Some queries have high
> planning time of explain analyze output. However, I run vacuumdb
> analyze command every day. I am a little bit confused about this.
>
> Warm regards,
> Kenn

Hi Kenny,

You can check by using grep -i huge /proc/meminfo. If your shared
buffers are properly allocated, you will see something like this:

grep -i huge /proc/meminfo Anon*Huge*Pages: 0 kB Shmem*Huge*Pages: 0 kB
File*Huge*Pages: 0 kB *Huge*Pages_Total: 3072 *HugePages_Free: 6*
*Huge*Pages_Rsvd: 3 *Huge*Pages_Surp: 0 *Huge*pagesize: 2048 kB
*Huge*tlb: 6291456 kB

If, on the other side, huge pages are not allocated properly, you will get something like this:

grep -i huge /proc/meminfo Anon*Huge*Pages: 0 kB Shmem*Huge*Pages: 0 kB
File*Huge*Pages: 0 kB *Huge*Pages_Total: 0 *Huge*Pages_Free: 0
*Huge*Pages_Rsvd: 0 *Huge*Pages_Surp: 0 *Huge*pagesize: 2048 kB
*Huge*tlb: 0 kB

HugePages total and free will be equal. You need to put the right number into /etc/sysctl.conf:

grep vm.nr_hugepages /etc/sysctl.conf *vm.nr_hugepages*=3072

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com