Details
Major Description
Analysis
Today messagev2 metadata table is the largest table of our schema (11GB on 6 million messages instalation).
Reads to it are abnormally long, and this is the query we spend the more time executing while doing IMAP:
(see attached screenshot)
That is by far our most expensive query (per row), and that while LWT do not even come into play!
Looking at table stats:
Number of partitions (estimate): 6100310
SSTable Compression Ratio: 0.43905338269472705
Space used (total): 2037635717
Doing a nodetool status, we realize that total occupied space in Cassandra is of `4.97 GiB`, so messagesv2 table occuppy 40% of total storage space which is a lot compared to other messages metadata tables (~180MB so 10x less).
Doing a tablehistograms analysis:
Percentile SSTables Write Latency Read Latency Partition Size Cell Count
(micros) (micros) (bytes)
50% 1.00 0.00 315.85 770 12
75% 1.00 0.00 545.79 770 12
95% 1.00 0.00 1131.75 1109 17
98% 1.00 0.00 1629.72 1331 17
99% 1.00 0.00 1955.67 1597 20
Min 0.00 0.00 88.15 30 0
Max 1.00 0.00 3379.39 1131752 770
We realize that cell count is high, and that the byte count is high (other message metadata table are between 125 -> 250 bytes so 6 times less).
Knowing our data model, each message have a set of properties, each composed of a namespace, a name and a value. These are stored as a UDT list, thus is really space inefficient. Compress ratio (see above) do not compensate for this.
These properties are well defined, set by the StoreMailboxManager and only include:
- mediaType
- subType
- ContentId
- ContentDescription
- ContentTrasferEncoding
- ContentLocation
- ContentDispositionType
- ContentDispositionParameters
- ContentTypeParameters
- ContentMD5
- ContentLanguage
- Charset
- MimepartDelimites
Why?
Here is the conclusion:
- Slow reads on messagev2 table have a large impact on IMAP performance (and on Cassandra performances)
- This slowness is due to the corresponding space used on disk (more data = slower reads)
- This extra space is due to an inefficient storage format of the property fields
- By restructuring the way we store these properties we can reclaim disk space and thus query read speed
How?
- Remove unused properties: see https://github.com/linagora/james-project/pull/3925 (charset and mime part delimiters are unused)
- Restructure CassandraMessageDAO (and underlying table) to store known properties in a column instead of a UDT list. Unknown properties shall be rejected.
This avoids the use of a collection on a critical table thus should significantly fasten related operations.
A data migration (messagev3) will be needed.
Definition of done
- Come up with space occupied per message update within tests for the old and the new
- IMAP performance tests
