Specific query taking time to process
Fahiz Mohamed <[email protected]> Sat, 7 Dec 2019 20:05:59 +0000
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <d8d95b54-9e85-4137-8ff2-7589b121e17d@Spark> |
--5dec062c_5072367_168 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi team, Could you please help me with this strange issue I am facing in my curren= t live server I am maintaining. There is a specific search query I am running to get list of Documents an= d their metadata from several table in the DB. We are running Postgres 9.6.9 on Amazon RDS (db.m5.4xlarge instance) Our current DB consists of 500GB of data and indexes. Most of the rows in= table are consist of=C2=A0454,078,915 With the fresh DB with the restore of the DATA without any indexes Search= query performs relatively quick and most of the time its less than a sec= ond. But after 3 weeks of use of the DB it sudenly started to slowdown only fo= r this perticular query and it takes 20+ seconds to respond. If I do a re= store the DB again then it continues to work fine and the symptom pops ou= t after 3 weeks time. I am just suspecting is there any cache or index maxing out causes this i= ssue=3F Could you please guide me what can it be the root cause of this issue=3F Thank you, =46ahiz --5dec062c_5072367_168 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline <html xmlns=3D=22http://www.w3.org/1999/xhtml=22> <head> <title></title> </head> <body> <div name=3D=22messageBodySection=22> <div dir=3D=22auto=22>Hi team, <div dir=3D=22auto=22><br /></div> <div dir=3D=22auto=22>Could you please help me with this strange issue I = am facing in my current live server I am maintaining.</div> <div dir=3D=22auto=22><br /></div> <div dir=3D=22auto=22>There is a specific search query I am running to ge= t list of Documents and their metadata from several table in the DB.</div= > <div dir=3D=22auto=22>We are running Postgres 9.6.9 on Amazon RDS (<span = style=3D=22color: rgb(209, 210, 211); font-family: Monaco, Menlo, Consola= s, "Courier New", monospace; font-size: 12px; orphans: 2; white= -space: pre-wrap; widows: 2; background: rgba(var(--sk=5Fforeground=5Fmin= ,29,28,29),0.04);=22>db.m5.4xlarge instance)</span></div> <div dir=3D=22auto=22><br /></div> <div dir=3D=22auto=22>Our current DB consists of 500GB of data and indexe= s. Most of the rows in table are consist of&=23160;<span style=3D=22color= : rgb(209, 210, 211); font-family: Slack-Lato, appleLogo, sans-serif; fon= t-size: 15px; orphans: 2; white-space: pre-wrap; widows: 2; background-co= lor: rgb(26, 29, 33);=22>454,078,915</span></div> </div> </div> <div name=3D=22messageSignatureSection=22><br /> <div dir=3D=22auto=22>With the fresh DB with the restore of the DATA with= out any indexes Search query performs relatively quick and most of the ti= me its less than a second.&=23160;</div> <div dir=3D=22auto=22><br /></div> <div dir=3D=22auto=22>But after 3 weeks of use of the DB it sudenly start= ed to slowdown only for this perticular query and it takes 20+ seconds to= respond. If I do a restore the DB again then it continues to work fine a= nd the symptom pops out after 3 weeks time.&=23160;</div> <div dir=3D=22auto=22><br /></div> <div dir=3D=22auto=22>I am just suspecting is there any cache or index ma= xing out causes this issue=3F</div> <div dir=3D=22auto=22><br /></div> <div dir=3D=22auto=22>Could you please guide me what can it be the root c= ause of this issue=3F</div> <div dir=3D=22auto=22><br /></div> <div dir=3D=22auto=22><br /></div> <div dir=3D=22auto=22>Thank you,</div> <div dir=3D=22auto=22>=46ahiz</div> <div dir=3D=22auto=22><br /></div> </div> </body> </html> --5dec062c_5072367_168--