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
