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

On 24/03/17 10: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.

Hi Pietro.

Well, your shared_buffers is 24G, so it is expected that it won't
use more (much more, the rest being other parameters). The rest if
effective_cache_size, which is what the VFS is expected to be caching.

Have you configured parallel query
(max_parallel_workers_per_gather) to allow for faster queries? It may
work well on your scenario.

Regards,

Álvaro

--

Álvaro Hernández Tortosa

-----------
<8K>data

> 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