Re: How to prevent POSTGRES killing linux system from accepting too much inserts?
Jeff Janes <[email protected]> Wed, 18 Dec 2019 14:09:25 -0500
| Newsgroups | gmane.comp.db.postgresql.performance,gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <CAMkU=1zJznOf7mEMoGOcR1-7WMKPSq9WBtCqJg=xjhFpt60Nsg@mail.gmail.com> |
--0000000000009eaef50599ff2f81 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Dec 18, 2019 at 4:53 AM James(=E7=8E=8B=E6=97=AD) <[email protected]= > wrote: > Hello, >> >> I encountered into this kernel message, and I cannot login into the Linu= x >> system anymore: > > >> >> Dec 17 23:01:50 hq-pg kernel: sh (6563): drop_caches: 1 >> >> Dec 17 23:02:30 hq-pg kernel: INFO: task sync:6573 blocked for more than >>> 120 seconds. >> >> Dec 17 23:02:30 hq-pg kernel: "echo 0 > >>> /proc/sys/kernel/hung_task_timeout_secs" disables this message. >> >> Dec 17 23:02:30 hq-pg kernel: sync D ffff965ebabd1040 0 >>> 6573 6572 0x00000080 >> >> Dec 17 23:02:30 hq-pg kernel: Call Trace: >> >> Dec 17 23:02:30 hq-pg kernel: [<ffffffffa48760a0>] ? >>> generic_write_sync+0x70/0x70 >> >> >> After some google I guess it's the problem that IO speed is low, while >> the insert requests are coming too much quickly.So PG put these into cac= he >> first then kernel called sync > > Could you expand on what you found in the googling, with links? I've never seen these in my kernel log, and I don't know what they mean other than the obvious that it is something to do with IO. Also, what kernel and file system are you using? > . > > I know I can queue the requests, so that POSTGRES will not accept these >> requests which will result in an increase in system cache. > > But is there any way I can tell POSTGRES, that you can only handle 20000 >> records per second, or 4M per second, please don't accept inserts more t= han >> that speed. > > For me, POSTGRES just waiting is much better than current behavior. > > I don't believe there is a setting from within PostgreSQL to do this. There was a proposal for a throttle on WAL generation back in February, but with no recent discussion or (visible) progress: https://www.postgresql.org/message-id/flat/2B42AB02-03FC-406B-B92B-18DED2D8= D491%40anarazel.de#b63131617e84d3a0ac29da956e6b8c5f I think the real answer here to get a better IO system, or maybe a better kernel. Otherwise, once you find a painful workaround for one symptom you will just smack into another one. Cheers, Jeff > --0000000000009eaef50599ff2f81 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div dir=3D"ltr">On Wed, Dec 18, 2019 at 4:53 AM James(=E7= =8E=8B=E6=97=AD) <<a href=3D"mailto:[email protected]">[email protected]</= a>> wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmai= l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20= 4,204);padding-left:1ex"><u></u><div><div><div class=3D"gmail_quote">Hello,= <blockquote class=3D"gmail_quote" style=3D"color:rgb(0,0,0);margin:0px 0px = 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I encoun= tered into this kernel message, and I cannot login into the Linux system an= ymore:</blockquote><blockquote class=3D"gmail_quote" style=3D"color:rgb(0,0= ,0);margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding= -left:1ex"><br></blockquote><blockquote class=3D"gmail_quote" style=3D"colo= r:rgb(0,0,0);margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204= );padding-left:1ex"><br></blockquote><blockquote class=3D"gmail_quote" styl= e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin= g-left:1ex"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0= .8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Dec 17 23:01:= 50 hq-pg kernel: sh (6563): drop_caches: 1</blockquote><blockquote class=3D= "gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2= 04,204,204);padding-left:1ex">Dec 17 23:02:30 hq-pg kernel: INFO: task sync= :6573 blocked for more than 120 seconds.</blockquote><blockquote class=3D"g= mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204= ,204,204);padding-left:1ex">Dec 17 23:02:30 hq-pg kernel: "echo 0 >= /proc/sys/kernel/hung_task_timeout_secs" disables this message.</bloc= kquote><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;= border-left:1px solid rgb(204,204,204);padding-left:1ex">Dec 17 23:02:30 hq= -pg kernel: sync=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 D ffff965ebabd104= 0=C2=A0 =C2=A0 =C2=A00=C2=A0 6573=C2=A0 =C2=A06572 0x00000080</blockquote><= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">Dec 17 23:02:30 hq-pg kern= el: Call Trace:</blockquote><blockquote class=3D"gmail_quote" style=3D"marg= in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e= x">Dec 17 23:02:30 hq-pg kernel: [<ffffffffa48760a0>] ? generic_write= _sync+0x70/0x70</blockquote><br></blockquote><blockquote class=3D"gmail_quo= te" style=3D"border-left:1px solid rgb(204,204,204);color:rgb(0,0,0);margin= :0px 0px 0px 0.8ex;padding-left:1ex">After some google I guess it's the= problem that IO speed is low, while the insert requests are coming too muc= h quickly.So PG put these into cache first then kernel called sync</blockqu= ote></div></div></div></blockquote><div><br></div><div>Could you expand on = what you found in the googling, with links?=C2=A0 I've never seen these= in my kernel log, and I don't know what they mean other than the obvio= us that it is something to do with IO.=C2=A0 Also, what kernel and file sys= tem are you using?</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" = style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa= dding-left:1ex"><div><div><div class=3D"gmail_quote"><blockquote class=3D"g= mail_quote" style=3D"border-left:1px solid rgb(204,204,204);color:rgb(0,0,0= );margin:0px 0px 0px 0.8ex;padding-left:1ex">.</blockquote><blockquote clas= s=3D"gmail_quote" style=3D"border-left:1px solid rgb(204,204,204);color:rgb= (0,0,0);margin:0px 0px 0px 0.8ex;padding-left:1ex">I know I can queue the r= equests, so that POSTGRES will not accept these requests which will result = in an increase in system cache.</blockquote><blockquote class=3D"gmail_quot= e" style=3D"border-left:1px solid rgb(204,204,204);color:rgb(0,0,0);margin:= 0px 0px 0px 0.8ex;padding-left:1ex">But is there any way I can tell POSTGRE= S, that you can only handle 20000 records per second, or 4M per second, ple= ase don't accept inserts more than that speed.</blockquote><blockquote = class=3D"gmail_quote" style=3D"border-left:1px solid rgb(204,204,204);color= :rgb(0,0,0);margin:0px 0px 0px 0.8ex;padding-left:1ex">For me, POSTGRES jus= t waiting is much better than current behavior.</blockquote></div></div></d= iv></blockquote><div><br></div><div>I don't believe there is a setting = from within PostgreSQL to do this.</div><div><br></div><div>There was a pro= posal for a throttle on WAL generation back in February, but with no recent= discussion or (visible) progress:</div><div><br></div><div><a href=3D"http= s://www.postgresql.org/message-id/flat/2B42AB02-03FC-406B-B92B-18DED2D8D491= %40anarazel.de#b63131617e84d3a0ac29da956e6b8c5f">https://www.postgresql.org= /message-id/flat/2B42AB02-03FC-406B-B92B-18DED2D8D491%40anarazel.de#b631316= 17e84d3a0ac29da956e6b8c5f</a>=C2=A0=C2=A0</div><div><br></div><div>I think = the real answer here to get a better IO system, or maybe a better kernel.= =C2=A0 Otherwise, once you find a painful workaround for one symptom you wi= ll just smack into another one.</div><div><br></div><div>Cheers,</div><div>= <br></div><div>Jeff</div><blockquote class=3D"gmail_quote" style=3D"margin:= 0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">= <u></u></blockquote></div></div> --0000000000009eaef50599ff2f81--