Cassandra warnings about tombstones on raw_extrinsic_metadata queries

Looks like the raw_extrinsic_metadata table has a lot of tombstones.

A tablestat info

                Percent repaired: 99.97
                Bytes repaired: 788.622GiB
                Bytes unrepaired: 258.229MiB
                Bytes pending repair: 0.000KiB
                Average tombstones per slice (last five minutes): NaN
                Maximum tombstones per slice (last five minutes): 0
                Droppable tombstone ratio: 0.25052

The checker script is spamming the logs:

	
[2024-03-08 18:27:57,292] Server warning: Read 5000 live rows and 5805 tombstone cells for query SELECT directory, fetcher_name, fetcher_version, format, metadata, origin, path, release, revision, snapshot, type, visit FROM swh.raw_extrinsic_metadata WHERE target = swh:1:dir:0c00174e41033b337f67575ce08c7492eedb5f9a AND authority_type = forge AND authority_url = https://opam.ocaml.org AND discovery_date > 2024-02-16T03:53:45.000Z AND id > 7339181526ca084c8826499c2f589ab313236a23 LIMIT 5000; token 3702119759087180356 (see tombstone_warn_threshold)

The code used to retrieve the object is here

        obj_model = RawExtrinsicMetadata.from_dict(obj)
        ids = [obj_model.id]
        cs_get = partial(cs_storage.raw_extrinsic_metadata_get_by_ids, ids)
        pg_get = partial(pg_storage.raw_extrinsic_metadata_get_by_ids, ids)

changed milestone to %Cassandra in production as primary storage [Roadmap - Tooling and infrastructure]

added cassandra label

changed title from Cassandra warning about tombstone on raw_extrinsic_metadata queries to Cassandra warnings about tombstones on raw_extrinsic_metadata queries

marked this issue as related to #4707 (closed)

changed the description

changed the description

Having looked a bit, that table has a high number of tombstones indeed [1]. It's off the charts [2] comparatively with other tables.

In the current storage implementation [3], we write null cells when adding new raw extrinsic metadata. So either our cassandra model should be reworked to 1. avoid this or the 2. storage implementation.

1. Rework the model: Adding the correct number of tables per combination of nullable fields (currently, nullable values can be release, revision, snapshot, directory)... So that translates to more RawExtrinsicMetadata(name_1, ..., name_n) tables.

2. Adapt storage implementation: maybe simpler but no idea if that's viable and/or the implications:

• Other references [3] [4] mention we could avoid setting any null values. When a field is None, do not mention the field during the insert action.

• write empty strings when that data is None instead of null (sounds like a workaround and we'd need to adapt the reading part too with the risk of confusion when the case of empty data strings exist too)

[1]

root@cassandra01:~# /opt/cassandra/bin/nodetool -u $USER -pw $PASS tablestats swh.raw_extrinsic_metadata | grep tombstone
                Average tombstones per slice (last five minutes): 230.452850877193
                Maximum tombstones per slice (last five minutes): 17084
                Droppable tombstone ratio: 0.24825

[2]

root@cassandra01:~# for obj_type in content directory release revision extid origin origin_visit origin_visit_status snapshot raw_extrinsic_metadata; do date; echo "object_ty
pe: $obj_type"; /opt/cassandra/bin/nodetool -u $USER -pw $PASS tablestats swh.$obj_type | grep tombstone; done
Wed 20 Mar 2024 09:58:01 AM UTC
object_type: content
                Average tombstones per slice (last five minutes): 1.0
                Maximum tombstones per slice (last five minutes): 1
                Droppable tombstone ratio: 0.00000
Wed 20 Mar 2024 09:58:03 AM UTC
object_type: directory
                Average tombstones per slice (last five minutes): 1.0004125715872978
                Maximum tombstones per slice (last five minutes): 10
                Droppable tombstone ratio: 0.09998
Wed 20 Mar 2024 09:58:05 AM UTC
object_type: release
                Average tombstones per slice (last five minutes): 1.0
                Maximum tombstones per slice (last five minutes): 1
                Droppable tombstone ratio: 0.02080
Wed 20 Mar 2024 09:58:07 AM UTC
object_type: revision
                Average tombstones per slice (last five minutes): 1.0
                Maximum tombstones per slice (last five minutes): 1
                Droppable tombstone ratio: 0.00494
Wed 20 Mar 2024 09:58:09 AM UTC
object_type: extid
                Average tombstones per slice (last five minutes): 1.0
                Maximum tombstones per slice (last five minutes): 1
                Droppable tombstone ratio: 0.00000
Wed 20 Mar 2024 09:58:11 AM UTC
object_type: origin
                Average tombstones per slice (last five minutes): 1.0
                Maximum tombstones per slice (last five minutes): 1
                Droppable tombstone ratio: 0.00000
Wed 20 Mar 2024 09:58:13 AM UTC
object_type: origin_visit
                Average tombstones per slice (last five minutes): 1.0
                Maximum tombstones per slice (last five minutes): 1
                Droppable tombstone ratio: 0.00000
Wed 20 Mar 2024 09:58:15 AM UTC
object_type: origin_visit_status
                Average tombstones per slice (last five minutes): 1.0
                Maximum tombstones per slice (last five minutes): 1
                Droppable tombstone ratio: 0.08160
Wed 20 Mar 2024 09:58:17 AM UTC
object_type: snapshot
                Average tombstones per slice (last five minutes): 1.0
                Maximum tombstones per slice (last five minutes): 1
                Droppable tombstone ratio: 0.00000
Wed 20 Mar 2024 09:58:18 AM UTC
object_type: raw_extrinsic_metadata
                Average tombstones per slice (last five minutes): 303.1795989537925
                Maximum tombstones per slice (last five minutes): 17084
                Droppable tombstone ratio: 0.24826

[3] https://gitlab.softwareheritage.org/swh/devel/swh-storage/-/blob/master/swh/storage/cassandra/storage.py?ref_type=heads#L1766-1770

[4] https://stackoverflow.com/questions/40713693/inserting-null-values-into-cassandra

[5] https://www.beyondthelines.net/databases/cassandra-tombstones/

@teams/developers @teams/sysadmin thoughts ^ ?

In the specific case of the raw_extrinsic_metadata table, non-null values for the context fields will always be swhids or a non-empty path, so I believe the empty string would be a good, inambiguous substitute to null values.

changed the description

mentioned in commit ardumont/swh-storage@fd481f7a

mentioned in merge request swh/devel/swh-storage!1108 (merged)

mentioned in commit ardumont/swh-storage@09b3f16f

mentioned in commit ardumont/swh-storage@8c6f87fa

mentioned in commit ardumont/swh-storage@172b4507

mentioned in commit ardumont/swh-storage@8ff48361

mentioned in commit ardumont/swh-storage@4b8a0dac

Perhaps this table would also deserve a bit of remodeling. It's the table with the big 1G partition (I thought it was directory_entry, but I was wrong).

The following grafana/prometheus query gives the exact numbers: max( cassandra_table_maxpartitionsize{environment="production"}) by(table)

The dsbulk tool allows to retrieve the id of N biggest partitions of a table:

 %  ./dsbulk count --driver.advanced.auth-provider.username=admin --driver.advanced.auth-provider.password=$PASS --driver.basic.contact-points $(hostname -f) -k swh -t raw_extrinsic_metadata -stats partitions -partitions 10
...
id                                                    lines percent
'swh:1:dir:a00426caacd26b8fd20704887fb4d7cefcf7edc1' 153954 0.04
'swh:1:dir:a51b9a6cad80abba3f7ce4ec051ace7c2b0eeb90' 152906 0.04
'swh:1:dir:c24ff7f51209509c28ad026f06c4254f57663458' 144716 0.04
'swh:1:dir:0c00174e41033b337f67575ce08c7492eedb5f9a' 141241 0.04
'swh:1:dir:45b87e1ca127914b936884dc73c9f788df5b7abd' 138551 0.04
'swh:1:dir:ab964a5bd58ffce2bb24667bc08dd90b7ffaea84' 138534 0.04
'swh:1:dir:2a9145a732b61adc6ff97a182ed2be914bc700df' 133214 0.03
'swh:1:dir:0145f0b12499573f9a2600878397e47dcdf6d3cc' 133207 0.03
'swh:1:dir:6745965831928f63790580d9587fa70ca0499fad' 131010 0.03
'swh:1:dir:7d7acd7ac3143e9d8e2cf3083947ec0263439ac3' 129105 0.03

The partitions get bigger and bigger each day.

After a random picking on the 50th biggest partitions, it seems the directories are all related to opam and more precisely the tezos project.

For example:

cqlsh:swh> select count(*) from raw_extrinsic_metadata where target='swh:1:dir:ab964a5bd58ffce2bb24667bc08dd90b7ffaea84';

 count
--------
 138770

(1 rows)

A little more of 700 lines are added each day for this directory. And those lines are not the smallest ones.

I've no idea how this table is requested and how it could be partitioned differently. I'll create a dedicated issue on swh-storage to track the problem.

For what it's worth, the reason this is happening is that:

1. Tezos has 175 OPAM packages; and on every release they push the same directory to every single one of them
2. The OPAM loader is not incremental, so every time there is a new release, it fetches and re-uploads metadata for every past release

I'll create a dedicated issue on swh-storage to track the problem.

fwiw, it's the following issue [1]

[1] swh/devel/swh-storage#4708

you mean swh/devel/swh-storage#4708

assigned to @ardumont

mentioned in commit ardumont/swh-storage@6a850c80

mentioned in issue swh/devel/swh-storage#4708