Re: Suddenly all queries moved to seq scan

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.