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

As I read this, you have 24G of hugepages, and hugepages enabled for
postgres. Can postgres use both standard pages and hugepages at the same
time? Seems unlikely to me.

On Fri, Mar 24, 2017 at 4: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
>
> 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
>

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'