Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1crLz7-0004vj-PV for pgsql-performance@arkaria.postgresql.org; Fri, 24 Mar 2017 09:58:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1crLz7-0006cu-3L for pgsql-performance@arkaria.postgresql.org; Fri, 24 Mar 2017 09:58:17 +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 1crLz5-0006Xn-GZ for pgsql-performance@postgresql.org; Fri, 24 Mar 2017 09:58:15 +0000 Received: from mail-wr0-x22c.google.com ([2a00:1450:400c:c0c::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1crLz2-0002Ml-26 for pgsql-performance@postgresql.org; Fri, 24 Mar 2017 09:58:14 +0000 Received: by mail-wr0-x22c.google.com with SMTP id u108so5891626wrb.3 for ; Fri, 24 Mar 2017 02:58:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:subject:message-id:date:to:mime-version; bh=GXw297k4INSC7PvCSlVjH4aIPvWWystjuxTgeUpMPjY=; b=dJgWe1Kf/G+ppJbvUkN2OgHuxG9kfF+B15CDSwv2GFzs4ijC7fD6EaOoEBM9huVZAX rMYiiPciTzYuzC0VQbYZyJz0Q1USn86kJDXEQwG1QBffN3+quAfNovW1uyiPYNl2cTfI vJtAYhYZbJMDr8lSNfDNQ9dumXODPdtLCwMdi2/uQnTtaaJ4Vo36vPdBNb+8/gIB8GY0 1UKT9CfqzYRKcom9lDisn5t05fnTFST44OPwLvrPHxL5CJGVgZhkiny5BKovV29CQA01 67ZX743T6BpQv1RweByWFAtF8w+HkXm9Sr0d4ZP9pdq7+gIVK2yV5x5BIWLIdyddu6xm V1jw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:subject:message-id:date:to:mime-version; bh=GXw297k4INSC7PvCSlVjH4aIPvWWystjuxTgeUpMPjY=; b=fIcvByckE1wQ7oqeonmeOpUmA1GQ8nDV+qOs0miKk/cc+IJ4MFsr+Pc+jNXIlpfmpr VMNWnJgLBegPAczVFgtzu5SKq0cSZwGaMBQJlx3cGY/AMBHQqTBjSk1LrrTbbDdKZ1Ws AtNZrgZR4ojp6PIAk0xLfqNv9ipBVhPxAvCwcfQe3Sg2KmGfxJ5T1blhh4UHfq68vPpK nqiSs2hVs3vPSodV/Aedu8WEd5sp9+jK30LIU5eVpHKeaWDlttvvPVmFIvOJZcsHvBwA Gh9cv1tt2KymeFUXQeMGEZ39L858fsPVY+1KOIhdXgX7O1URZDoMA9DueS/sdXs6u6u+ Vg6A== X-Gm-Message-State: AFeK/H2SNC5Uiqh7zWkqm3QD9Xq2bZ7FY8yGJzPei+gdKaYJvQyJHOeR1WQSBomffv+Csw== X-Received: by 10.223.177.207 with SMTP id r15mr7555389wra.127.1490349490047; Fri, 24 Mar 2017 02:58:10 -0700 (PDT) Received: from [192.168.2.5] (2-230-140-245.ip202.fastwebnet.it. [2.230.140.245]) by smtp.gmail.com with ESMTPSA id e16sm2207659wra.62.2017.03.24.02.58.08 for (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Fri, 24 Mar 2017 02:58:09 -0700 (PDT) From: Pietro Pugni Content-Type: multipart/alternative; boundary="Apple-Mail=_71F82495-1DAE-4505-A14F-11F96FE0F89A" Subject: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Message-Id: Date: Fri, 24 Mar 2017 10:58:08 +0100 To: "pgsql-performance@postgresql.org" Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\)) X-Mailer: Apple Mail (2.3124) X-Pg-Spam-Score: -2.0 (--) 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 --Apple-Mail=_71F82495-1DAE-4505-A14F-11F96FE0F89A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi there, I=E2=80=99m 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 =3D 0.9 data_directory =3D '/mnt/raid10/pg_data_9.6.2' default_statistics_target =3D 1000 effective_cache_size =3D 72GB effective_io_concurrency =3D 1000 listen_addresses =3D '127.0.0.1,192.168.2.90' maintenance_work_mem =3D 1GB max_connections=3D32 random_page_cost=3D1.2 seq_page_cost=3D1.0 shared_buffers =3D 24GB work_mem =3D 512MB The kernel configuration in /etc/sysctl.conf is: # 24GB =3D (24*1024*1024*1024) kernel.shmmax =3D 25769803776 # 6MB =3D (24GB/4096) dove 4096 e' uguale a "getconf PAGE_SIZE" kernel.shmall =3D 6291456 kernel.sched_migration_cost_ns =3D 5000000 kernel.sched_autogroup_enabled =3D 0 vm.overcommit_memory =3D 2 vm.overcommit_ratio =3D 90 vm.swappiness =3D 4 vm.zone_reclaim_mode =3D 0 vm.dirty_ratio =3D 15 vm.dirty_background_ratio =3D 3 vm.nr_hugepages =3D 12657 vm.min_free_kbytes=3D262144 dev.raid.speed_limit_max=3D1000000 dev.raid.speed_limit_min=3D1000000 Huge pages are being used on this machine and Postgres allocates 24GB = immediately after starting up, as set by vm.nr_hugepages =3D 12657. My concern is that it never uses more than 24GB. For example, I=E2=80=99m = 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=E2=80=99= t know when they will finish, but RAM never overpassed those 24GB (+ = some system).=20 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=20 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ = COMMAND = = =20 9686 postgres 20 0 24.918g 214236 12628 R 100.0 0.2 2872:38 = postgres = = =20 9687 postgres 20 0 24.918g 214212 12600 R 100.0 0.2 2872:27 = postgres = = =20 9688 postgres 20 0 25.391g 709936 12708 R 100.0 0.7 2872:40 = postgres = = =20 9691 postgres 20 0 24.918g 214516 12900 R 100.0 0.2 2865:23 = postgres = = =20 9697 postgres 20 0 24.918g 214284 12676 R 100.0 0.2 2866:05 = postgres = = =20 9698 postgres 20 0 24.922g 218608 12904 R 100.0 0.2 2872:31 = postgres = = =20 9699 postgres 20 0 24.918g 214512 12904 R 100.0 0.2 2865:32 = postgres = = =20 9702 postgres 20 0 24.922g 218332 12628 R 100.0 0.2 2865:24 = postgres = = =20 9704 postgres 20 0 24.918g 214512 12904 R 100.0 0.2 2872:50 = postgres = = =20 9710 postgres 20 0 24.918g 212364 12904 R 100.0 0.2 2865:38 = postgres = = =20 9681 postgres 20 0 24.918g 212300 12596 R 99.7 0.2 2865:18 = postgres = = =20 9682 postgres 20 0 24.918g 212108 12656 R 99.7 0.2 2872:34 = postgres = = =20 9684 postgres 20 0 24.918g 212612 12908 R 99.7 0.2 2872:24 = postgres = = =20 9685 postgres 20 0 24.918g 214208 12600 R 99.7 0.2 2872:47 = postgres = = =20 9709 postgres 20 0 24.918g 214284 12672 R 99.7 0.2 2866:03 = postgres = = =20 9693 postgres 20 0 24.918g 214300 12688 R 99.3 0.2 2865:59 = postgres = = =20 9063 postgres 20 0 24.722g 14812 12956 S 0.3 0.0 0:07.36 = postgres = = =20 9068 postgres 20 0 24.722g 6380 4232 S 0.3 0.0 0:02.15 = postgres = = =20 9065 postgres 20 0 24.727g 10368 3516 S 0.0 0.0 0:04.24 = postgres = = =20 9066 postgres 20 0 24.722g 4100 2248 S 0.0 0.0 0:06.04 = postgres = = =20 9067 postgres 20 0 24.722g 4100 2248 S 0.0 0.0 0:01.37 = postgres = = =20 9069 postgres 20 0 161740 4596 2312 S 0.0 0.0 0:04.48 = postgres =20 What=E2=80=99s wrong with this? There isn=E2=80=99t something wrong in = RAM usage? Thank you all Pietro=20= --Apple-Mail=_71F82495-1DAE-4505-A14F-11F96FE0F89A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi there,
I=E2=80=99m 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 =3D 0.9
= data_directory =3D '/mnt/raid10/pg_data_9.6.2'
= default_statistics_target =3D 1000
= effective_cache_size =3D 72GB
= effective_io_concurrency =3D 1000
= listen_addresses =3D '127.0.0.1,192.168.2.90'
= maintenance_work_mem =3D 1GB
= max_connections=3D32
= random_page_cost=3D1.2
= seq_page_cost=3D1.0
= shared_buffers =3D 24GB
work_mem = =3D 512MB


The kernel configuration = in /etc/sysctl.conf is:

# 24GB =3D = (24*1024*1024*1024)
= kernel.shmmax =3D 25769803776

# 6MB =3D (24GB/4096) dove 4096 = e' uguale a "getconf PAGE_SIZE"
= kernel.shmall =3D 6291456

kernel.sched_migration_cost_ns =3D = 5000000
kernel.sched_autogroup_enabled =3D = 0

= vm.overcommit_memory =3D 2
= vm.overcommit_ratio =3D 90
= vm.swappiness =3D 4
= vm.zone_reclaim_mode =3D 0
= vm.dirty_ratio =3D 15
= vm.dirty_background_ratio =3D 3
= vm.nr_hugepages =3D 12657
= vm.min_free_kbytes=3D262144

= dev.raid.speed_limit_max=3D1000000
= dev.raid.speed_limit_min=3D1000000


Huge pages are being used on this machine = and Postgres allocates 24GB immediately after starting up, as set = by vm.nr_hugepages =3D 12657.
My = concern is that it never uses more than 24GB. For example, I=E2=80=99m = 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=E2=80=99= 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=E2=80=99s wrong with this? There = isn=E2=80=99t something wrong in RAM usage?

Thank you all
 =  Pietro 
= --Apple-Mail=_71F82495-1DAE-4505-A14F-11F96FE0F89A--