5

What amount of physical memory is recommended for a dedicated PGPool machine running connection pooling & load balancing, but no query cache?

I see num_init_children(96) * max_pool(2) * number_of_backends(2) = 384 lines in SHOW pool_pools; and the modal average seems to be about 99M per PID, with a couple of 1G outliers

# top for 20 pgpool processes
$ top -p $(pgrep pgpool | head -20 | tr "\\n" "," | sed 's/,$//')

Tasks:  20 total,   0 running,  20 sleeping,   0 stopped,   0 zombie
%Cpu(s):  3.1 us,  4.0 sy,  0.0 ni, 92.2 id,  0.0 wa,  0.0 hi,  0.7 si,  0.0 st
KiB Mem :  1784080 total,    22068 free,  1629960 used,   132052 buff/cache
KiB Swap:  4194300 total,   437328 free,  3756972 used.    71276 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND     
15407 root      20   0   97828    872    708 S   0.3  0.0   5:52.95 pgpool      
 8076 root      20   0  101664   2800   1708 S   0.0  0.2   0:06.51 pgpool      
 9597 root      20   0  101656   4508   1264 S   0.0  0.3   0:18.85 pgpool      
13603 root      20   0  149936  35548    984 S   0.0  2.0   1:13.56 pgpool      
13634 root      20   0  151848  39364    984 S   0.0  2.2   1:00.25 pgpool      
13677 root      20   0  149424  37812   1180 S   0.0  2.1   0:28.03 pgpool      
13680 root      20   0  153416  34948   1180 S   0.0  2.0   0:32.97 pgpool      
15397 root      20   0   97820    828    668 S   0.0  0.0   0:33.05 pgpool      
15399 root      20   0   97884    240    132 S   0.0  0.0   1:41.48 pgpool      
15402 root      20   0   93636     72      0 S   0.0  0.0   0:07.12 pgpool      
15405 root      20   0   93636    280    172 S   0.0  0.0   0:42.39 pgpool      
17121 root      20   0  101676   2016   1648 S   0.0  0.1   0:03.39 pgpool      
17206 root      20   0   97824     72      0 S   0.0  0.0   0:00.00 pgpool      
17207 root      20   0   97820    164     56 S   0.0  0.0   0:00.27 pgpool      
21348 root      20   0 3871428 1.090g   1536 S   0.0 64.1 429:48.53 pgpool      
21917 root      20   0  102672   2832   1696 S   0.0  0.2   0:49.46 pgpool      
22117 root      20   0  101692   2868   1752 S   0.0  0.2   0:16.57 pgpool      
22436 root      20   0  101692   4644   3464 S   0.0  0.3   0:31.12 pgpool      
23037 root      20   0  101692   3776   1780 S   0.0  0.2   0:20.52 pgpool      
23142 root      20   0  101664    980    936 S   0.0  0.1   0:08.60 pgpool      
3
  • 1
    Surprised this question isn't answered anywhere. Starting a bounty on it. Jan 18, 2018 at 21:21
  • Which exact version of pgpool is/was this? Jan 23, 2018 at 19:16
  • can't say exactly anymore because of upgrades since, but it was just prior to pgpool-II version 3.6.5 (subaruboshi)
    – gregn
    Jan 24, 2018 at 7:20

2 Answers 2

2

It's not documented in the official docs (as of now - Feb 2018), but PgPool's memory footprint is quite heavy.

In my recent testing PgPool II version 3.6 needs as much as 140 MB RAM per child process. (the number of child processes is defined in num_init_children). This is private process memory - not shared.

This means approximately 8GB for each 50 clients.

Compared to PostgreSQL, this is 5-10 times more (PostgreSQL can easily handle 250 sessions on 8 GB RAM). Plus, PostgreSQL uses shared buffer cache which is much more cost effective.

2
  • Can you give an example of this? I am running docker containers with 500 init_children in each one. I ran 10 threads querying a simple select from a table in each thread looping as fast as possible. Each container has a 500M memory limit. Never hit an OOM error. I am curious if my test overlooked something.
    – OneLiner
    Oct 31, 2018 at 20:14
  • @OneLiner sorry I don't have this setup nearby... but 10 sessions is too little to observe the issue. If you try more concurrent sessions (close to the limit) and inspect process memory usage, you may see what I saw.
    – filiprem
    Nov 1, 2018 at 8:46
1

It's worth to take a read to the official documentation about resource requirements. It won't cover all the cases but it gives you an idea.

Basically there are two types of memory usage you should take into account: Shared Memory and Process Memory.

To give a rough idea they suggest to calculate the shared usage memory with:

  • Shared memory requirement (in bytes) = num_init_children * max_pool * 17408

For the process memory use this formula:

  • Process memory requirement in total (in mega bytes) = num_init_children * 5

Example for shared memory with default values (num_init_children = 32 and max_pool = 4)

  • Shared Memory: 32 * 4 * 17408 = 2228224 bytes = 2.1 MB.
  • Process Memory: 32 * 5 = 160 MB
3
  • Please add the relevant parts that are answering the question to your answer. Links-only answers can become obsolete. In a case like this you might want to consider adding minimal information as a comment. Thanks and welcome to DBA.SE
    – John K. N.
    Oct 14, 2020 at 11:18
  • done, thanks for the feedback
    – Daniele
    Oct 15, 2020 at 12:54
  • The docs do not say if the process memory must be again multiplied with num_init_children, so is it fair to assume 5 MB per child process? In this case the total memory will be 162.1 MB, quite low compared to what we read. Oct 11, 2021 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.