Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)

On Fri, Mar 24, 2017 at 3:58 AM, Pietro Pugni <pietro(dot)pugni(at)gmail(dot)com> wrote:
> Hi there,
> I’m running PostgreSQL 9.6.2 on Ubuntu 16.04.2 TLS (kernel
> 4.4.0-66-generic). Hardware is:
> - 2 x Intel Xeon E5-2690
> - 96GB RAM
> - Software mdadm RAID10 (6 x SSDs)
>
> Postgres is used in a sort of DWH application, so all the resources are
> assigned to it and the aim is to maximize the single transaction performance
> instead of balancing between multiple connections.
>
> The configuration variables I changed are the following ones:
>
> checkpoint_completion_target = 0.9
> data_directory = '/mnt/raid10/pg_data_9.6.2'
> default_statistics_target = 1000
> effective_cache_size = 72GB
> effective_io_concurrency = 1000
> listen_addresses = '127.0.0.1,192.168.2.90'
> maintenance_work_mem = 1GB
> max_connections=32
> random_page_cost=1.2
> seq_page_cost=1.0
> shared_buffers = 24GB
> work_mem = 512MB
>
>
> The kernel configuration in /etc/sysctl.conf is:
>
> # 24GB = (24*1024*1024*1024)
> kernel.shmmax = 25769803776
>
> # 6MB = (24GB/4096) dove 4096 e' uguale a "getconf PAGE_SIZE"
> kernel.shmall = 6291456
>
> kernel.sched_migration_cost_ns = 5000000
> kernel.sched_autogroup_enabled = 0
>
> vm.overcommit_memory = 2
> vm.overcommit_ratio = 90
> vm.swappiness = 4
> vm.zone_reclaim_mode = 0
> vm.dirty_ratio = 15
> vm.dirty_background_ratio = 3
> vm.nr_hugepages = 12657
> vm.min_free_kbytes=262144
>
> dev.raid.speed_limit_max=1000000
> dev.raid.speed_limit_min=1000000
>
>
> Huge pages are being used on this machine and Postgres allocates 24GB
> immediately after starting up, as set by vm.nr_hugepages = 12657.
> My concern is that it never uses more than 24GB. For example, I’m running 16
> queries that use a lot of CPU (they do time series expansion and some
> arithmetics). I estimate they will generate a maximum of 2.5 billions of
> rows. Those queries are running since 48 hours and don’t know when they will
> finish, but RAM never overpassed those 24GB (+ some system).
>
> Output from free -ht:
> total used free shared buff/cache
> available
> Mem: 94G 28G 46G 17M 19G
> 64G
> Swap: 15G 0B 15G
> Total: 109G 28G 61G

Looks normal to me. Note that the OS is caching 19G of data.
Postgresql is only going to allocate extra memory 512MB at a time for
big sorts. Any sort bigger than that will spill to disk. GIven that
top and vmstat seem to show you as being CPU bound I don't think
amount of memory postgresql is using is your problem.

You'd be better off to ask for help in optimizing your queries IMHO.