Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

feat(stac-db): Provide sensible defaults for pgSTAC db parameters #1

Merged
merged 1 commit into from
Nov 3, 2022
Merged

feat(stac-db): Provide sensible defaults for pgSTAC db parameters #1

merged 1 commit into from
Nov 3, 2022

Conversation

alukach
Copy link
Member

@alukach alukach commented Nov 2, 2022

What I am changing

We want to deploy pgSTAC databases with sensible defaults. These values should be overridable by users.

How I did it

I'm hoping that the code is self-evident. I attempted to apply the logic described in DS' DB tuning recommendations but had to take some liberties. For example, for a guideline such as:

In general max_connections * work_mem should be less than the setting for shared_buffers

I struggled turning this into a rule. I ended up just making the work_mem = shared_buffers / max_connections. I'm uncertain if that is an appropriate translation (this obviously results in a shared_buffers value equal max_connections * work_mem, not less).

Related Issues

closes developmentseed/asdi-pgstac#8

bitner
bitner reviewed Nov 2, 2022
View reviewed changes
lib/database/index.ts
const maxConnections = parameters?.maxConnections
? Number.parseInt(parameters.maxConnections)
: // https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Limits.html#RDS_Limits.MaxConnections
Math.min(Math.round((memory_in_kb * 1024) / 9531392), 5000);
Copy link

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

5000 as a min maybe super high if it is a server that is not expecting that much concurrency. Now I know, the next thing you are going to ask is "what's reasonable" and there really isn't a clear cut answer for that, you need to know your use case.

Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This formula is the default from RDS docs itself: LEAST({DBInstanceClassMemory/9531392}, 5000), I'm just recreating it here to use for other calculations

Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Also, note that 5000 is actually the max, we're taking whichever is less.

Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

If you're curious, here are what the default maxConnections are per instance size:
InstanceType Memory (MB) max_connections shared_buffers (KB) effective_cache_size (KB) work_mem (KB) maintenance_work_mem (KB) max_locks_per_transaction temp_buffers (KB) seq_page_cost random_page_cost
t2.nano, t3.nano, t3a.nano, t4g.nano 512 56 131072 393216 2340 32768 1024 131072 1 1.1
t2.micro, t3.micro, t3a.micro, t4g.micro 1024 113 262144 786432 2319 65536 1024 131072 1 1.1
c6g.medium, c6gd.medium, c6gn.medium, c7g.medium, t2.small, t3.small, t3a.small, t4g.small 2048 225 524288 1572864 2330 131072 1024 131072 1 1.1
c4.large 3840 422 983040 2949120 2329 245760 1024 131072 1 1.1
c5.large, c5a.large, c5ad.large, c5d.large, c6a.large, c6g.large, c6gd.large, c6gn.large, c6i.large, c6id.large, c7g.large, m6g.medium, m6gd.medium, t2.medium, t3.medium, t3a.medium, t4g.medium 4096 451 1048576 3145728 2325 262144 1024 131072 1 1.1
c5n.large 5376 591 1376256 4128768 2328 344064 1024 131072 1 1.1
is4gen.medium 6144 676 1572864 4718592 2326 393216 1024 131072 1 1.1
c4.xlarge 7680 845 1966080 5898240 2326 491520 1024 131072 1 1.1
c5.xlarge, c5a.xlarge, c5ad.xlarge, c5d.xlarge, c6a.xlarge, c6g.xlarge, c6gd.xlarge, c6gn.xlarge, c6i.xlarge, c6id.xlarge, c7g.xlarge, g5g.xlarge, im4gn.large, inf1.xlarge, m4.large, m5.large, m5a.large, m5ad.large, m5d.large, m5dn.large, m5n.large, m5zn.large, m6a.large, m6g.large, m6gd.large, m6i.large, m6id.large, r6g.medium, r6gd.medium, t2.large, t3.large, t3a.large, t4g.large 8192 901 2097152 6291456 2327 524288 1024 131072 1 1.1
c5n.xlarge 10752 1183 2752512 8257536 2326 688128 1024 131072 1 1.1
is4gen.large 12288 1352 3145728 9437184 2326 786432 1024 131072 1 1.1
c4.2xlarge 15360 1690 3932160 11796480 2326 983040 1024 131072 1 1.1
i3.large, r4.large 15616 1718 3997696 11993088 2326 999424 1024 131072 1 1.1
c5.2xlarge, c5a.2xlarge, c5ad.2xlarge, c5d.2xlarge, c6a.2xlarge, c6g.2xlarge, c6gd.2xlarge, c6gn.2xlarge, c6i.2xlarge, c6id.2xlarge, c7g.2xlarge, d3en.xlarge, g4ad.xlarge, g4dn.xlarge, g5.xlarge, g5g.2xlarge, i3en.large, i4i.large, im4gn.xlarge, inf1.2xlarge, m4.xlarge, m5.xlarge, m5a.xlarge, m5ad.xlarge, m5d.xlarge, m5dn.xlarge, m5n.xlarge, m5zn.xlarge, m6a.xlarge, m6g.xlarge, m6gd.xlarge, m6i.xlarge, m6id.xlarge, mac2.metal, r5.large, r5a.large, r5ad.large, r5b.large, r5d.large, r5dn.large, r5n.large, r6a.large, r6g.large, r6gd.large, r6i.large, r6id.large, t2.xlarge, t3.xlarge, t3a.xlarge, t4g.xlarge, x2gd.medium, z1d.large 16384 1802 4194304 12582912 2327 1048576 1024 131072 1 1.1
c5n.2xlarge 21504 2366 5505024 16515072 2326 1376256 1024 131072 1 1.1
is4gen.xlarge, vt1.3xlarge 24576 2704 6291456 18874368 2326 1572864 1024 131072 1 1.1
c4.4xlarge 30720 3380 7864320 23592960 2326 1966080 1024 131072 1 1.1
d2.xlarge, g3s.xlarge, i3.xlarge, r4.xlarge 31232 3436 7995392 23986176 2326 1998848 1024 131072 1 1.1
c5.4xlarge, c5a.4xlarge, c5ad.4xlarge, c5d.4xlarge, c6a.4xlarge, c6g.4xlarge, c6gd.4xlarge, c6gn.4xlarge, c6i.4xlarge, c6id.4xlarge, c7g.4xlarge, d3.xlarge, d3en.2xlarge, g4ad.2xlarge, g4dn.2xlarge, g5.2xlarge, g5g.4xlarge, h1.2xlarge, i3en.xlarge, i4i.xlarge, im4gn.2xlarge, m4.2xlarge, m5.2xlarge, m5a.2xlarge, m5ad.2xlarge, m5d.2xlarge, m5dn.2xlarge, m5n.2xlarge, m5zn.2xlarge, m6a.2xlarge, m6g.2xlarge, m6gd.2xlarge, m6i.2xlarge, m6id.2xlarge, mac1.metal, r5.xlarge, r5a.xlarge, r5ad.xlarge, r5b.xlarge, r5d.xlarge, r5dn.xlarge, r5n.xlarge, r6a.xlarge, r6g.xlarge, r6gd.xlarge, r6i.xlarge, r6id.xlarge, t2.2xlarge, t3.2xlarge, t3a.2xlarge, t4g.2xlarge, trn1.2xlarge, x2gd.large, z1d.xlarge 32768 3605 8388608 25165824 2326 2097152 1024 131072 1 1.1
c5n.4xlarge 43008 4731 11010048 33030144 2327 2752512 1024 131072 1 1.1
inf1.6xlarge, is4gen.2xlarge, m5zn.3xlarge, vt1.6xlarge 49152 5000 12582912 37748736 2516 3145728 1024 131072 1 1.1
c4.8xlarge 61440 5000 15728640 47185920 3145 3932160 1024 131072 1 1.1
d2.2xlarge, i3.2xlarge, p2.xlarge, p3.2xlarge, r4.2xlarge 62464 5000 15990784 47972352 3198 3997696 1024 131072 1 1.1
c5a.8xlarge, c5ad.8xlarge, c6a.8xlarge, c6g.8xlarge, c6gd.8xlarge, c6gn.8xlarge, c6i.8xlarge, c6id.8xlarge, c7g.8xlarge, d3.2xlarge, d3en.4xlarge, g4ad.4xlarge, g4dn.4xlarge, g5.4xlarge, g5g.8xlarge, h1.4xlarge, i3en.2xlarge, i4i.2xlarge, im4gn.4xlarge, m4.4xlarge, m5.4xlarge, m5a.4xlarge, m5ad.4xlarge, m5d.4xlarge, m5dn.4xlarge, m5n.4xlarge, m6a.4xlarge, m6g.4xlarge, m6gd.4xlarge, m6i.4xlarge, m6id.4xlarge, r5.2xlarge, r5a.2xlarge, r5ad.2xlarge, r5b.2xlarge, r5d.2xlarge, r5dn.2xlarge, r5n.2xlarge, r6a.2xlarge, r6g.2xlarge, r6gd.2xlarge, r6i.2xlarge, r6id.2xlarge, x2gd.xlarge, z1d.2xlarge 65536 5000 16777216 50331648 3355 4194304 1024 131072 1 1.1
c5.9xlarge, c5d.9xlarge 73728 5000 18874368 56623104 3774 4718592 1024 131072 1 1.1
c5.12xlarge, c5a.12xlarge, c5ad.12xlarge, c5d.12xlarge, c5n.9xlarge, c6a.12xlarge, c6g.12xlarge, c6gd.12xlarge, c6gn.12xlarge, c6i.12xlarge, c6id.12xlarge, c7g.12xlarge, d3en.6xlarge, i3en.3xlarge, is4gen.4xlarge, m5zn.6xlarge, z1d.3xlarge 98304 5000 25165824 75497472 5033 6291456 1024 131072 1 1.1
d2.4xlarge, f1.2xlarge, g3.4xlarge, i3.4xlarge, r4.4xlarge, x1e.xlarge 124928 5000 31981568 95944704 6396 7995392 1024 131072 1 1.1
c5a.16xlarge, c5ad.16xlarge, c6a.16xlarge, c6g.16xlarge, c6g.metal, c6gd.16xlarge, c6gd.metal, c6gn.16xlarge, c6i.16xlarge, c6id.16xlarge, c7g.16xlarge, d3.4xlarge, d3en.8xlarge, g4ad.8xlarge, g4dn.8xlarge, g5.8xlarge, g5g.16xlarge, g5g.metal, h1.8xlarge, i4i.4xlarge, im4gn.8xlarge, m5.8xlarge, m5a.8xlarge, m5ad.8xlarge, m5d.8xlarge, m5dn.8xlarge, m5n.8xlarge, m6a.8xlarge, m6g.8xlarge, m6gd.8xlarge, m6i.8xlarge, m6id.8xlarge, r5.4xlarge, r5a.4xlarge, r5ad.4xlarge, r5b.4xlarge, r5d.4xlarge, r5dn.4xlarge, r5n.4xlarge, r6a.4xlarge, r6g.4xlarge, r6gd.4xlarge, r6i.4xlarge, r6id.4xlarge, x2gd.2xlarge, x2iedn.xlarge 131072 5000 33554432 100663296 6710 8388608 1024 131072 1 1.1
c5.18xlarge, c5d.18xlarge 147456 5000 37748736 113246208 7549 9437184 1024 131072 1 1.1
m4.10xlarge 163840 5000 41943040 125829120 8388 10485760 1024 131072 1 1.1
c5.24xlarge, c5.metal, c5a.24xlarge, c5ad.24xlarge, c5d.24xlarge, c5d.metal, c5n.18xlarge, c5n.metal, c6a.24xlarge, c6i.24xlarge, c6id.24xlarge, d3en.12xlarge, g4dn.12xlarge, g5.12xlarge, i3en.6xlarge, inf1.24xlarge, is4gen.8xlarge, m5.12xlarge, m5a.12xlarge, m5ad.12xlarge, m5d.12xlarge, m5dn.12xlarge, m5n.12xlarge, m5zn.12xlarge, m5zn.metal, m6a.12xlarge, m6g.12xlarge, m6gd.12xlarge, m6i.12xlarge, m6id.12xlarge, vt1.24xlarge, z1d.6xlarge 196608 5000 50331648 150994944 10066 12582912 1024 131072 1 1.1
d2.8xlarge, f1.4xlarge, g3.8xlarge, i3.8xlarge, p3.8xlarge, r4.8xlarge, x1e.2xlarge 249856 5000 63963136 191889408 12792 15990784 1024 131072 1 1.1
c6a.32xlarge, c6i.32xlarge, c6i.metal, c6id.32xlarge, c6id.metal, d3.8xlarge, g4ad.16xlarge, g4dn.16xlarge, g5.16xlarge, h1.16xlarge, i4i.8xlarge, im4gn.16xlarge, m4.16xlarge, m5.16xlarge, m5a.16xlarge, m5ad.16xlarge, m5d.16xlarge, m5dn.16xlarge, m5n.16xlarge, m6a.16xlarge, m6g.16xlarge, m6g.metal, m6gd.16xlarge, m6gd.metal, m6i.16xlarge, m6id.16xlarge, r5.8xlarge, r5a.8xlarge, r5ad.8xlarge, r5b.8xlarge, r5d.8xlarge, r5dn.8xlarge, r5n.8xlarge, r6a.8xlarge, r6g.8xlarge, r6gd.8xlarge, r6i.8xlarge, r6id.8xlarge, x2gd.4xlarge, x2iedn.2xlarge, x2iezn.2xlarge 262144 5000 67108864 201326592 13421 16777216 1024 131072 1 1.1
c6a.48xlarge, c6a.metal, g4dn.metal, g5.24xlarge, i3en.12xlarge, m5.24xlarge, m5.metal, m5a.24xlarge, m5ad.24xlarge, m5d.24xlarge, m5d.metal, m5dn.24xlarge, m5dn.metal, m5n.24xlarge, m5n.metal, m6a.24xlarge, m6i.24xlarge, m6id.24xlarge, r5.12xlarge, r5a.12xlarge, r5ad.12xlarge, r5b.12xlarge, r5d.12xlarge, r5dn.12xlarge, r5n.12xlarge, r6a.12xlarge, r6g.12xlarge, r6gd.12xlarge, r6i.12xlarge, r6id.12xlarge, z1d.12xlarge, z1d.metal 393216 5000 100663296 301989888 20132 25165824 1024 131072 1 1.1
g3.16xlarge, i3.16xlarge, p2.8xlarge, p3.16xlarge, r4.16xlarge, x1e.4xlarge 499712 5000 127926272 383778816 25585 31981568 1024 131072 1 1.1
i3.metal, i4i.16xlarge, m6a.32xlarge, m6i.32xlarge, m6i.metal, m6id.32xlarge, m6id.metal, r5.16xlarge, r5a.16xlarge, r5ad.16xlarge, r5b.16xlarge, r5d.16xlarge, r5dn.16xlarge, r5n.16xlarge, r6a.16xlarge, r6g.16xlarge, r6g.metal, r6gd.16xlarge, r6gd.metal, r6i.16xlarge, r6id.16xlarge, trn1.32xlarge, x2gd.8xlarge, x2iedn.4xlarge, x2iezn.4xlarge 524288 5000 134217728 402653184 26843 33554432 1024 131072 1 1.1
p2.16xlarge 749568 5000 191889408 575668224 38377 47972352 1024 131072 1 1.1
dl1.24xlarge, g5.48xlarge, i3en.24xlarge, i3en.metal, m6a.48xlarge, m6a.metal, p3dn.24xlarge, r5.24xlarge, r5.metal, r5a.24xlarge, r5ad.24xlarge, r5b.24xlarge, r5b.metal, r5d.24xlarge, r5d.metal, r5dn.24xlarge, r5dn.metal, r5n.24xlarge, r5n.metal, r6a.24xlarge, r6i.24xlarge, r6id.24xlarge, x2gd.12xlarge, x2iezn.6xlarge 786432 5000 201326592 603979776 40265 50331648 1024 131072 1 1.1
f1.16xlarge, x1.16xlarge, x1e.8xlarge 999424 5000 255852544 767557632 51170 63963136 1024 131072 1 1.1
i4i.32xlarge, i4i.metal, r6a.32xlarge, r6i.32xlarge, r6i.metal, r6id.32xlarge, r6id.metal, x2gd.16xlarge, x2gd.metal, x2idn.16xlarge, x2iedn.8xlarge, x2iezn.8xlarge 1048576 5000 268435456 805306368 53687 67108864 1024 131072 1 1.1
p4d.24xlarge 1179648 5000 301989888 905969664 60397 75497472 1024 131072 1 1.1
r6a.48xlarge, r6a.metal, x2idn.24xlarge, x2iezn.12xlarge, x2iezn.metal 1572864 5000 402653184 1207959552 80530 100663296 1024 131072 1 1.1
x1.32xlarge, x1e.16xlarge 1998848 5000 511705088 1535115264 102341 127926272 1024 131072 1 1.1
x2idn.32xlarge, x2idn.metal, x2iedn.16xlarge 2097152 5000 536870912 1610612736 107374 134217728 1024 131072 1 1.1
u-3tb1.56xlarge, x2iedn.24xlarge 3145728 5000 805306368 2415919104 161061 201326592 1024 131072 1 1.1
x1e.32xlarge 3997696 5000 1023410176 3070230528 204682 255852544 1024 131072 1 1.1
x2iedn.32xlarge, x2iedn.metal 4194304 5000 1073741824 3221225472 214748 268435456 1024 131072 1 1.1
u-6tb1.112xlarge, u-6tb1.56xlarge 6291456 5000 1610612736 4831838208 322122 402653184 1024 131072 1 1.1
u-9tb1.112xlarge 9437184 5000 2415919104 7247757312 483183 603979776 1024 131072 1 1.1
u-12tb1.112xlarge 12582912 5000 3221225472 9663676416 644245 805306368 1024 131072 1 1.1

@bitner
Copy link

bitner commented Nov 2, 2022

work_mem = shared_buffers / max_connections is a totally reasonable place to start. If you have a number of parallel query plans or other things that end up using multiple workers / connection, you can still run into issues when you are nearing the max_connections, but you are probably fairly safe there.

I would make max_connections a required parameter. I fear that the minimum values that are used in the example you found from the RDS docs may end up being quite high. PGStac requires a lot more memory per connection than a "normal" fully indexed OLTP type query in Postgres (which is what that default is designed for). By having a max_connections count too high and following the work_mem formula above, you are going to end up with a really low value for work_mem and a number of queries may need to swap out to disk regularly. You really want your work_mem to be as high as is reasonable for the expected load and the only way to do that is by setting what type of load you want to be able to handle.

@alukach
Copy link
Member Author

alukach commented Nov 2, 2022

@bitner

work_mem = shared_buffers / max_connections is a totally reasonable place to start. If you have a number of parallel query plans or other things that end up using multiple workers / connection, you can still run into issues when you are nearing the max_connections, but you are probably fairly safe there.

Great, I appreciate the sanity check on that.

I would make max_connections a required parameter. I fear that the minimum values that are used in the example you found from the RDS docs may end up being quite high.

I am still unsure, I really want this module to provide "reasonable defaults". I worry that requiring a user to provide a value for max_connections could lead to them shooting themselves in the foot (e.g. they actually specify a number greater than the default because they're just guessing). Looking at the default max_connections values generated by our formula, what do you think about adding a fractional multiplier? Maybe 0.5 (ie Math.round(0.5 * Math.min((memory_in_kb * 1024 / 9531392), 5000)))?

@alukach alukach merged commit 11eb89e into main Nov 3, 2022
@alukach alukach deleted the feature/autotune-db branch November 3, 2022 03:52
github-actions bot pushed a commit that referenced this pull request Nov 3, 2022
@semantic-release-bot
chore(release): 2.2.0 [skip ci]
22cbbd3 
# [2.2.0](v2.1.0...v2.2.0) (2022-11-03)

### Features

* **stac-db:** Provide sensible defaults for pgSTAC db parameters ([#1](#1)) ([11eb89e](11eb89e))
@alukach alukach mentioned this pull request Sep 16, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@bitner bitner bitner left review comments

@sharkinsspatial sharkinsspatial Awaiting requested review from sharkinsspatial

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
2 participants
@alukach @bitner