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

[Osahon Oduware]

THE TRUTH CANNOT BE HIDDEN
**Explosion in my car.
https://www.docdroid.net/s11XHOS/the-truth-cannot-be-hidden.pdf

On Wed, 18 Dec 2019, 10:54 James(王旭),  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: 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: syncD 965ebabd1040 0
>>> 6573   6572 0x0080
>>
>> Dec 17 23:02:30 hq-pg kernel: Call Trace:
>>
>> Dec 17 23:02:30 hq-pg kernel: [] ?
>>> 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 2
>> 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.
>
>
>>
>> Thanks,
>
> James
>
>
>>

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

[Jeff Janes]

On Wed, Dec 18, 2019 at 4:53 AM James(王旭)  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: 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: syncD 965ebabd1040 0
>>> 6573   6572 0x0080
>>
>> Dec 17 23:02:30 hq-pg kernel: Call Trace:
>>
>> Dec 17 23:02:30 hq-pg kernel: [] ?
>>> 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
>
>
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 2
>> 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.
>
>
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-18DED2D8D491%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

>

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

[Merlin Moncure]

On Wed, Dec 18, 2019 at 3:53 AM James(王旭)  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: 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: syncD 965ebabd1040 0  
>>> 6573   6572 0x0080
>>>
>>> Dec 17 23:02:30 hq-pg kernel: Call Trace:
>>>
>>> Dec 17 23:02:30 hq-pg kernel: [] ? 
>>> 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 2 
>> 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.

This is more a problem with the o/s than with postgres itself.

synchronous_commit is one influential parameter that can possibly help
mitigate the issue with some safety tradeoffs (read the docs).   For
linux, one possible place to look is tuning dirty_background_ratio and
related parameters.  The idea is you want the o/s to be more
aggressive about syncing to reduce the impact of i/o storm; basically
you are trading off some burst performance for consistency of
performance.  Another place to look is checkpoint behavior.   Do some
searches, there is tons of information about this on the net.

merlin

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

[Steven Lembark]

On Wed, 18 Dec 2019 17:53:26 +0800
"James(王旭)"  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
> > /proc/sys/kernel/hung_task_timeout_secs" disables this
> message.Dec 17 23:02:30 hq-pg kernel: sync       
>     D 965ebabd1040     0 
> 6573   6572 0x0080Dec 17 23:02:30 hq-pg kernel: Call
> Trace:Dec 17 23:02:30 hq-pg kernel: [ 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 2 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:



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 Lembark3646 Flora Place
Workhorse ComputingSt. Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508

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

[James(王旭)]

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 > /proc/sys/kernel/hung_task_timeout_secs" disables 
this message.Dec 17 23:02:30 hq-pg kernel: sync        
    D 965ebabd1040     0  6573  
 6572 0x0080Dec 17 23:02:30 hq-pg kernel: Call Trace:Dec 17 23:02:30 
hq-pg kernel: [