Nginx/postgres not using all cores

nginxphp-fpmpostgresql

I have an Nginx+Postgres server with 8 cpu cores. I noticed that under load CPU is used only up to ~60%.

I looked at top and here is a screenshot:

It turns out that only 5 processes are actually run in parallel. What could cause this capping?

I looked at postgres configuration. I didn't notice any settings would cap it off, the settings were not really limiting, for example:

max_connections = 100

Then I checked Nginx configuration. These settings seems relevant (but does not seem to be capping in this case):

worker_processes auto;
worker_connections 768;

In the php settings I looked at this setting and it seems suspicious:

pm.max_children

The pm is set to dynamic. Could this be the limiting factor (I can't blindly test various setting changes and restart this server for experimenting)? If so, how should I choose the value? Set it equal to core count or maybe more?

Best Answer

NGINX isn't breaking a sweat here managing what looks to be 5 connections to PHP-FPM. PHP-FPM dynamically spun up workers for each connection. Everything looks good here.

PostgreSQL is using 99% CPU and 1% memory. There’s likely a faster way to execute queries if you give it more resources.

Before you tune anything, familiarize yourself with how PostgreSQL uses CPU.
Tune your configuration for your hardware and expected load (connections/clients). You can use pgtune to get some ballpark numbers.
If your workload involves UPDATE and DELETE you (or the auto-vacuum daemon) need to be VACUUMing your database to remove dead tuples.
If you can afford some downtime and you have spare disk space, I'd recommend a VACUUM FULL ANALYZE before investing too much time.
If you are on a pre-9.0 version of PostgreSQL you may need to REINDEX after VACUUM.
Since you've inherited this system, it's a good idea to see the types of queries being run and what's eating up all of that CPU. pgbadger will normalize and group your queries together. You may be able to hand tune a few queries or indexes.
There are many different types of indexes that you may have not seen before. You can do partial indexes, indexes on expressions, BRIN, GIN, etc. Heroku has a summary on using indexes efficiently.
You also definitely want to make sure you’re not having locking issues.
Consider reviewing the mailing list archives or posting to the list. There is an excellent community on IRC as well.

Correct way in new versions of nginx

Turn out my first answer to this question was correct at certain time, but it turned into another pitfall - to stay up to date please check Taxing rewrite pitfalls

I have been corrected by many SE users, so the credit goes to them, but more importantly, here is the correct code:

server {
       listen         80;
       server_name    my.domain.com;
       return         301 https://$server_name$request_uri;
}

server {
       listen         443 ssl;
       server_name    my.domain.com;
       # add Strict-Transport-Security to prevent man in the middle attacks
       add_header Strict-Transport-Security "max-age=31536000" always; 

       [....]
}

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Best Answer

Related Solutions

NGINX Redirect – How to Rewrite All HTTP Requests to HTTPS While Maintaining Sub-Domain

Correct way in new versions of nginx

PostgreSQL – Default Superuser Username/Password After New Install

Related Topic