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

On Fri, Mar 24, 2017 at 2:47 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> 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.

+1 this. Absent evidence, there is no reason to believe the memory is
needed. Memory is not magic pixie dust that makes queries go faster;
good data structure choices and algorithms remain the most important
determiners of query performance.

merlin