Graph Indexer “Store error: database unavailable”

Recently I started to see this error appear randomly when hitting out Graph Indexer directly, which in turn would have been impacting any requests coming through the Graph decentralised Gateway service.

Looking at the query_node_0 logs I could see that the outages coincided with the following logs

Dec 12 12:01:06.000 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool
Dec 12 12:01:07.925 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool
Dec 12 12:02:43.493 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool
Dec 12 12:04:13.113 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool
Dec 12 12:05:42.932 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool
Dec 12 12:07:13.324 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool
Dec 12 12:08:43.035 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool
Dec 12 12:10:13.168 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool
Dec 12 12:11:43.020 ERRO Connection checkout timed out, wait_ms: 5000, pool: main, shard: primary, component: ConnectionPool

Thankfull Marc-André from Ellipfra and Payne from StakeSquid were able to narrow it down to something that Marc saw from 0.28.

While I’m still running 0.27, the fix is to increase the pool size in the graphnode config, and also increase the max connections in the postgres to accomodate the changes as well.

I’m running on https://github.com/StakeSquid/graphprotocol-mainnet-docker/ as our indexer, so the following needed updating.

The pool_size has been updated from 10 to 50 by going to

nano graph-node-configes/query_node_0_config.toml

[store]
[store.primary]
connection = "postgresql://${postgres_user}:${postgres_pass}@${postgres_host}:5432/${postgres_db}?host=/var/run/postgresql"
pool_size = 50

[chains]
ingestor = "index_node_1"

[deployment]
[[deployment.rule]]
indexers = [ "index_node_0", "index_node_1" ]

[general]
query = "query_node_0"

I also updated the nano index_node_1_config.toml and nano index_node_0_config.toml pool sizes to 50 as well to maintain consistency between the files (not sure if that has any impact).

Save the file, and then open docker-compose.yml and find where your configuration files are for postgres. In my instance they were at

/var/lib/docker/volumes/graphprotocol-mainnet-docker_postgres_data/_data/postgresql.conf and /var/lib/docker/volumes/graphprotocol-mainnet-docker_postgres2_data/_data/postgresql.conf

I updated the following lines

max_connections = 500

shared_buffers = 1GB                    # min 128kB

Then run

bash start --force-recreate indexer-agent postgres postgres2