[ZK Stack] - Postgres: sorry, too many clients already

[sachanayush47]

Team or Project

No response

Module Affected

• System contracts (era-system-contracts)
• Smart contracts (era-contracts)
• zkEVM (era-zk_evm)
• Server (zksync-era)
• Prover (era-boojum)
• Fee withdrawer (era-fee-withdrawer)
• Block Explorer (clock-explorer)
• Portal - Wallet + Bridge (dapp-portal)
• Other (please specify below)

Rust Version

1.82.0

Operating System

• Ubuntu
• MacOS
• Windows
• Other (Please specify below)

Issue Description

We have been using block explorer from the last 30 days and suddenly we started getting this error
Nov 29 05:26:50 validium-testnet start.sh[3032140]: [API] Error 29/11/2024 05:26:50.079 [ExceptionsHandler] sorry, too many clients already - {"stack":["error: sorry, too many clients already\n at Parser.parseErrorMessage (/home/zksync/block-explorer/node_modules/pg-protocol/dist/parser.js:287:98)\n at Parser.handlePacket (/home/zksync/block-explorer/node_modules/pg-protocol/dist/parser.js:126:29)\n at Parser.parse (/home/zksync/block-explorer/node_modules/pg-protocol/dist/parser.js:39:38)\n at Socket.<anonymous> (/home/zksync/block-explorer/node_modules/pg-protocol/dist/index.js:11:42)\n at Socket.emit (node:events:519:28)\n at addChunk (node:internal/streams/readable:559:12)\n at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)\n at Readable.push (node:internal/streams/readable:390:5)\n at TCP.onStreamRead (node:internal/stream_base_commons:191:23)"]} +109ms

To fix this, we tried to restart postgres container but the fix only lasts 3 hour and then error starts comes.

Expected Behavior

Block explorer should run without any issues.

Current Behavior

Postgres maximum connection limit has been exceeded. It happens if in the code the postgres connection is not being flushed after committing.

Repository Link (if applicable)

https://github.com/matter-labs/block-explorer

Additional Details

No response

Prior Research

• Yes, I searched for similar issues and did not find any matching ones.

[gleenai[bot]]

The error message "sorry, too many clients already" indicates that your PostgreSQL database has reached its maximum connection limit. This typically occurs when connections to the database are not properly closed or recycled. Here are a few steps you can take to address this issue:

1. Increase the Maximum Connections:

   • You can increase the max_connections setting in your PostgreSQL configuration file (postgresql.conf). This will allow more simultaneous connections.

2. Connection Pooling:

   • Implement connection pooling if not already in use. Tools like PgBouncer can help manage and reuse database connections efficiently.

3. Optimize Application Code:

   • Ensure that your application properly closes database connections after use. Check the block explorer's code for any potential leaks where connections might not be closed or returned to the pool.

4. Monitor and Log:

   • Monitor your database connections and set up logging to identify when and why the maximum limit is reached. This can help in pinpointing specific areas in your application that may be causing the issue.

For more detailed troubleshooting and optimization, you might want to consult the PostgreSQL documentation or consider reaching out to a database administrator.

For further discussions and support, you can visit the ZKsync developers discussions on Github.

Your feedback helps us serve you better. If this fully addresses your question, please give it a thumbs up. If it doesn't, please give it a thumbs down. This will inform the community and team so we can improve our support.

[dimazhornyk]

Hey! Thanks for pointing this out!
We will look into this and verify whether we do keep some unnecessary db connections

On your side, could you please make sure the max_connections in your Postgres config is set to a reasonable value?

[dimazhornyk]

Also, if you would be able to get the values of thedb_connection_pool_size metric - that would be helpful as well

[sachanayush47]

Hi,
Postgres max_connections and worker pool size both are set to 100. I think changing worker pool size to 50 can fix the issue. Let me try this.

[dimazhornyk]

Yes, having the same values for pool_size and max_connections is quite likely to cause such errors. Please let me know if reducing pool_size helped

I also checked the code and couldn't find the connections left idle anywhere. I'd assume that it is not the case unless your solution with reducing pool_size doesn't work

[sachanayush47]

Hi, Postgres max_connections and worker pool size both are set to 100. I think changing worker pool size to 50 can fix the issue. Let me try this.

It worked