Tuning Replication

Performance of streaming replication had been bothering me for some time. I'd noticed that the CPU utilisation was extremely high, so that the WALSender process was guzzling CPU even when nothing was happening.

When we think we have a clue about something and we were right we call that "intuition" and it sounds all mystical and clever. When we are wrong about something we call those "preconceptions". Strange difference of wording. Anyway, I had an idea that turned out to be a preconception. I started by looking for a smoking gun: one part of the system that would turn out to be using more CPU than another. There was no smoking gun, so that trail was a deadend. So next idea was just to look at the bgwriter code to see if there were any clear differences.

Anyway, the real culprit was something trivial. WALSender was supposed to sleep every 200ms by default. What it was actually doing was sleeping for 200 microseconds and then seeing if there was anything to send. So all it took to improve performance was to multiply by 1000 for the call to pg_usleep(). If only tuning databases was ever that simple!

That leaves me to say that the streaming replication now has a clean bill of health from an overall performance perspective. As far as I can see, YMMV, caveat emptor etc..

3.14159265358979

I'm told today is Pi day, in celebration of our friendly transcendental constant and because today is the 14th March, which is written as 3.14 in some locales....

So I thought I'd celebrate with a look at Postgres' mathematical musculature.

postgres=# select pi();
pi
------------------
3.14159265358979

Cool! That matches on all the digits, so we're rocking. So what datatype is this?

postgres=# select pg_typeof(pi());
pg_typeof
------------------
double precision

and as the manual says, we support at least 15 digits for this datatype. Even better. So let's flex those long dormant trigonometry muscles:

postgres=# select sin(90);
sin
-------------------
0.893996663600558

Oh no! Surely sin() of 90 degrees is 1.0? Perhaps the default is radians and I just forgot. Of course, doh! The manual doesn't actually say, which seems like a flaw. Here comes a doc patch. So lets supply radians instead.

postgres=# select sin(radians(90));
sin
-----
1

Phew! That works. Now lets try some advanced stuff. There are 2*pi() radians in a circle, so pi() radians is half way round. So the sin() of that should be 0, and the cos() should be 1.

postgres=# select sin(pi()), cos(pi());
sin | cos
----------------------+-----
1.22464679914735e-16 | -1

Hmmm. That is somewhat strange, but I guess that pi is transcendental so any representation with a fixed number of digits will always be slightly wrong by exactly that number of digits. That makes sense, but I guess I was expecting sin(pi()) to return 0.

Just to put this in perspective, in comparison to the diameter of the earth that is a precision of about one millionth of a millimetre. So that is very accurate for most applications.

Let's quickly check "e", another well known transcendental. We don't provide a function for e as we do for pi, but its easy to calculate.

postgres=# select exp(1);
exp
------------------
2.71828182845905

That looks good. So lets play some games with that also.

postgres=# select ln(exp(1));
ln
----
1

Thank goodness for that. All good. I notice in the docs that exp() takes either a double precision or a numeric input, so which one did we just try there? Let's see

postgres=# select ln(exp(1::float));
ln
----
1

and lets also try

postgres=# select ln(exp(1)::numeric);
ln
--------------------
1.0000000000000018

Hmmm. Looks like Postgres is being irrational. Or should that be insufficiently irrational? It's been a long day. I can't wait for "sqrt(2) day".