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

From: James(王旭) <wangxu(at)gu360(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to prevent POSTGRES killing linux system from accepting too much inserts?
Date: 2019-12-18 09:53:26
Message-ID: tencent_59F48E3719B4AB494460DF9D@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general pgsql-performance

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.

Thanks,James

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2019-12-18 09:56:45 Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
Previous Message Roman Cervenak 2019-12-18 08:49:22 Re: Memory leak (possibly connected to postgis) leading to server crash

Browse pgsql-general by date

  From Date Subject
Next Message Steven Winfield 2019-12-18 10:42:49 RE: Row locks, SKIP LOCKED, and transactions
Previous Message Karsten Hilbert 2019-12-18 09:37:35 Re: Encoding/collation question

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2019-12-18 12:17:10 Re: Consecutive Query Executions with Increasing Execution Time
Previous Message Piotr Włodarczyk 2019-12-18 09:16:11 Re: shared memory size during upgrade pgsql with partitions (max_locks_per_transaction)