Mastodon performance tuning / postgres + pgpool

Mastodon

The problem

Recently, my Mastodon instance started flapping - it was happening more and more often. I started debugging the thing, and found out, that there are problems with pgpool - it was timing out when running heartbeats:

Liveness probe failed: pgpool INFO  ==> Checking pgpool health...
psql: timeout expired

As it is a Kubernetes setup, and this heartbeat is a livenessProbe, when it fails - the pod is restarted, causing all applications to fail when connecting to the database. BRUH.

Env setup

My Mastodon instance is a Kubernetes deployment done using upstream helm chart which I edited a bit to fit my usecase. I have disabled using Postgres subchart and deployed it separately using Bitnami postgres-ha helm chart.

I've git 3 postgres instances in clustered mode (using repmgr for managing replication). Loadbalancing and connection pooling is done via pgpool.

Digging into the problem

The liveness probe is a k8s special kind of check, which tells whether the service is working fine. In this particular case, it is configured to run a bash script /opt/bitnami/scripts/pgpool/healthcheck.sh. This script was timing out, so I tried to run it manually to confirm, there is a problem. It was randomly timing out when connecting to the postgres via pgpool (which stands in front of the database backends).

I had a quick suspicion, that the root cause might be the max_connection reached in Postgres backend servers. I verified this, but servers were performing good at that time:

postgres=# select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from
(select count(*) used from pg_stat_activity) t1,
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
(select setting::int max_conn from pg_settings where name=$$max_connections$$) t3;

 max_conn | used | res_for_super | res_for_normal
----------+------+---------------+----------------
      100 |   40 |             3 |             57
(1 row)

It was similar on rest of Postgres nodes, so "no luck" this time. It was something different, than an easy problem of max connections reached.

However this made me thinking, that there is this pgpool acting as a loadbalancer and connection pooler, standing in front of all postgres instances. As I deployed it with default values, I decided to take a look into details here.

I found this Github issue which was basically describing my problem. The main fix describe was:

Defined initial child process and the connection lifetime.

Actually, the mostsuggested fix was to increase the default num_init_children value (32) to something more:

numInitChildren: 64

How to get a proper value for num_init_children?

So actually, there is this great document on this subject Relationship_between_max_pool,_num_init_children,_and_max_connections.

The formula to understand here is:

max_pool*num_init_children <= (max_connections - superuser_reserved_connections)

And now, to understand this correctly:

max_pool parameter configures how many connections to cache per child. So if num_init_children is configured to 100, and max_pool is configured to 3, then pgpool can potentially open 300 (=3*100) connections to the backend database.

Especially this one:

A child process opens a new backend connection only if the requested [user,database] pair is not already in the cache. So if the application uses only one user to connect to only one database, say [pguser1,pgdb1], then each child will continue to reuse the first connection and will never open a second connection, so in effect pgpool will open no more than 100 backend connections even though max_pool is set to 3.

So just to summarize: num_init_children can be set to a number of max_connections - superuser_reserved_connections (no matter how many postgres workers you have). And we can set safely max_pool to 1 because Mastodon operates only on one database. So according to paragraphs mentioned above, the formula turns into:

num_init_children <= (max_connections - superuser_reserved_connections)

So e.g. in my case I set:

# pgpool
max_pool=1
num_init_children=90
# postgres
max_connections=100
superuser_reserved_connections=3

And this still gives me around 7 connections to be used by any other applications (e.g. monitoring etc).

Another performance considerations

  • Tuning livenessprobe: imo increasing timeouts on livenessProbes is not a good solution - it just tries to omit the underlying problem, which will be still there.
  • When deploying on k8s, set reserved_connections to 1 (default: 0). More on this here
  • pgpool heartbeat script timeouts might be also about low entropy level on the pgpool container; see this for details - this was not my case.
  • Running performance test is always a good idea (to confirm pgpool / max_connections settings): pgbench -c 64 -j 4 -t 1000 -f queries.sql
  • Verify LB distribution:
  • pgbench -U <username> -c 10 -T 10 -S
  • psql -U <username> -W -c "show pool_nodes"
  • Look at select_cnt of show pool_nodes and draw your conclusions

Interesting URLs on this subject

Tuning Mastodon / performance notes

This is one of a series of notes I created during one of performance tuning sessions done on my own Mastodon instance.

I'm planning to write a broad article about this subject, but for now, just a tiny note about on of the things I needed to work on.

Comments