Content-Type: multipart/alternative;
 boundary="------------SfgJW6fjZgt0mQWTzrEsO39h"
Message-ID: <a2a12fe8-0be8-458b-8098-b129f63c4ea2@cloud.gatewaynet.com>
Date: Wed, 20 Nov 2024 14:56:05 +0200
MIME-Version: 1.0
Subject: Re: Suddenly all queries moved to seq scan
To: pgsql-general@lists.postgresql.org
References: <CAOW_W8PZ7Op_vOT9szMnpZE4jOmP09NWfc3M1ORTFMzjebpK5w@mail.gmail.com>
Content-Language: en-US
From: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <CAOW_W8PZ7Op_vOT9szMnpZE4jOmP09NWfc3M1ORTFMzjebpK5w@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/a2a12fe8-0be8-458b-8098-b129f63c4ea2%40cloud.gatewaynet.com>
Precedence: bulk

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

On 11/20/24 12:50, Sreejith P wrote:

> Hi,
>
> We are using PostgresQL 10 in our production database. We have around 
> 890 req /s request on peak time.
>
> We have 1 primary and 4 slave databases as well in the same postgres 
> cluster.
>
> 2 days back we applied some patches in the primary server and 
> restarted. We didn't do anything on the secondary server.
>
> Next day, After 18 hours all our queries from secondary servers 
> started taking too much time.  queries were working in 2 sec started 
> taking 80 seconds. Almost all queries behaved the same way.
>
> After half an hour of outage we restarted all db servers and system 
> back to normal.
>
> Still we are not able to understand the root case. We couldn't find 
> any error log or fatal errors.  During the incident, in  one of the 
> read server disks was full. We couldn't see any replication lag or 
> query cancellation due to replication.
>
> please help
Still you say seq scan, how do you know that? You run explain (buffers, 
analyze, settings) ? If this is indeed the case, then you need to check 
your indexes. Check when they were last used/scanned and if they are 
usable. Did the disk error had any of your PostgreSQL tablespaces / 
tables/indexes  ?
>
> Regards
> Sreejith
>
>
>
>
>
> *Solutions for Care Anywhere*
>
> ------------------------------------------------------------------------
> *dWise HealthCare IT Solutions Pvt. Ltd.*| www.lifetrenz.com 
> <http://www.lifetrenz.com>
> *Disclaimer*: The information and attachments contained in this email 
> are intended for exclusive use of the addressee(s) and may contain 
> confidential or privileged information. If you are not the intended 
> recipient, please notify the sender immediately and destroy all copies 
> of this message and any attachments. The views expressed in this email 
> are, unless otherwise stated, those of the author and not those of 
> dWise HealthCare IT Solutions or its management. 
--------------SfgJW6fjZgt0mQWTzrEsO39h
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>On 11/20/24 12:50, Sreejith P wrote:</p>
    <blockquote type="cite"
cite="mid:CAOW_W8PZ7Op_vOT9szMnpZE4jOmP09NWfc3M1ORTFMzjebpK5w@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">Hi,
          <div><br>
          </div>
          <div>We are using PostgresQL 10 in our production database. 
            We have around 890 req /s request on peak time.</div>
          <div><br>
          </div>
          <div>We have 1 primary and 4 slave databases as well in the
            same postgres cluster. </div>
          <div><br>
          </div>
          <div>2 days back we applied some patches in the primary server
            and restarted. We didn't do anything on the secondary
            server.</div>
          <div><br>
          </div>
          <div>Next day, After 18 hours all our queries from secondary
            servers started taking too much time.  queries were working
            in 2 sec started taking 80 seconds. Almost all queries
            behaved the same way.</div>
          <div><br>
          </div>
          <div>After half an hour of outage we restarted all db servers
            and system back to normal.</div>
          <div><br>
          </div>
          <div>Still we are not able to understand the root case. We
            couldn't find any error log or fatal errors.  During the
            incident, in  one of the read server disks was full. We
            couldn't see any replication lag or query cancellation due
            to replication.</div>
          <div><br>
          </div>
          <div>please help</div>
        </div>
      </div>
    </blockquote>
    Still you say seq scan, how do you know that? You run explain
    (buffers, analyze, settings) ? If this is indeed the case, then you
    need to check your indexes. Check when they were last used/scanned
    and if they are usable. Did the disk error had any of your
    PostgreSQL tablespaces / tables/indexes  ?<br>
    <blockquote type="cite"
cite="mid:CAOW_W8PZ7Op_vOT9szMnpZE4jOmP09NWfc3M1ORTFMzjebpK5w@mail.gmail.com">
      <div dir="ltr">
        <div dir="ltr">
          <div><br>
          </div>
          <div>Regards</div>
          <div>Sreejith</div>
          <div><br>
          </div>
          <div><br>
          </div>
          <div><br>
          </div>
          <div><br>
          </div>
        </div>
      </div>
      <br>
      <div>
        <p
style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><img
            moz-do-not-send="true"></p>
        <p
style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><img
            src="https://www.lifetrenz.com/lt-logo.png"
            moz-do-not-send="true"> </p>
        <p
style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><b><span
              style="color:rgb(0,32,96)">Solutions for Care Anywhere</span></b></p>
      </div>
      <hr><span><font color="#888888"><b><span
style="color:rgb(31,73,125);font-family:&quot;Calibri&quot;,sans-serif;font-size:11pt">dWise
              HealthCare IT Solutions Pvt. Ltd.</span></b><span
style="color:rgb(31,73,125);font-family:&quot;Calibri&quot;,sans-serif;font-size:11pt">
            | </span><span lang="EN-US"
style="color:rgb(31,73,125);font-family:&quot;Arial&quot;,sans-serif;font-size:7pt"><a
              href="http://www.lifetrenz.com" target="_blank"
              moz-do-not-send="true"><span style="color:rgb(5,99,193)">www.lifetrenz.com</span></a><br>
          </span><font color="gray"><b><span lang="EN-US"
style="font-family:&quot;Arial&quot;,sans-serif;font-size:7pt">Disclaimer</span></b><span
              lang="EN-US"
style="font-family:&quot;Arial&quot;,sans-serif;font-size:7pt">: The
              information and attachments contained in this email are
              intended for exclusive use of the addressee(s) and may
              contain confidential or privileged information. If you are
              not the intended recipient, please notify the sender
              immediately and destroy all copies of this message and any
              attachments. The views expressed in this email are, unless
              otherwise stated, those of the author and not those of
              dWise HealthCare IT Solutions or its management.</span></font></font></span>
    </blockquote>
  </body>
</html>

--------------SfgJW6fjZgt0mQWTzrEsO39h--