Re: How to prevent POSTGRES killing linux system from accepting too much inserts?

On Wed, 18 Dec 2019 17:53:26 +0800
"James(王旭)" <wangxu(at)gu360(dot)com> wrote:

> Hello,I encountered into this kernel message, and I cannot login into
> the Linux system anymore:
>
> Dec 17 23:01:50 hq-pg kernel: sh (6563): drop_caches: 1Dec 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
> &gt; /proc/sys/kernel/hung_task_timeout_secs" disables this
> message.Dec 17 23:02:30 hq-pg kernel: sync&nbsp; &nbsp; &nbsp; &nbsp;
> &nbsp; &nbsp; D ffff965ebabd1040&nbsp; &nbsp; &nbsp;0&nbsp;
> 6573&nbsp; &nbsp;6572 0x00000080Dec 17 23:02:30 hq-pg kernel: Call
> Trace:Dec 17 23:02:30 hq-pg kernel: [<ffffffffa48760a0&gt;] ?
> 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 cache first then kernel called
> sync.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 than that speed.For me, POSTGRES just waiting is
> much better than current behavior. Any help will be much appreciated.

There isn't one magic-bullet solution for this. It may be that you can
tune Linux, PG, or the filesystem to handle the load more
gracefully; or that you just need more hardware. Streaming inserts might
be better batched and handled via synchronous ETL than pushed in at
random, at that point you can control the resources.

One approach might be tighter timeouts on the server or client, which
will leave the queries failing when the queue gets too high. That
frees up resources on the server, at the obvious expense of having
transactions roll back. On the other hand, you can end up with
timeouts so tight that you end up thrashing, which doesn't help the
problem.

Catch from this end is that without more informaton on the system
you are dealing with there isn't any quick-and-dirty fix.

I'd suggest looking over:

<https://duckduckgo.com/?q=linux+postgres+tuning&t=ffab&ia=web>

for suggestions and seeing which ones work or don't. If you have
more specific questions on the parameters or how to evaluate the
stats PG is keeping feel free to ask them here, but you will need
to be specific as to the stats and situation in which they were
acquired so that people have enough context to give you a reasonable
answer.

--
Steven Lembark 3646 Flora Place
Workhorse Computing St. Louis, MO 63110
lembark(at)wrkhors(dot)com +1 888 359 3508