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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Dec 18, 2019 at 4:53 AM James(王旭) <wangxu@xxxxxxxxx> 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: 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 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 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.

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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux