Hello Wojtek,
Thanks for the hint, https://github.com/apache/james-project/pull/1595
thus addresses this.
Best regards,
Benoit
On 15/06/2023 01:30, Wojtek wrote:
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
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscr...@james.apache.org
For additional commands, e-mail: server-dev-h...@james.apache.org
