Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQHQz-00037o-90 for pgsql-admin@arkaria.postgresql.org; Tue, 23 Aug 2022 00:06:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQHQw-0008QM-3t for pgsql-admin@arkaria.postgresql.org; Tue, 23 Aug 2022 00:06:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQHQv-0008QD-Nr for pgsql-admin@lists.postgresql.org; Tue, 23 Aug 2022 00:06:17 +0000 Received: from mail-qt1-x82d.google.com ([2607:f8b0:4864:20::82d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oQHQt-0007kp-9B for pgsql-admin@lists.postgresql.org; Tue, 23 Aug 2022 00:06:16 +0000 Received: by mail-qt1-x82d.google.com with SMTP id w28so9247776qtc.7 for ; Mon, 22 Aug 2022 17:06:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc; bh=QMaECygS0vs1kV7Gd9bQmPKQGjT7kc37zAQg7vRAh68=; b=HIeWH9b8LlmuuwnIFNRvl5gyi1pwLxF/Ho/+Y0JNqjIg7DiaSHSCYygC78Ns4hI0dV hY69RvjH+l3j1FLKxUI98SnsT8IwMzE8e5T+r13/oPzg5QzJC/qgjbKhuHqeuTHbnpUu 2Eaiye3dOcraWSoXHO5zf35kOHl8muyd5D9jEWZA5d7RYqReZA+xBH8RRcSq2HRcxh9R YaiHYDdRCA/00lnPtTRdk1scn+XnmJkH4lD78ezWZvvNrCgiXk+nlofnLeT+qGlXaza8 vKTAz75hcF/MiinUgHZ5vQ0cLqkKVmbWVKdIEvB7UkNlPz7pCivZRtTtB9+T5HrcJdgo TzlA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc; bh=QMaECygS0vs1kV7Gd9bQmPKQGjT7kc37zAQg7vRAh68=; b=W++0JqdzqRaq/EGw7MSLyxBUJoUeFAirQJ77j01icHzaX/mH4I0thmjMabeKRiVx1t z0TnX7ODJqu0gGQUgRcaqrF0slUG+U86xTv5XQhqVZuBJSLgFE3HZgRh6QUihl1YM7qj E0R8+VP2mAJL8SGMy3X98sUUSUnhjwF39l+ibYu7JPuTSn1vaUD04ILzqGGCFfig1mos j5wNxyju9Qu6Znutwal2OE1+jzppXPkZJMA+d8LDq/pTVTuN3IVE+tMwDhczX1yIyWdd FiEexfA5egNJr7b5Ub02H8BW+NdLbIioV4FjBQUqfYbchNjCFKchYeKkn8SMmP59Jndn /DVA== X-Gm-Message-State: ACgBeo0CmA8EJJ1slgotY0mP0ScQG1PAByQdcplL6yGQiICSsnqvd4RC XUjcXT4PoK8ydNWC71TiE5A= X-Google-Smtp-Source: AA6agR4avgKySukVmW2QaAlE7k60AR5F10xPVh79FxmZay0tTFFZ7LsUuroVIbS/WHZhnLMiApV5jQ== X-Received: by 2002:ac8:5f08:0:b0:344:5cbe:c0f5 with SMTP id x8-20020ac85f08000000b003445cbec0f5mr17579237qta.356.1661213174238; Mon, 22 Aug 2022 17:06:14 -0700 (PDT) Received: from [192.168.2.100] (pool-72-79-30-4.nwrknj.east.verizon.net. [72.79.30.4]) by smtp.gmail.com with ESMTPSA id u8-20020a05622a17c800b003447c4f5aa5sm9976623qtk.24.2022.08.22.17.06.13 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 22 Aug 2022 17:06:13 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------vfBxxpkkH1SZw2oHSvWh5H00" Message-ID: Date: Mon, 22 Aug 2022 20:06:12 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.12.0 Subject: Re: Postgresql 14 performance Content-Language: en-US To: Kenny Bachman Cc: pgsql-admin@lists.postgresql.org References: From: Mladen Gogala In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------vfBxxpkkH1SZw2oHSvWh5H00 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------vfBxxpkkH1SZw2oHSvWh5H00 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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
--------------vfBxxpkkH1SZw2oHSvWh5H00--