Hi Benoit,
Thank you for working on this - highly appreciated!
I'm not very familiar with JPA myself, but after digging in there seems to be `indexes` property of
@Table annotation, which could take multi-column indexes
(https://www.baeldung.com/jpa-indexes#2-multicolumn-index) so it seems that slightly modifying
@Table annotation on JPAMailboxMessage
(https://github.com/apache/james-project/blob/master/mailbox/jpa/src/main/java/org/apache/james/mailbox/jpa/mail/model/openjpa/JPAMailboxMessage.java#LL48C21-L48C21)
from:
```
@Table(name = "JAMES_MAIL")
```
to something like this:
```
@Table(name = "JAMES_MAIL", indexes = {
@Index(columnList = "MAILBOX_ID, MAIL_IS_RECENT"),
@Index(columnList = "MAILBOX_ID, MAIL_IS_SEEN")
})
```
should give you what you need
Wojtek
On 14/06/2023 07:31, Benoit TELLIER wrote:
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
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscr...@james.apache.org
For additional commands, e-mail: server-dev-h...@james.apache.org
