configuring the STAC API for more concurrent requests

[hrodmn]

I have been using eoAPI to serve my organization's STAC API for several months and everything has been working pretty well! However, when we have tried running workflows that make more than ~8 concurrent requests to the STAC API endpoint, the service fails.

Here is the python script that I am using to test the concurrent requests:

import multiprocessing as mp

import click
import numpy as np
import pystac_client
import tqdm


XMIN = -96
YMIN = 45
WIDTH = 0.5

# eoapi STAC endpoint
ENDPOINT = "https://..."

# some collection-id
COLLECTION_ID = "my-collection"


def get_items(bbox):
    """Get STAC items"""
    catalog = pystac_client.Client.open(ENDPOINT)

    return catalog.search(bbox=bbox, collections=[COLLECTION_ID]).item_collection()


@click.command()
@click.option("--cores", default=mp.cpu_count())
def run(cores):
    """test concurrent STAC queries"""

    # make a set of bounding boxes for the queries
    bboxes = []
    for x in np.arange(XMIN, XMIN + 4, step=WIDTH):
        for y in np.arange(YMIN, YMIN + 3, step=WIDTH):
            bboxes.append((x, y, x + WIDTH, y + WIDTH))

    with mp.Pool(processes=cores) as pool:
        res = []
        with tqdm.tqdm(total=len(bboxes)) as pbar:
            for result in pool.imap(get_items, bboxes):
                pbar.update(1)
                res.append(result)


if __name__ == "__main__":
    run()

This runs fine on four cores, but if I increase it beyond that I get this error:

pystac_client.exceptions.APIError: {"message":"Service Unavailable"}

The STAC Lambda function logs show that the function hits the timeout (20 seconds) but doesn't show a specific error (that I can find so far). I tried increasing the timeout to 60 seconds but am still running into the same issue.

The Postges database logs show messages like this:

2023-08-15 15:12:41 UTC:10.0.160.48(31142):eouser@eoapi:[23236]:LOG: could not send data to client: Broken pipe
2023-08-15 15:12:41 UTC:10.0.160.48(31142):eouser@eoapi:[23236]:STATEMENT:
SELECT * FROM all_collections();

2023-08-15 15:12:41 UTC:10.0.160.48(31142):eouser@eoapi:[23236]:FATAL: connection to client lost

Are there any configuration tweaks I might make to handle concurrent requests better?

• add an RDS Proxy in between the Lambda function and the database instance?
• increase the size of the database instance?
• limit number of concurrent Lambda function executions?

The Postgres logs suggest that the query SELECT * from all_collections() is causing the error. Could there be something wrong with my collection-level metadata that is causing this query to be really heavy?

Any suggestions you have for configuring the eoapi stack for higher traffic would be much appreciated!

[bitner]

Can you give any more details on how you have eoAPI configured / deployed?

By default, Postgres is not going to handle large numbers of connections well at all - which is particularly an issue when you are dealing with Lambda.

When using Lambda, you should make sure the database configuration for stac-fastapi and tipg are set to only use a single connection - with Lambda, there is no benefit to pooling.

You should definitely look at your database settings, particularly MAX_CONNECTIONS, SHARED_BUFFERS, and WORK_MEM. You are definitely not going to be able to have more Lambdas than the MAX_CONNECTIONS setting without using a separate pooler. You should also make sure that MAX_CONNECTIONS * WORK_MEM is less than what you have set for SHARED_BUFFERS. If you need more connections than allows that to happen (generally SHARED_BUFFERS should be about 1/4 of the total system memory), you may want to consider bumping up database instance sizes.

It can definitely be a good idea to put a connection pooler between the Lambdas and the database. I have not worked much with RDS Proxy, so I am not entirely sure if everything works with that, but pgBouncer definitely works well.

[hrodmn]

@bitner thank you so much for your advice!

Can you give any more details on how you have eoAPI configured / deployed?

I have two eoAPI deployments (production and staging), I used the default settings for everything when I deployed it using the AWS CDK app. They are both running pgstac==0.7.10. This week I have been experimenting with RDS Proxy in my staging deployment to see if that can help control the proliferation of connections.

By default, Postgres is not going to handle large numbers of connections well at all - which is particularly an issue when you are dealing with Lambda.

When using Lambda, you should make sure the database configuration for stac-fastapi and tipg are set to only use a single connection - with Lambda, there is no benefit to pooling.

The raster, stac, and vector services are all running as Lambda functions with the default DB_MAX_CONN_SIZE = 1.

You should definitely look at your database settings, particularly MAX_CONNECTIONS, SHARED_BUFFERS, and WORK_MEM. You are definitely not going to be able to have more Lambdas than the MAX_CONNECTIONS setting without using a separate pooler. You should also make sure that MAX_CONNECTIONS * WORK_MEM is less than what you have set for SHARED_BUFFERS. If you need more connections than allows that to happen (generally SHARED_BUFFERS should be about 1/4 of the total system memory), you may want to consider bumping up database instance sizes.

Out of the box, the RDS db.t3.medium (2 vCPU, 4GB RAM) is configured like this:

{'MAX_CONNECTIONS': '403', 'SHARED_BUFFERS': '939960kB', 'WORK_MEM': '4MB'}

Indeed, MAX_CONNECTIONS * WORK_MEM > SHARED_BUFFERS. I will try adjusting that to prevent tanking the memory on the database. Maybe it is possible to configure these settings differently during eoAPI deployment!

I have definitely not tried sending 403 simultaneous connections to the STAC endpoint (probably 40 at most), but when I watch the CloudWatch metrics, the freeable memory drops down to zero and the cpu utilization gets maxed out despite not being close to the MAX_CONNECTIONS limit.

During my testing today I tried upgrading the DB instance to a t3.2xlarge (8 vCPU, 32GB) just to see what would happen and so far it has handled 16 concurrent queries without a problem. Maybe that's a reasonable solution, but I still wonder why the smaller database instance can't keep up!

It can definitely be a good idea to put a connection pooler between the Lambdas and the database. I have not worked much with RDS Proxy, so I am not entirely sure if everything works with that, but pgBouncer definitely works well.

I tried adding an RDS Proxy to the eoAPI CDK stack today and it did not solve my particular problem, but I think that is because I am not even close to approaching the MAX_CONNECTIONS threshold with my usage.

[edkeeble]

Probably tangential to the real issue here, but one thing to watch out for with RDS Proxy is asyncpg (used by stac-fastapi) seems to have intermittent issues connecting to it: MagicStack/asyncpg#952

I've observed this in one of our stac-fastapi services running in ECS and it seems to only be an issue when first creating the connection. In our case, it means we'll typically see a bunch of logs for failed connections when the container first starts up, but finally asyncpg will successfully connect and the service will stabilize. I'm not sure how how that would impact a service running in Lambda, though, since I assume you'll be creating a new connection every time you hit a cold start.

[hrodmn]

Thanks for the heads up about the asyncpg issue, @edkeeble. I would be really interested to see what the ECS-based deployment looks like and how it performs with many concurrent requests!

I haven't done a lot of testing on my RDS Proxy deployment but I noticed that the default settings did not have any effect on the maximum number of connections that the Lambda function would open when I threw a bunch of concurrent requests at the API. You can set a cap based on a percent of MAX_CONNECTIONS from the database settings but I have not done extensive testing yet to find out if that can prevent the database from getting overloaded by connection overhead.

[zacharyDez]

@ranchodeluxe @bitner @sunu @ividito @vincentsarago ; Is database parameter configuration something we could factor into https://github.com/developmentseed/eoapi-cdk, https://github.com/developmentseed/eoapi-k8s, and maybe future work on SAM deployment?

[bitner]

Only to a very very limited extent. @alukach has already done some work at getting a better starting point using SDK which can at least help to not do things like having WORK_MEM * MAX_CONNECTIONS significantly greater than SHARED_BUFFERS (or about 25%-40% of the system memory), but even that starting point should be seen as just that -- a starting point.

The correct database parameters are VERY VERY VERY dependent on a number of specifics and there is not going to be any one size fits all that will be appropriate for all situations. There is a reason that there are people who's entire careers are administering databases.

The single biggest factor that there is no way that we can predict the best variables for is the level of concurrency required. The biggest knobs that we have to control concurrency are WORK_MEM and MAX_CONNECTIONS. WORK_MEM is very important in that it is the amount of memory that is available to each worker (note that with multiple CPUs there can be multiple workers in some instances). If WORK_MEM is set too low (which 4MB is probably a very low value) queries are going to be forced to put intermediate results to disk anytime the query is doing any kind of sorting or joining.

Basically, you can get away with a much much smaller database instance if you reduce the required concurrency - but you would need to adjust the settings appropriately. In the case @hrodmn has described, he likely needs to bump up the WORK_MEM from 4MB (experiment -- there is no real way to know the best value other than seeing how things behave for the specific queries getting most used). Then take that number that seems to work for low levels of connections and multiply it by the concurrency needed and then multiply that by four to get the size memory instance that should be used.

This is not the only memory that is getting used in Postgres -- each connection, even when idle can take upwards of 2-3MB just for the connection (this is a HUGE reason why using a connection pooler and managing things like the IDLE_SESSION_TIMEOUT are so important).

Every time that data in a table or index is fetched from disk, it is also pulled into memory. Frequently accessed data is able to stay in memory - this is why it is important to understand the sizes of the most frequently used indexes as you want to make sure you are on a system that is big enough to keep all frequently accessed index chunks in memory rather than needing to always go to disk.

A PgSTAC instance with a indexes on many columns rather than just the default geometry, datetime, and id columns is going to require much more memory than an instance with fewer indexes.

Postgres can also store temporary files during some queries which can be controlled by the TEMP_BUFFERS configuration.

Some things to watch for -

Pay attention to disk iops being used. Particularly on the smaller and burstable instance types, you can plow through your iops quota really really fast. One trick to get more iops on instances without reserved iops is to provision a much larger storage capacity as your iops quota is determined by the size of disk provisioned. If you have 10G of data, it might be cheaper to provision a 100G disk than to pay for reserved iops.

If the system load when looking at the memory usage and number of connections is low but queries are really slow, you probably want to consider bumping up the WORK_MEM (and correspondingly dropping down the MAX_CONNECTIONS -- whenever you change one, you should always change the other!). If the queries that are coming through that are slow are searching or sorting on fields that are not indexed, you will likely want to add some indexes (pgstac will automate this for partitions by managing what is indexed using the queryables table). More indexes is not always better though -- the more indexes there are, the speed of updates/inserts goes way down and there are also more indexes "competing" to stay in active memory.

If the CPU level is high, it is a good sign that you likely need a larger instance. There is one configuration in pgstac and stac-fastapi that can be used that pushes the "hydration" (basically using item_assets from a collection to compress the asset properties on an item) to the API rather than doing the CPU intensive job of merging objects in the database.

Another setting that you want to make sure you change on any database instances that are using SSD is to reduce the RANDOM_PAGE_COST from the Postgres default of 4 down to 1.1 (assuming that the default SEQUENTIAL_PAGE_COST hasn't been changed - it is the ratio of these two values that matters).