S3 Bucket Analysis

We collect in this document the various steps taken to gauge the amount of data in the S3 proddata.sagebase.org bucket comparing it to the data indexed in the synapse database.

• A snapshot of both the prod database and the tables database was taken on stack 332 on November 5th.

• The S3 inventory was enabled on production and a snapshot was produced on November 5th

S3 Bucket

Data reported by S3 metrics on November 5th (From the cloud watch metrics for the bucket), note that results might not be precise:

• Storage: 707451754672065 bytes (643TB)

• Number of objects: 43,536,933

S3 Inventory

The inventory was enabled (See https://sagebionetworks.jira.com/browse/PLFM-6426 ) on the bucket.

We created a database named files_inventory in the Glue catalog. In Athena we created an external table for the S3 inventory:

We ran the repair on the table:

We check how much data and objects are reported (dt is the date when the inventory is generated):

Results:

The inventory reports also if a file was uploaded as multipart, this would provide us with how many objects are uploaded without going through the standard synapse upload API:

This result is surprising, only 5.7M objects seems to be multipart uploads but we do have an order of magnitude more than that in the database, what is going on?

On further analysis we checked a few of those files and we could see that they were in fact normal multipart uploads in the DB with the relative file handles. The reason for this inconsistency is that we encrypted the S3 bucket back in 2019, this most likely was done using a PUT copy of the same object. This belief is reinforced by the fact that the modified dates on those objects seem to be consistent with the timeline of the encryption, while the original upload date in synapse was done prior. If the python API was used most likely all the objects that were smaller than a certain size were “copied” over without multipart.

File Handles

We created a table that contains only data pointing to the proddata.sagebase.org bucket that also includes a de-duplication identifier:

Count and size of the file handles that are indexed for the prod bucket:

Excluding duplicated file handles pointing to the same key:

There are 7680010 * 2 file handles that are duplicated (pointing to the same key), the size of those copies is around 46TB.

S3 Inventory vs File Handles

Since we have the S3 inventory in parquet format we decided to export the snapshot of the production file handles table as created above to S3 in parquet format, so that we can analyze further the data. The file handles table has a considerable size and we decided to use an AWS Glue job to export the table. There are some caveat since apparently at the time of writing Glue does not fully support MySQL 8 and this is not fully documented. The workaround is to customize the data source JDBC connection using a compatible MySQL driver (See https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect.html#aws-glue-programming-etl-connect-jdbc).

We uploaded the JDBC driver JAR for version 8.0.22 in the bucket used for analytics and used in the connection (using the "customJdbcDriverS3Path" and "customJdbcDriverClassName" properties of the connection). The Job was parallelized using an hash expression on the id with 7 worker nodes, note that we filter out the duplicated file handles as well. Below the ETL script used for exporting the data to S3 in this way is as follows:

The Job on the the table that had around 46M rows took around 20 minutes (on an smaller instance with 16GB of ram and 4 vcpu). We then created a crawler to discover the table from S3:

Once the table is added to the catalog we can now run queries with Athena joining the S3 inventory and the file handles table, we computed the count and size of objects that are stored in file handles but for which we do not have data in prod:

We can also run the inverse, to find which data is in prod but not indexed (which should retain a result similar to previous findings), note that we filter on the date of the inventory snapshot in this case:

Note that this number most likely contains temporary objects never deleted (e.g. temporary multipart upload files, old tests, staging data etc).

File Entities

From the synapse storage report that is generated monthly and stored in a synapse table we can get an idea of how much data is used by synapse entities in projects, the following query gets the aggregated sum of the size in bytes for the last 10 months:

SELECT `Date`, SUM(sizeInBytes) FROM syn18406644 GROUP BY `Date` ORDER BY `Date` DESC LIMIT 10

Note that this report does not account for file handles that are referenced in tables or other objects or file handles that are not linked to anything.

Running the following query should retains similar results, but more inline with the snapshot we are using and excluding eventual duplicates:

So out of 39426647 distinct file handles in prod that take 633TB of data, 4659729 (~12%) file handles are used as entities and make up for 559.5TB (~88%) of the data. So about 34766918 (88%) file handles taking 73TB (~12%) of data are potentially linked in tables or other objects or used by unlinked file handles.

Synapse Tables

Each synapse table might link to file handles and for data access checks we keep for each table a companion table that stores the file handle id for each table. To get an idea of how much data is accounted for by synapse tables we created a table that contains the union of all the tables.

We created a container table:

And we run the following script, we also record the origin id of the table referencing the file handle:

This copied around 10.5K tables, out of which about 1.3k contained file handles. The process took roughly 4 minutes and exported 36173253 file handles. The biggest table contains 7093160 file handles. The distinct number of file handles is 27624848 (This is probably due to table snapshots).

We then created an additional FILES_TABLE_DISTINCT table to stored the the distinct file handles ids:

Note that we used a flag to indicate if the file handle is “reused”, since we have the object replication data we can verify if any of the file handles in tables are also reused by entities using a query such as (We created an index on both the FILE_ID in the object replication and on ID in the FILES_TABLE_DISTINCT first):

We have 49166 file handles referenced in tables that are also used in file entities. We can update the table above with this information:

We created a Glue job to export this table to S3 so that we can join on the S3 data to compute the size claimed by tables, the ETL script is as follows:

With this table catalogued in Glue, we can now join the data in S3 using Athena (Note: for this we used a dump of the file handle table computed above, but including the file handles with duplicate key since we are joining on ids), we compute the sum of the file sizes that are linked in the tables:

Note that we filter by the reused = False since those file handles are already counted by file entities.

This number is the unique file handles referenced by tables that is stored in the production bucket, if we want to compute the actual size (e.g. excluding file handle copies):

The space taken by the file handles that are linked both in tables and in entities (and that are included in the storage report reported above):

Trying to deduplicate from copied file handles does not yield different results (meaning that this file handles were never copied):

These are interesting numbers, it looks like the table links are only claiming about 3.4TB of data. The total number of file handles referenced by synapse tables and that are in the production bucket is 13760593 (49166 + 13711427). Out of 27624848 unique file handles referenced by synapse tables 50% are in the prod bucket (The rest might be file handles that are stored elsewhere).

Other Linked Objects

We have several other objects in Synapse that link back explicitly to file handles, the list of these relations is described by https://rest-docs.synapse.org/rest/org/sagebionetworks/repo/model/file/FileHandleAssociateType.html. Most of those are relatively small sets, in the following we gather the statistics about the following associations where we see relatively big numbers:

• MessageAttachment: File handles for user messages

• WikiMarkdown: File handles that contain the markdown of wikis

• WikiAttachment: File handles attached to wikis

• SubmissionAttachment: File handles submitted as part of evaluation submissions (This are submitted as entities, but we keep the list of file handles submitted. Since entities can be deleted we need to compute the delta)

The above objects track their file handle associations into dedicated tables and we can figure out the count and size, taking care of de-duplicating:

MessageAttachement

The messages store their file handles in the MESSAGE_CONTENT table, the file handles are either uploaded internally (e.g. system generated emails) or externally (first the message is uploaded and then the message is sent through the API, an example is the evaluation orchestrator):

We can count and compute the file size directly from the DB:

Just for completeness we can actually de-duplicate further by the object key (since file handles are exposed in the API external processes might actually make a copy of the same file handle):

WikiMarkdown and WikiAttachment

The file handles for both the wiki markdown and the wiki attachments are tracked in the table V2_WIKI_ATTACHMENT_RESERVATION:

De-duplicating for the size:

SubmissionAttachment

Evaluation submissions file handles are tracked in the table JDOSUBMISSION_FILE:

Note however that submissions are linked to entities, we need to compute the count and size of submissions for which the file handle is NOT linked to an entity:

Therefore out of 93198 submissions with file entities, 42979 (46%) are not linked to entities and take 0.55TB of data (0.47% of the total). Note that we used the table with file handles that are only in prod (e.g. the temporary table FILES_PROD created previously), the numbers are similar if we include all other storage locations (+/- 300GB).

Temporary File Handles

Synapse also uses file handles for what I refer to as temporary file handles, this are file handles used most likely only once. These file handles can be generated internally by the system or uploaded externally, for the latter we cannot control where the file handle ends up (e.g. in our bucket).

In particular we identified in the code base 4 places where we use file handles that might end up in our bucket (The main reference is the usage of the multipartUploadLocalFile method: https://github.com/Sage-Bionetworks/Synapse-Repository-Services/blob/e2b84bfbbd1d1450e955f360199f5448a1b620c3/services/repository-managers/src/main/java/org/sagebionetworks/repo/manager/file/MultipartManagerImpl.java#L222) for this cases we do not have an associations but still make up a chunk of the data in prod:

• Storage reports: CSV reports that contains a breakdown of data consumed by entities in each project.

• Query results: Table and View query results that are stored in CSV or TSV files. They are used in some cases as a cache.

• Bulk downloads: Zipped packages for bulk downloads up to 2GB.

• Tables uploads: This are CSV files uploaded by the user in order to update tables or views.

All of the above are linked to asynchronous job requests, the body of the request is stored as a blob in the database. Unfortunately the asynchronous job status is not a migratable table therefore we lose the file handle links at each release cycle.

For some of these we can estimate the size since we know the file name pattern used by the backend when the file handles are created, for this task we used Athena on the exported file handle table in S3 since the results can be computed much faster:

Storage reports

They are initiated from a DownloadStorageReportRequest, a background job creates a file with the pattern Job-{jobId}.csv. Using Athena we ran the following query:

Note that these includes also query results in csv format, since they follow the same pattern.

Query Results

They are initiated from a DownloadFromTableRequest, a background job creates either a CSV or TSV file with the pattern Job-{jobId}.{csv|tsv}. Since the previous query included the csv pattern we ran the following query:

Bulk Downloads

They are initiated from a BulkFileDownloadRequest, a background job created a zipped package using the file pattern Job{jobId}.zip. This is partially true as the request can override the filename. We ran the following query with Athena:

Since this is lot of data, to make sure those are not file handles used in entities or tables we additionally ran the following query:

That returned 0 results. Meaning that with a high probability this file handles are not linked anywhere else.

Table Uploads

They are initiated from an UploadToTableRequest, this request can even be embedded in a TableUpdateTransactionRequest as part of the change set. Unfortunately at the moment we do not have a way to estimate their size or numbers, in particular we verified that:

• The asynchronous job status table is not migratable therefore we lose any information in the original request

• The data warehouse does not store the request body for requests and the id of the file handle is stored as part of the request body

• We checked the web client implementation to see if there was a common file name pattern used when uploading the CSV but the name of the original file is used instead

Multipart Uploads

In prod we do not clean up unfinished multipart uploads, the following script was used to fetch some statistics about those:

So we have about 1.4 M multipart uploads that are potentially taking storage but are not completed, we could not compute the size of the uploads/parts. Since we upload temporary objects for each part it might also make up the difference reported by the size in S3 and the size in indexed in the file handles table.

The indexed multipart uploads in the DB give us a very different picture:

Result: 6353

We can have a rough estimate of the amount of data that has been uploaded in prod but not yet completed:

So we have about 2.4TB of data that could be potentially freed just removing the unfinished multipart uploads.

Upon further analysis of the backend code we discovered a bug where a multipart upload is initiated when we create or update a wiki page using the first version of the wiki API that submitted the markdown as a string. The multipart upload is never completed for such cases: https://sagebionetworks.jira.com/browse/PLFM-6523. Additionally the new multipart upload that tracks the uploads was implemented relatively recently, the previous implementation might have left behind other unfinished multipart uploads.

Initial Summary of Results

Working on a snapshot of prod 332 (11/05/2020) for the production bucket we have the following numbers for file handles:

Additionally we have the following figures for S3:

In summary out of the 47M file handles that point to the production bucket, we can account for about 24M (~50%). Out of 633TB of indexed data, we can account for about 585 TB (92%). The amount of data that can potentially be archived amounts to about 48 TB, referenced by around 23M file handles. Note that the temporary file handles can potentially be archived as well removing an additional 20.7 TB from the bucket.

Unlinked and Hot Data

As of May 2021 we implemented the discovery of association and the unlinked file handle detection. In order to decide how to proceed with a strategy to archive unlinked data (See Synapse S3 Storage Maintenance | Un linked File Handle Archival ) we need to make an estimate of some of the data we collected. In the following we provide the steps taken for collecting this data.

Unlinked Data

We wanted to know how much data is unlinked that does not have copies that are still linked, we proceeded as follows:

1. We executed the unlinked file handle detection on a migrated staging version (Stack 357)

2. A snapshot of the DB was created in AWS

3. Created a table to hold unlinked file handles found for the proddata.sagebase.org bucket including the count of keys that are linked + count that are unlinked:

4. Imported in the FILES_UNLINKED table all the file handles that are unlinked in proddata.sagebase.org:

5. Computed the unlinked count and size:

6. Computed the unlinked count and size for keys >= 128KB:

7. Computed the monthly average count and size of unlinked data:

This are the results:

• Unlinked Data Count: 8,762,805

• Unlinked Data Size: 100,456,586,079,288 (91.36 TiB)

• Unlinked Data Count (>= 128 KB): 2,823,189

• Unlinked Data Size (>= 128 KB): 100,408,552,794,768 (91.32 TiB)

• Monthly Unlinked Count (>= 128KB): 28,808

• Monthly Unlinked Size (>= 128KB): 1,024,577,069,334 (0.93 TiB)

Hot Data

Additionally we wanted to have a rough estimate of the amount of hot data in our bucket. Unfortunately we never enabled the bucket analytics so we have to work with the data that we collect internally. In particular we collect the downloads from entities and tables and we store the records in S3 in parquet format, we can query this data with Athena, joining on the file handle data that we now export in S3 to get the count and size (we computed for years 2020 and 2021):

Note that we only consider downloads in our bucket. Additionally we wanted to know how much of this is for files that are bigger than 128KB:

The results are as follow:

• Hot Data Count*: 9,802,820

• Hot Data Size*: 314,611,245,904,518 (286.1 TiB)

• Hot Data (>= 128KB) Count: 4,555,052

• Hot Data (>= 128KB) Size: 314,472,063,061,465 (286 TiB)