Max Performance of Nginx, PHP & PostgreSQL Chain — Again aka Part #2

This blog post is the second part in a series on performance engineering (PE) research, check this link for the first one. We look into max performance of Nginx, PHP & PostgreSQL chain — again!

Another take of Nano Banana on the subject.

A bit of Refresher on Max Performance of Nginx, PHP & PostgreSQL Chain

See “Setup for Max Performance of Nginx, PHP & PostgreSQL Chain Research” section of the first part for details but here’s quick recap:

  • The goal was to find out performance limits of a particular request processing pipeline (nginx + PHP-FPM + PostgreSQL) in synthetic env
  • Hardware was a laptop with Intel Core i7-7700HQ + 32 GB RAM, all inside Docker

We covered serving purely static HTML hosted by the backend (no PHP executed) in the first part. In this part we’ll look into following scenarios:

  • Serving simple PHP page with a bit of code but no DB access
  • Serving PHP page which involves querying the DB

Pure PHP Page at 5k RPS

This time I made requests rate ramp up in 1 minute up to 5k. Same setup regarding VUs — 1k pre-allocated just in case. PHP page in question was something along these lines:

<?php
    $randValue = rand(1,7);
?>
   
<?= "<html lang=\"ru\">
<head>
    <title>FOO</title>
    <meta charset=\"utf-8\">
</head>
<body>
    <p>HEYA! {$randValue}</p>
</body>
</html>" ?>

Surprisingly this just worked! No breaking point, no dropped iterations. Closer to 5k RPS frontend’s CPU usage climbs up to 70-90%, backend’s — up to 270-300%. Overall laptop got all threads busy up to 90% because Docker componentry was getting busy too closer to peak load.

PHP-FPM pool was set up to be dynamic, pm.max_children = 48, backend Nginx talks to PHP-FPM over UNIX socket.

Usage of keepalive / persistent connections between frontend and backend (keepalive 1024) had the following effects:

  • frontend CPU usage slowed down getting around just 50% closer to the peak load
  • overall iteration latency improved by 40-50% among all percentiles (avg, median, p90, p95)

What makes sense since frontend Nginx stops wasting resources on connection creation and connection reuse decrease overhead resulting in lower latency. The amount won this way was kind of surprising, though.

What’s interesting in this case is how frontend to backend Nginx connectivity didn’t end with sockets exhaustion? Despite some non-trivial effort I didn’t manage to get to the bottom of it. In case file is returned by try_files Nginx directive (as in case of static HTML) TIME_WAIT sockets pile up at both sides. In case file is returned by fastcgi_pass (that’s simple PHP page) TIME_WAIT sockets don’t pile up on frontend side. And backend doesn’t have any issue with lots of closed & time wait sockets.

PHP Page with DB Roundtrip

For the DB check I set up super simple table like this:

create table public.test_table
(
    id   integer generated always as identity
        constraint test_table_pk
            primary key,
    data varchar(10)
);

And prefilled it with just 3 records. PHP endpoint gets record ID via query param and fetches it from the Postgres. All should be immediately cached and returned from memory on subsequent queries.

Without ANY persistent connections this set up starts to drown as soon as RPS gets to 300+. ~500% CPU load by backend, ~200% by postgres, frontend’s load is negligent, all laptop’s cores top out at 99.9%.

Obviously lack of connection pooling on PHP side got us to such detrimental results, but why? I used bpftrace to measure latency distributions of:

  • Postgres establishing connections — this was overhead for every iteration
  • Postgres creating backends for new connections — for every new connection master is forked to handle it

For instance, measuring connection establishing was easy as running this on host:

sudo bpftrace -e 'k:do_accept /comm == "postgres"/ { @ts[tid] = nsecs; } kr:do_accept /@ts[tid]/ {@ = hist(nsecs - @ts[tid]); delete(@ts[tid]); }'

95%+ of do_accept calls fell into “under 16 us” range with isolated outliers up to 1-2 ms. That means around 63k RPS should be possible. Similar latency distribution was for ret_from_fork but for kernel_clone it took much longer — from 256us to 1ms. So it was just around 2k RPS possible in my setup on essential syscalls alone.

Obviously Postgres did something else and php-fpm did a lot of stuff to fully occupy 5 CPU cores. Since before overload VUs utilized were close to 1 handling was quick enough for processing being basically sequential. That means requests were handled under 3.33 ms and around 20% of that time was spent on kernel_clone alone. Just in case I checked socket usage on backend side but it obviously didn’t get high enough on such low RPS.

Changing PHP code from

$pdo = new PDO($dsn, $username, $password);

to

$pdo = new PDO($dsn, $username, $password, array(
    PDO::ATTR_PERSISTENT => true
));

alone moves tipping point WAY up closer to 4k+ RPS. CPU usage gets up to 300% for backend, 100% for postgres and closer to 100% for frontend. That likely means frontend hits the same connection exhaustion we saw multiple times already.

With keepalive at frontend side it’s CPU usage stays in line with the simple PHP page — under 50%. We arrive to breaking point upon maxing out CPU usage on laptop cores a bit closer to 5k RPS but never actually getting there without overloading.

Conclusion

Hope you enjoyed the ride with me! Looking at the two articles of the series I feel a bit disappointed — the most important conclusion is “just use persistent connections, b*tch!” =) Nevertheless it’s a huge difference between two things. First is just knowing from elsewhere that something is a best practice. Second is experiencing first-hand how and why this practice is born.

PE keeps me engaged because it drives my mastery of computers further, so I may get to sharing something interesting on the topic in the future. Stay tuned!