Re: NUMA settings

[Laurenz Albe]

On Wed, 2020-04-29 at 08:54 +0200, Marc Rechté wrote:
> I am trying to figure out the recommended settings for a PG dedicated 
> machine regarding NUMA.
> 
> I assume that the shared buffers are using Huge Phages only. Please 
> correct if I am wrong:
> 
> 1) postgres is started with numactl --interleave=all, in order to spread 
> memory pages evenly on nodes.
> 2) wm.swappiness is left to the default 60 value, because Huge Pages 
> never swap, and we wish the idle backend to be swapped out if necessary.
> 3) vm.zone_reclaim_mode = 0. I am not sure it is the right choice.
> 4) kernel.numa_balancing = 1. Only if it is confirmed that it will not 
> affect postgres, because started with the interleave policy.

I am not the top expert on this, but as far as I can tell:

- Disabling NUMA is good if you want to run a single database cluster
  on the machine that should use all resources.

  If you want to run several clusters that share the resources, leaving
  NUMA support enabled might be the better thing to do.

- If you can, disable NUMA in the BIOS, on as low a level as possible.

- I think "kernel.numa_balancing" should be 0.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: The query plan get all columns but I'm using only one column.

[Michael Lewis]

It is generally a very bad idea to turn off autovacuum. When it is causing
problems, it is likely that it needs to run more often to keep up with the
work, rather than not run at all. Certainly if it is turned off, it would
be critical to have a regularly scheduled process to vacuum analyze all
tables.

>