[cassandra] snapshot_count_branches timeouts on large snapshots

When attempting to browse an origin with a snapshot containing a large number of branches, the snapshot_count_branches operation timeouts with the cassandra storage backend.

For instance, when browsing https://archive.softwareheritage.org/browse/origin/directory/?origin_url=https://github.com/web-platform-tests/wpt, the latest snapshot for that origin is bd9a5ff52282a9f189334dde60ab799f84ee2cbb and it contains 106567 branches.

softwareheritage=> select count(*) from snapshot_branches where snapshot_id = (select object_id from snapshot where id = decode('bd9a5ff52282a9f189334dde60ab799f84ee2cbb', 'hex'));
 count  
--------
 106567
(1 row)

I think we should split the queries to get a snapshot size into multiple ones to avoid being hit by these cassandra timeouts.

Related sentry issue: SWH-STORAGE-2XA3.

I think we should split the queries to get a snapshot size into multiple ones to avoid being hit by these cassandra timeouts.

We can increase the timeout for this particular query instead.

We can increase the timeout for this particular query instead.

Bumping the timeout from 1000 to 1000000000 in CqlRunner._execute_with_retries_inner does not fix the issue.

Also there is something odd as when executing the script below without modifying the timeout value, the queries retrieving all the snapshot branches are successfully executed while one of those counting the number of branches timeouts.

from swh.model.hashutil import hash_to_bytes
from swh.storage import get_storage


storage = get_storage(
    "cassandra",
    hosts=[f"cassandra{i:02d}.internal.softwareheritage.org" for i in range(1, 11)],
    keyspace="swh",
    consistency_level="LOCAL_QUORUM",
    auth_provider={
        "cls": "cassandra.auth.PlainTextAuthProvider",
        "username": "swh-ro",
        "password": "***", # get it from credentials repository
    },
)

snapshot_id = "bd9a5ff52282a9f189334dde60ab799f84ee2cbb"

# works fine
storage.snapshot_get_branches(
    hash_to_bytes(snapshot_id),
    branches_count=1000000,
    branch_name_exclude_prefix=b"refs/pull/",
)

# timeouts
storage.snapshot_count_branches(
    hash_to_bytes(snapshot_id), branch_name_exclude_prefix=b"refs/pull/"
)

The queries to get all snapshot branches are the following:

<PreparedStatement query="SELECT snapshot_id, name, target_type, target FROM swh.snapshot_branch WHERE snapshot_id = ? AND name >= ? AND name < ? LIMIT ?", consistency=Not Set>
<PreparedStatement query="SELECT snapshot_id, name, target_type, target FROM swh.snapshot_branch WHERE snapshot_id = ? AND name >= ? LIMIT ?", consistency=Not Set>

while those counting the number of branches per target type are:

<PreparedStatement query="
        SELECT ascii_bins_count(target_type) AS counts
        FROM swh.snapshot_branch
        WHERE snapshot_id = ? AND name < ?
        ", consistency=Not Set>
<PreparedStatement query="
        SELECT ascii_bins_count(target_type) AS counts
        FROM swh.snapshot_branch
        WHERE snapshot_id = ? AND name >= ?
        ", consistency=Not Set>

In that case, this is the second one that timeouts.

I am suspecting the use of the ascii_bins_count custom stored function is degrading query performance.

weird. The query does not actually take 1000000000s to run, right? There may be an other timeout elsewhere

I am suspecting the use of the ascii_bins_count custom stored function is degrading query performance.

Possible. I never looked into the performance of stored functions and just assumed it would be faster than returning results.

weird. The query does not actually take 1000000000s to run, right? There may be an other timeout elsewhere

I guess there is some timeout values set in cassandra server configuration.

I am trying to reproduce the issue in docker environment but the https://github.com/web-platform-tests/wpt repository is quite huge so I did not manage to fully load it yet.

Would it not be easier to reproduce this issue on the staging environment?

It's an environment closer than production in term of replication and data.

I submitted https://github.com/web-platform-tests/wpt to SCN on staging in order to check if the issue arises here too.

I was intending to hack a bit on the stored cql function so that's why I tried to load that big repository locally (but did not entirely success yet, got hit by kafka error about too large messages after 12 hours of loading).

So if I replace the use of the custom ascii_bins_count function by a COUNT(*) instruction, there is no more timeouts.

mentioned in merge request !1162 (merged)

As I suspected the use of a custom cassandra aggregate function was the root cause of the issue. I just submitted !1162 (merged) that performs the aggregation client-side instead as the performance are much better this way and timeouts are no longer reported.

closed with commit 87ae723c

mentioned in commit 87ae723c