Subject: Re: Lock contention high
To: Ashkil Dighin <ashkildighin76@gmail.com>,
 pgsql-performance@lists.postgresql.org
References: 
 <CAH6zzqCNtbCDSSFweuZp93R7TOxfEB8f75iosExb6sutyAa-Uw@mail.gmail.com>
From: Mikhail Zhilin <zhilin.ms@phystech.edu>
Message-ID: <4da46846-5835-8da7-b3c0-29c7b08e8b09@phystech.edu>
Date: Tue, 12 Oct 2021 13:29:33 +0300
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:78.0) Gecko/20100101
 Thunderbird/78.10.2
MIME-Version: 1.0
In-Reply-To: 
 <CAH6zzqCNtbCDSSFweuZp93R7TOxfEB8f75iosExb6sutyAa-Uw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------F53D081C89D9626388981D6D"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/4da46846-5835-8da7-b3c0-29c7b08e8b09%40phystech.edu>
Precedence: bulk

This is a multi-part message in MIME format.
--------------F53D081C89D9626388981D6D
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi,

How many sockets are on motherboard?
What is CPU model and interconnect type (UPI?)?
Can you share output of "lscpu"?

If you have more than 1 NUMA node it may be worth to run PostgreSQL in 
single NUMA node via taskset. It will eliminate access to remote memory 
and speed up processing.

Thanks,
  Michael.

On 10/12/21 10:35 AM, Ashkil Dighin wrote:
>
> Hi,
> Lock contention observed high in PostgreSQLv13.3
> The source code compiled with GNC(GCCv11.x)
> PostgreSQL version: 13.3
> Operating system:   RHEL8.3
> Kernel name:4.18.0-305.10.2.el8_4.x86_64
> RAM Size:512GB
> SSD: 1TB
> The environment used IBM metal and test benchmark environment HammerDbv4.2
> Test case :TPC-C
>
> Perf data for 24vu(TPC-C)
> --------------------------------
>
>       18.99%  postgres  postgres            [.] LWLockAcquire
>      7.09%  postgres  postgres            [.] _bt_compare
>      8.66%  postgres  postgres            [.] LWLockRelease
>      2.28%  postgres  postgres            [.] GetSnapshotData
>      2.25%  postgres  postgres            [.] hash_search_with_hash_value
>      2.11%  postgres  postgres            [.] XLogInsertRecord
>      1.98%  postgres  postgres            [.] PinBuffer
>
> 1.Is there a way to tune the lock contention ?
> 2.Is any recommendations to tune/reduce the lock contention via 
> postgres.conf
>
> Postgres.conf used  in Baremetal
> ========================
> shared_buffers = 128GB(1/4 th RAM size)
> effective_cachesize=392 GB(1/3 or 75% of RAM size)
> huge_pages = on
> temp_buffers = 4000MB
> work_mem = 4000MB
> maintenance_work_mem = 512MB
> autovacuum_work_mem = -1
> max_stack_depth = 7MB
> dynamic_shared_memory_type = posix
> max_files_per_process = 4000
> effective_io_concurrency = 32
> wal_level = minimal
> synchronous_commit = off
> wal_buffers = 512MB
> checkpoint_timeout = 1h
> checkpoint_completion_target = 1
> checkpoint_warning = 0
> log_min_messages = error
> log_min_error_statement = error
> log_timezone = 'GB'
> autovacuum = off
> datestyle = 'iso, dmy'
> timezone = 'GB'
> lc_messages = 'en_GB.UTF-8'
> lc_monetary = 'en_GB.UTF-8'
> lc_numeric = 'en_GB.UTF-8'
> lc_time = 'en_GB.UTF-8'
> default_text_search_config = 'pg_catalog.english'
> max_locks_per_transaction = 64
> max_pred_locks_per_transaction = 64
>
> Best Regards
> Anil
>


--------------F53D081C89D9626388981D6D
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    Hi,<br>
    <br>
    How many sockets are on motherboard? <br>
    What is CPU model and interconnect type (UPI?)? <br>
    Can you share output of "lscpu"? <br>
    <br>
    If you have more than 1 NUMA node it may be worth to run PostgreSQL
    in single NUMA node via taskset. It will eliminate access to remote
    memory and speed up processing. <br>
    <br>
    Thanks,<br>
     Michael.<br>
    <br>
    <div class="moz-cite-prefix">On 10/12/21 10:35 AM, Ashkil Dighin
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAH6zzqCNtbCDSSFweuZp93R7TOxfEB8f75iosExb6sutyAa-Uw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div><br>
      </div>
      <div>
        <div dir="ltr">
          <div>Hi,</div>
          <div><font color="#000000">Lock contention observed high in
              PostgreSQLv13.3</font></div>
          <div><font color="#000000">The source code compiled with
              GNC(GCCv11.x)</font></div>
          <div><font color="#000000">PostgreSQL version: 13.3<br>
              Operating system:   RHEL8.3</font></div>
          <div><font color="#000000">Kernel name:4.18.0-305.10.2.el8_4.<wbr>x86_64</font></div>
          <div><font color="#000000">RAM Size:512GB</font></div>
          <div><font color="#000000">SSD: 1TB</font></div>
          <div><font color="#000000">The environment used IBM metal and
              test benchmark environment HammerDbv4.2<br>
              Test case :TPC-C<br>
              <br>
              Perf data for 24vu(TPC-C)<br>
              ------------------------------<wbr>--<br>
              <br>
                    18.99%  postgres  postgres            [.]
              LWLockAcquire<br>
                   7.09%  postgres  postgres            [.] _bt_compare<br>
                   8.66%  postgres  postgres            [.]
              LWLockRelease<br>
                   2.28%  postgres  postgres            [.]
              GetSnapshotData<br>
                   2.25%  postgres  postgres            [.]
              hash_search_with_hash_value<br>
                   2.11%  postgres  postgres            [.]
              XLogInsertRecord<br>
                   1.98%  postgres  postgres            [.] PinBuffer<br>
              <br>
            </font></div>
          <div><font color="#000000">1.Is there a way to tune the lock
              contention ?<br>
              2.Is any recommendations to tune/reduce the lock
              contention via postgres.conf</font></div>
          <div><font color="#000000"><br>
              Postgres.conf used  in Baremetal<br>
              ========================<br>
              shared_buffers = 128GB(1/4 th RAM size)</font></div>
          <div><font color="#000000">effective_cachesize=392 GB(1/3 or
              75% of RAM size)                        </font>
            <div>
              <div><font color="#000000">huge_pages = on               <br>
                  temp_buffers = 4000MB                 <br>
                  work_mem = 4000MB                     <br>
                  maintenance_work_mem = 512MB           <br>
                  autovacuum_work_mem = -1               <br>
                  max_stack_depth = 7MB                 <br>
                  dynamic_shared_memory_type = posix     <br>
                  max_files_per_process = 4000           <br>
                  effective_io_concurrency = 32         <br>
                  wal_level = minimal                   <br>
                  synchronous_commit = off               <br>
                  wal_buffers = 512MB                            <br>
                  checkpoint_timeout = 1h         <br>
                  checkpoint_completion_target = 1       <br>
                  checkpoint_warning = 0         <br>
                  log_min_messages = error               <br>
                  log_min_error_statement = error<br>
                  log_timezone = 'GB'<br>
                  autovacuum = off                       <br>
                  datestyle = 'iso, dmy'<br>
                  timezone = 'GB'<br>
                  lc_messages = 'en_GB.UTF-8'           <br>
                  lc_monetary = 'en_GB.UTF-8'           <br>
                  lc_numeric = 'en_GB.UTF-8'             <br>
                  lc_time = 'en_GB.UTF-8'               <br>
                  default_text_search_config = 'pg_catalog.english'</font><br>
                <font color="#000000">max_locks_per_transaction = 64   
                       <br>
                  max_pred_locks_per_transaction = 64</font><br>
              </div>
              <div><br>
              </div>
              <div><font color="#000000">Best Regards</font></div>
              <div><font color="#000000">Anil</font></div>
            </div>
          </div>
          <div><font color="#000000"><br>
            </font></div>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------F53D081C89D9626388981D6D--