Hello community,
Lately I devoted attention to James JPA product.
With the help of Wojtek we solved a leak problem, synchronous execution
on the Netty event loop problem and identified that STATUS and SELECT
where (to say the least) shockingly slow.
Here is the bulk of PRs related to the topic:
- Use bounded elastic https://github.com/apache/james-project/pull/1579
- Avoid connection leaks https://github.com/apache/james-project/pull/1581
- Reuse entity manager https://github.com/apache/james-project/pull/1582
- Improve UID listing https://github.com/apache/james-project/pull/1583
- Today I did put together an enhancement for loading Applicable flags
in a quicker fashion: https://github.com/apache/james-project/pull/1593
As shown in #1593 this partially solves the problem: p99 latencies for
SELECT an STATUS are still through the roof on a set up with moderate
data: 10 req/s leads to ~2s tail latency.
POCing around I decided to manually create on my MariaDB container the
index that I did think relevant:
root@e774b9c0d6df:/# mysql -u test -p test -e 'CREATE INDEX bte1 ON
JAMES_MAIL (MAILBOX_ID, MAIL_IS_RECENT) ;'
root@e774b9c0d6df:/# mysql -u test -p test -e 'CREATE INDEX bte2 ON
JAMES_MAIL (MAILBOX_ID, MAIL_IS_SEEN) ;'
Those index turned to be used:
root@e774b9c0d6df:/# mysql -u test -p test -e 'SELECT * FROM
information_schema.INDEX_STATISTICS;'
+--------------+--------------------------+------------------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+--------------------------+------------------------+-----------+
| test | OPENJPA_SEQUENCE_TABLE | PRIMARY | 22 |
| test | JAMES_USER | PRIMARY | 100 |
| test | JAMES_MAILBOX | PRIMARY | 3379 |
| test | JAMES_MAIL | PRIMARY | 1977 |
| test | JAMES_MAIL_REPOS | PRIMARY | 16 |
| test | JAMES_MAIL | bte2 | 1038792 |
| test | JAMES_MAIL | bte1 | 4366042 |
| test | JAMES_QUOTA_CURRENTQUOTA | PRIMARY | 712 |
| test | JAMES_MAIL | I_JMS_MIL_MAIL_IS_SEEN |
3690116 |
+--------------+--------------------------+------------------------+-----------+
And the results are astonishingly good! P99 dropped to 69ms and 39ms for
both commands...
So first, I wanted to share this result with the community!
And second,*I have no idea how to create an index on several row with
OpenJPA...* Ideally this process shall be automated so that every one
benefits from it, not just the lucky enough users to read that mail!
*So if by any chance we have lucky JPA wizards around that want to give
this a shot...*
On my side I'll try to push the benchmarks with more concurrent users,
more data...
Best regards,
Benoit
