Re: Postgresql 14 performance

Kenny Bachman <kenny.bachman17@xxxxxxxxx> · Tue, 23 Aug 2022 16:27:54 +0300

Hi Mladen,
Thank you so much for your response. I checked the huge page status; the huge pages not allocated.

AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB

Mladen Gogala <gogala.mladen@xxxxxxxxx>, 23 Ağu 2022 Sal, 03:06 tarihinde şunu yazdı:

    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
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    3072
HugePages_Free:        6
HugePages_Rsvd:        3
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         6291456 kB

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

grep -i huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               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