On Wed, 18 Dec 2019 17:53:26 +0800 "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: 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 ffff965ebabd1040 0 > 6573 6572 0x00000080Dec 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.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@xxxxxxxxxxx +1 888 359 3508
