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

Looks like Postgres will never "use" (visually) more than shared_buffers
size of memory.

Change it to 48GB, and in your "top" output you will see how memory
usage bumped up to this new limit.

But it's just a "visual" change, I doubt you'll get any benefits from it.

On 03/24/17 02:58, Pietro Pugni 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
>
> Output from /vmstat -S M/:
> procs -----------memory---------- ---swap-- -----io---- -system--
> ------cpu-----
> r b swpd free buff cache si so bi bo in cs us sy
> id wa st
> 17 0 0 47308 197 19684 0 0 4 12 3 8 96 0
> 3 0 0
>
>
> Output from /top -U postgres/:
> top - 10:54:19 up 2 days, 1:37, 1 user, load average: 16.00, 16.00,
> 16.00
> Tasks: 347 total, 17 running, 330 sleeping, 0 stopped, 0 zombie
> %Cpu(s):100.0 us, 0.0 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0
> si, 0.0 st
> KiB Mem : 98847584 total, 48442364 free, 30046352 used, 20358872
> buff/cache
> KiB Swap: 15825916 total, 15825916 free, 0 used. 67547664 avail
> Mem
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 9686 postgres 20 0 24.918g 214236 12628 R 100.0 0.2 2872:38
> postgres
> 9687 postgres 20 0 24.918g 214212 12600 R 100.0 0.2 2872:27
> postgres
> 9688 postgres 20 0 25.391g 709936 12708 R 100.0 0.7 2872:40
> postgres
> 9691 postgres 20 0 24.918g 214516 12900 R 100.0 0.2 2865:23
> postgres
> 9697 postgres 20 0 24.918g 214284 12676 R 100.0 0.2 2866:05
> postgres
> 9698 postgres 20 0 24.922g 218608 12904 R 100.0 0.2 2872:31
> postgres
> 9699 postgres 20 0 24.918g 214512 12904 R 100.0 0.2 2865:32
> postgres
> 9702 postgres 20 0 24.922g 218332 12628 R 100.0 0.2 2865:24
> postgres
> 9704 postgres 20 0 24.918g 214512 12904 R 100.0 0.2 2872:50
> postgres
> 9710 postgres 20 0 24.918g 212364 12904 R 100.0 0.2 2865:38
> postgres
> 9681 postgres 20 0 24.918g 212300 12596 R 99.7 0.2 2865:18
> postgres
> 9682 postgres 20 0 24.918g 212108 12656 R 99.7 0.2 2872:34
> postgres
> 9684 postgres 20 0 24.918g 212612 12908 R 99.7 0.2 2872:24
> postgres
> 9685 postgres 20 0 24.918g 214208 12600 R 99.7 0.2 2872:47
> postgres
> 9709 postgres 20 0 24.918g 214284 12672 R 99.7 0.2 2866:03
> postgres
> 9693 postgres 20 0 24.918g 214300 12688 R 99.3 0.2 2865:59
> postgres
> 9063 postgres 20 0 24.722g 14812 12956 S 0.3 0.0 0:07.36
> postgres
> 9068 postgres 20 0 24.722g 6380 4232 S 0.3 0.0 0:02.15
> postgres
> 9065 postgres 20 0 24.727g 10368 3516 S 0.0 0.0 0:04.24
> postgres
> 9066 postgres 20 0 24.722g 4100 2248 S 0.0 0.0 0:06.04
> postgres
> 9067 postgres 20 0 24.722g 4100 2248 S 0.0 0.0 0:01.37
> postgres
> 9069 postgres 20 0 161740 4596 2312 S 0.0 0.0 0:04.48
> postgres
>
> What’s wrong with this? There isn’t something wrong in RAM usage?
>
> Thank you all
> Pietro