Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1crVdX-0005zg-JE for pgsql-performance@arkaria.postgresql.org; Fri, 24 Mar 2017 20:16:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1crVdX-0005BI-5w for pgsql-performance@arkaria.postgresql.org; Fri, 24 Mar 2017 20:16:39 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1crVdW-0005Ah-2P for pgsql-performance@postgresql.org; Fri, 24 Mar 2017 20:16:38 +0000 Received: from mail-wr0-x233.google.com ([2a00:1450:400c:c0c::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1crVdS-0006GK-Fq for pgsql-performance@postgresql.org; Fri, 24 Mar 2017 20:16:36 +0000 Received: by mail-wr0-x233.google.com with SMTP id y90so511091wrb.0 for ; Fri, 24 Mar 2017 13:16:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=8kdata-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to; bh=ZDfRhBo7UKaBNJ6IDqaXjZxIQzrlQjj273lpinOPV7o=; b=xbd8TF/EMLyyPUBiHo9zQ7rSoM5TA3Dh0yxMzvsU1XaDknimyBTIprTVCndQUoHn4f 01gPUsiBAk0J5OvUsyvPlghNvQKU2N8uth6u9WVPfMa6Txytle0P97aFHY/L+vdxJiNL bTHM2p24c+HLZPcGCV7/zExmVyPB2dUzFFwqz+QjfHdAtzZUhhXibe7viVCScmdM6YSm N1NKQYnsQCFylBbWo2IiO3FtWNvd2b+YAtxBZ1ME80mml1slsHNkuRHT9DpyCs5peZcC DxSQm/QSaStoXcyjCX/3yADPmnZCjuw6pJ+f9msWZs5V+X+j+8GlS/I22sPxmFlSCust NnkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to; bh=ZDfRhBo7UKaBNJ6IDqaXjZxIQzrlQjj273lpinOPV7o=; b=NR4uvUgFCXOPO4FSc+8d641FyOUvgEpt/P1Jvg+S5WTyb9ygSIWBQeVpMvq15Bm6wF olZcn4xMNWfIb8cbhYyrFEekrI6+e0wZd5C5Qm0696AQAQvyCpRh7UMHJwI7dQy72Gw4 m5rARwEp6CVdt9tiULtUG6fRzJhj2BMmDFwyuUHUsX4gtNXTA5jXOCl61NbLwc/EUwBY YAAijNjCrBP1GJqYuuunbr/qguPjQCCXO1VxYzT3iRry++U93/w/x20AChbFCV5jIKzB ndHbUn2lPK5izcUbxesOgt1YvPKdZ4fjH0h3VXq9+KXGnq88QWSEwkX9LCyonIZm8YfL FrFA== X-Gm-Message-State: AFeK/H18EkGxM5a5d7uo8CG8EpT5Buv7Sfbo0MQXutJoEShR0KM2fdBpxtyZLuSd4H3WOA== X-Received: by 10.223.175.84 with SMTP id z78mr10710703wrc.84.1490386591772; Fri, 24 Mar 2017 13:16:31 -0700 (PDT) Received: from [192.168.1.130] (228.58.223.87.dynamic.jazztel.es. [87.223.58.228]) by smtp.gmail.com with ESMTPSA id g78sm4158816wrd.11.2017.03.24.13.16.29 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 24 Mar 2017 13:16:30 -0700 (PDT) Subject: Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) To: Pietro Pugni , "pgsql-performance@postgresql.org" References: From: =?UTF-8?Q?=c3=81lvaro_Hern=c3=a1ndez_Tortosa?= Message-ID: <9f461e29-3518-ed36-7f5d-cd140244dae7@8kdata.com> Date: Fri, 24 Mar 2017 21:16:28 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A3105D295F1B173A6B9B8F0C" X-Pg-Spam-Score: -1.1 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------A3105D295F1B173A6B9B8F0C Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------A3105D295F1B173A6B9B8F0C Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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 

--------------A3105D295F1B173A6B9B8F0C--