Connection reset by peer when dabase server cpu usage reaches 100%

[ShitalPatil-GSLab]

Our requirement is to execute thousands of queries per second on PostGres. Currently we are trying few hundred queries per second. We have database server with fairly high configuration (32 GB, 16 core).
ulimit set to 100000, database/sql max open connection config set to 30000 and max idle to 5000
Queries execute well till certain point but after some time cpu usage reaches 100%. and "connection reset by peer" error occurs. This in turn leads in application panic.

After some short interval when cpu usage starts gradually dropping from 100% . One approach we wanted to take was retry queries for some interval so that queries at least get chanceto get executed when cpu usage drops. Here this article they say default library mechanism is to retry 10 times and logic is hard coded https://www.vividcortex.com/blog/2015/01/19/gos-connection-pool-retries-and-timeouts/.

Could you help with how can I go about retrying queries by specifying certain retry time interval? Or is there any other way to resolve this problem?

[johto]

Queries execute well till certain point but after some time cpu usage reaches 100%. and "connection reset by peer" error occurs.

What does postgres think happened to the connection? You should see something in postgres logs.

[sj14]

First of all, thanks for this nice library! I'm using it to write a small benchmarking tool and have the same issue. Postgres is running in docker on the same machine on Linux. While docker logs show errors when a key constraint fails, there are no logs when this particular problem happens. On client side I get:

read tcp 127.0.0.1:36396->127.0.0.1:5432: read: connection reset by peer

steps to reproduce:

docker run --name dbbench-postgres -p 5432:5432 -d postgres

install dbbench v0.2.0 or ...

go get -u github.com/sj14/dbbench/cmd
go install github.com/sj14/dbbench/cmd

run the benchmark:

dbbench -type postgres  -user postgres -pass example -iter 100000

After ~40 seconds the error occurs.

[sj14]

On the other hand, it works with CockroachDB which also uses this Postgres driver.

docker run --name dbbench-cockroach -d -p 26257:26257 -p 8080:8080 cockroachdb/cockroach:latest start --insecure
dbbench -type cockroach -iter 100000

Maybe it's an issue with Postgres.

[sj14]

At least for me it seems to be a docker issue. It works when I run it using the package from the ubuntu repository. Same for MySQL.

[zuoRambo]

I meet the problem "read tcp 172.xx.xx.xx:39288->10.yy.yy.yy:5432: read: connection reset by peer", but the database server's cpu do not reach 100%,even not 60%.

[Lekensteyn]

Those seeing write: connection reset by peer should have most cases addressed by #1013 (>= v1.9.0).

read: connection reset by peer is a distinct error case with another reproducer in #835. I think that applications are supposed to be prepared for it and reconnect themselves since the database driver cannot know for sure whether it is safe to continue.