pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres not using all RAM (Huge Page activated on a 96GB RAM system)
7+ messages / 7 participants
[nested] [flat]
* Postgres not using all RAM (Huge Page activated on a 96GB RAM system)
@ 2017-03-24 09:58 Pietro Pugni <pietro.pugni@gmail.com>
2017-03-24 14:00 ` Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Andrew Kerber <andrew.kerber@gmail.com>
2017-03-24 19:47 ` Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Scott Marlowe <scott.marlowe@gmail.com>
2017-03-24 20:16 ` Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Álvaro Hernández Tortosa <aht@8kdata.com>
2017-03-25 01:56 ` Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) trafdev <trafdev@mail.ru>
0 siblings, 4 replies; 7+ messages in thread
From: Pietro Pugni @ 2017-03-24 09:58 UTC (permalink / raw)
To: pgsql-performance
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 =
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)
2017-03-24 09:58 Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Pietro Pugni <pietro.pugni@gmail.com>
@ 2017-03-24 14:00 ` Andrew Kerber <andrew.kerber@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Andrew Kerber @ 2017-03-24 14:00 UTC (permalink / raw)
To: Pietro Pugni <pietro.pugni@gmail.com>; +Cc: pgsql-performance
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.pugni@gmail.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.'
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)
2017-03-24 09:58 Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Pietro Pugni <pietro.pugni@gmail.com>
@ 2017-03-24 19:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-03-27 13:27 ` Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Merlin Moncure <mmoncure@gmail.com>
3 siblings, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2017-03-24 19:47 UTC (permalink / raw)
To: Pietro Pugni <pietro.pugni@gmail.com>; +Cc: pgsql-performance
On Fri, Mar 24, 2017 at 3:58 AM, Pietro Pugni <pietro.pugni@gmail.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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)
2017-03-24 09:58 Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Pietro Pugni <pietro.pugni@gmail.com>
2017-03-24 19:47 ` Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Scott Marlowe <scott.marlowe@gmail.com>
@ 2017-03-27 13:27 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Merlin Moncure @ 2017-03-27 13:27 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Pietro Pugni <pietro.pugni@gmail.com>; pgsql-performance
On Fri, Mar 24, 2017 at 2:47 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Mar 24, 2017 at 3:58 AM, Pietro Pugni <pietro.pugni@gmail.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)
2017-03-24 09:58 Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Pietro Pugni <pietro.pugni@gmail.com>
@ 2017-03-24 20:16 ` Álvaro Hernández Tortosa <aht@8kdata.com>
2017-03-25 01:27 ` Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Shaun Thomas <bonesmoses@gmail.com>
3 siblings, 1 reply; 7+ messages in thread
From: Álvaro Hernández Tortosa @ 2017-03-24 20:16 UTC (permalink / raw)
To: Pietro Pugni <pietro.pugni@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)
2017-03-24 09:58 Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Pietro Pugni <pietro.pugni@gmail.com>
2017-03-24 20:16 ` Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Álvaro Hernández Tortosa <aht@8kdata.com>
@ 2017-03-25 01:27 ` Shaun Thomas <bonesmoses@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Shaun Thomas @ 2017-03-25 01:27 UTC (permalink / raw)
To: Álvaro Hernández Tortosa <aht@8kdata.com>; +Cc: Pietro Pugni <pietro.pugni@gmail.com>; pgsql-performance
> What’s wrong with this? There isn’t something wrong in RAM usage?
Nope, nothing wrong with RAM usage at all from what you've presented
here. Please consider the cut-and-paste you included a bit closer. All
of your active threads are utilizing 100% CPU, and are therefore
CPU-bound. If there were some kind of IO issue due to disk fetching,
your CPU utilization would be much lower. From the looks of things,
your threads are either operating on fully cached or otherwise
available pages, or are generating their own such that it doesn't
matter.
The real question is this: what are your queries/processes doing?
Because if the query plan is using a giant nested loop, or you are
relying on a stored procedure that's in a tight and non-optimized loop
of some kind, you're going to be consuming a lot of clock cycles with
diminishing benefits. If you're not making use of set theory within a
database, for example, you might be getting 100x less throughput than
you could otherwise attain. If it's not proprietary in some way, or
you can obfuscate it into a test case, we can probably help then. As
it stands, there isn't enough to go on.
--
Shaun Thomas
bonesmoses@gmail.com
http://bonesmoses.org/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)
2017-03-24 09:58 Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Pietro Pugni <pietro.pugni@gmail.com>
@ 2017-03-25 01:56 ` trafdev <trafdev@mail.ru>
3 siblings, 0 replies; 7+ messages in thread
From: trafdev @ 2017-03-25 01:56 UTC (permalink / raw)
To: pgsql-performance
Looks like Postgres will never "use" (visually) more than shared_buffers
size of memory.
Change it to 48GB, and in your "top" output you will see how memory
usage bumped up to this new limit.
But it's just a "visual" change, I doubt you'll get any benefits from it.
On 03/24/17 02: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. 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-03-27 13:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-03-24 09:58 Postgres not using all RAM (Huge Page activated on a 96GB RAM system) Pietro Pugni <pietro.pugni@gmail.com>
2017-03-24 14:00 ` Andrew Kerber <andrew.kerber@gmail.com>
2017-03-24 19:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-03-27 13:27 ` Merlin Moncure <mmoncure@gmail.com>
2017-03-24 20:16 ` Álvaro Hernández Tortosa <aht@8kdata.com>
2017-03-25 01:27 ` Shaun Thomas <bonesmoses@gmail.com>
2017-03-25 01:56 ` trafdev <trafdev@mail.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox