Out-of-memory Exception during Certificate Ingestion

[cyrill-k]

When ingesting a batch of ~40 million certificates from local csv files via the CertificateFolders config option introduced in https://github.com/netsec-ethz/fpki/tree/cyrill-mapserver-improvements, the mapserver runs out of memory during the SMT update phase (after adding the certificates and coalescing the payloads).

...
coalescing certificate payloads at 2024-03-21T19:31:52Z
updating SMT at 2024-03-21T19:55:49Z

[cyrill-k]

This bug can be reproduced as follows:

• clear DB: ./create_schema.sh
• set high max ingestion rows limit in mapserver config: CsvIngestionMaxRows: 10000000
• ingest from google 2024/2025 logs:

    "CTLogServerURLs": [
        "https://ct.googleapis.com/logs/eu1/xenon2024",
        "https://ct.googleapis.com/logs/eu1/xenon2025h1",
        "https://ct.googleapis.com/logs/eu1/xenon2025h2",
        "https://ct.googleapis.com/logs/us1/argon2024",
        "https://ct.googleapis.com/logs/us1/argon2025h1",
        "https://ct.googleapis.com/logs/us1/argon2025h2"
    ],
  

• (optionally) speed up by ingesting from local csv files:

    "CertificateFolders": {
        "https://ct.googleapis.com/logs/eu1/xenon2024": "/mnt/external/ct-log-download/certificates/https:__ct.googleapis.com_logs_eu1_xenon2024/bundled",
        "https://ct.googleapis.com/logs/eu1/xenon2025h1": "/mnt/external/ct-log-download/certificates/https:__ct.googleapis.com_logs_eu1_xenon2025h1/bundled",
        "https://ct.googleapis.com/logs/us1/argon2024": "/mnt/external/ct-log-download/certificates/https:__ct.googleapis.com_logs_us1_argon2024/bundled",
        "https://ct.googleapis.com/logs/us1/argon2025h1": "/mnt/external/ct-log-download/certificates/https:__ct.googleapis.com_logs_us1_argon2025h1/bundled"
    }
  

• restart service: sudo systemctl restart fpki-mapserver.service
• inspect output sudo journalctl -fu fpki-mapserver.service

[cyrill-k]

When adjusting the batch size to 100000, the SMT can be updated without any issues.

However, when using a batch size of 1000000, the following error message appears:

• Apr 08 18:01:50 netsec-hpc-articuno mapserver[5245]: ERROR: update returned updating SMT: commitChangesToDB | UpdateKeyValuePairBatches | error inserting key-values into tree: Error 1390 (HY000): Prepared statement contains too many placeholders

We may be able to solve this problem by:

• limiting the number of certificates added in a single update cycle. This is already supported for the LocalLogFetcher but not so easy to implement for the HttpLogFetcher since we need to process all certificates until the current STH.
• Or, do a batch-wise update of the tree nodes.

[juagargi]

Although it could have happened before this step, when the ingest tool runs the SMT update, we run out of memory in mysqld. See attached screenshot.

We probably need to:

• Hard limit the RAM use by mysql
• Do the SMT update in bundles.