Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qT1qV-0089Sv-4t for pgsql-general@arkaria.postgresql.org; Mon, 07 Aug 2023 15:08:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qT1qT-00ADs6-KW for pgsql-general@arkaria.postgresql.org; Mon, 07 Aug 2023 15:08:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qT1qT-00ADry-6b for pgsql-general@lists.postgresql.org; Mon, 07 Aug 2023 15:08:33 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qT1qQ-0012g7-KS for pgsql-general@lists.postgresql.org; Mon, 07 Aug 2023 15:08:32 +0000 Received: by mail-oi1-x230.google.com with SMTP id 5614622812f47-3a3e1152c23so3432242b6e.2 for ; Mon, 07 Aug 2023 08:08:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691420909; x=1692025709; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=EIKzYo5uVN5z6nb5nRuOMD2fzW19Fhd/p5SfNZNs2B0=; b=MLgLuHq8ShzPAbyoV+cJ1Ih3NLWqtpr6v8ttzSrwAQzcmAnvSbXMCkyj1HAiZHhAf6 qYHf+3nGqHqqfMmavwx3lEiVWUNhnfBgcxcp7qDaI2XxWGu/PCgpAUM3i7kkJEDgypz+ jDRd47EjALlDd8Xqlbyq5mGcESGRYyqDPWt2l8BpJJJF0jWH1TbMmA9rpPAjbf7OImzv cDLnttgZ4GAdEfqBEpWQdFOVGXPgjCE2cnTWS/gNd59ZIfgg3+5BIGIlAUaEswWtREjS ZdpbYdg03GTbI8OLtHcWM7x7tdG+yz274WkMA4WZSsQ0wvsoQ0VPuj1HmNcZUfZ5QCqJ aL2Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691420909; x=1692025709; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=EIKzYo5uVN5z6nb5nRuOMD2fzW19Fhd/p5SfNZNs2B0=; b=mFDFmfLIbpWXY0n8YKLiAZWjSh6zPky0Ge7FroFep8VsT5Vhe6qYBtBte0kI7R8I0N lnUlRvw9G6cLDh2nd4XLQ1rz4V1hCL3ZH2KpuGlbQU9dItRf8h40DYG0+Wm6GhzxLwe2 KzkggJaDWIXRYnV+bsBTl7INEVBHZOOYNGbU85RuAUWpYZblfri0PaVCr/FAR1/ozIai SjEprvBDXI5/6BpDETkD71kLZtZiitAII8PeaHGqxmQsz0Hg0LrNAFOrJsFcTobcr76h 5Njpas+jPSWns9Sxlsv7y9OrhQzV5G1sjUWTqcYDI1qHzJUBp/LU0yuJhLDCjDa/vvbO E1qA== X-Gm-Message-State: AOJu0Yz236T3AEzX6ZCE8842puH7qcBEWL2BkYpiDeZrFW1P7IolptgX DM9f7+fnxz6IF6oD/vJKIpep1sKgf6k= X-Google-Smtp-Source: AGHT+IFd0u3kWZgsmVPYVi1XzgkG+lx64HNNst5IFocgDz1fSOsF8HR9UjtWlexDJbBJXlrWYJv6Xw== X-Received: by 2002:a05:6808:15a9:b0:3a7:7d4:4f91 with SMTP id t41-20020a05680815a900b003a707d44f91mr12753600oiw.14.1691420909548; Mon, 07 Aug 2023 08:08:29 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:df7:89d:77c9:8e6a? ([2600:1700:bd2c:b810:df7:89d:77c9:8e6a]) by smtp.googlemail.com with ESMTPSA id q5-20020a5b0345000000b00d17586ff1f7sm2265049ybp.28.2023.08.07.08.08.29 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Aug 2023 08:08:29 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------tiFYKli4YV3bjs0fq0upS8oW" Message-ID: <1a4d0a85-c037-a9a4-d550-a51fc5e556ff@gmail.com> Date: Mon, 7 Aug 2023 10:08:28 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: DB Server slow down & hang during Peak hours of Usage Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron 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. --------------tiFYKli4YV3bjs0fq0upS8oW Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 8/7/23 08:41, KK CHN wrote: > List , > > *Description:* > > Maintaining a DB Server Postgres and with a lot of read writes to this > Server( virtual machine running on  ESXi 7 with CentOS 7) . > > ( I am not sure how to get the read / write counts or required IOPS or any > other parameters for you. If  you point our  I can execute those commands > and get the data. ) > > Peak hours  say 19:00 Hrs to 21:00 hrs it hangs ( The application is an > Emergency call response system  writing many  Emergency Response vehicles > locations coordinates to the DB every 30 Seconds and every emergency call > metadata (username, phone number, location info and address of the caller > to the DB for each call) > > During these hours  the system hangs and the  Application ( which shows > the location of the vehicles on a  GIS map hangs ) and the CAD machines > which connects to the system hangs as those machines can't  connect to the > DB and get data for displaying the caller information to the call taking > persons working on them. ) Have you looked at the postgresql log file? Have you looked in the *application* error log? > > *Issue : * > How to trace out what makes this DB  hangs and make it slow  and how to > fix it.. > > *Resource poured on the system :* > * > * > *64 vCPUs  allocate ( Out of a host machine comprised of 2 processor slots > of 20 cores each with Hyper Threading, intel xeon 2nd Gen, CPU usage show > 50 % in vCentre Console), and RAM 64 GB allocated ( buy usage always > showing around 33 GB only ) * > * > * What else happens on the ESXi host between 19:00 and 21:00? > *Query :* > * > * > How to rectify the issues that makes the DB server underperforming and > find a permanent fix for this slow down issue*. * > * > * > *Attached the  Postgres.conf file here for reference .* > * > * > *Any more information required I can share for analysis to fix the issue. * > * > * > > *Krishane * -- Born in Arizona, moved to Babylonia. --------------tiFYKli4YV3bjs0fq0upS8oW Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 8/7/23 08:41, KK CHN wrote:
List ,

Description:

Maintaining a DB Server Postgres and with a lot of read writes to this Server( virtual machine running on  ESXi 7 with CentOS 7) . 

( I am not sure how to get the read / write counts or required IOPS or any other parameters for you. If  you point our  I can execute those commands and get the data. ) 

Peak hours  say 19:00 Hrs to 21:00 hrs it hangs ( The application is an Emergency call response system  writing many  Emergency Response vehicles locations coordinates to the DB every 30 Seconds and every emergency call metadata (username, phone number, location info and address of the caller to the DB for each call)  

During these hours  the system hangs and the  Application ( which shows the location of the vehicles on a  GIS map hangs ) and the CAD machines which connects to the system hangs as those machines can't  connect to the DB and get data for displaying the caller information to the call taking persons working on them. )

Have you looked at the postgresql log file?

Have you looked in the application error log?


Issue : 
How to trace out what makes this DB  hangs and make it slow  and how to fix it..

Resource poured on the system :

64 vCPUs  allocate ( Out of a host machine comprised of 2 processor slots of 20 cores each with Hyper Threading, intel xeon 2nd Gen, CPU usage show 50 % in vCentre Console), and RAM 64 GB allocated ( buy usage always showing around 33 GB only ) 


What else happens on the ESXi host between 19:00 and 21:00?

Query :

How to rectify the issues that makes the DB server underperforming and find a permanent fix for this slow down issue. 

Attached the  Postgres.conf file here for reference .

Any more information required I can share for analysis to fix the issue. 


Krishane 

--
Born in Arizona, moved to Babylonia.
--------------tiFYKli4YV3bjs0fq0upS8oW--