exadel-inc / exadel-inc/CompreFace

Compreface makes suboptimal queries to DB on UPDATE event

Open
#1,197 0 comments 3 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
8.3k
Forks
1.2k
PR merge metrics
No merged PRs in 30d

Description

**Describe the bug**
Our installation:
1) CompreFace 1.1.0 in k8s (3 api servers) with resourses:
```
resources:
limits:
cpu: 3
memory: 21Gi
requests:
cpu: 1
memory: 15Gi
```
2) Subjects number ~ 189000
3) Faces number (examples) ~ 243000

We use compreface's recognition API as a second factor for 2FA and see in pg logs sql queries like
```
select embedding0_.id as col_0_0_, embedding0_.embedding as col_1_0_, subject1_.subject_name as col_2_0_
from public.Embedding embedding0_ left outer join public.Subject subject1_ on embedding0_.subject_id=subject1_.id
where subject1_.api_key=$1
```
which takes much time to execute on our data (query explain analyse is below):
```
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------+
Gather (cost=7094.81..101440.07 rows=242447 width=1085) (actual time=91471.818..102814.254 rows=241970 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Parallel Hash Join (cost=6094.81..76195.37 rows=101020 width=1085) (actual time=91475.228..91532.997 rows=80657 loops=3) |
Hash Cond: (embedding0_.subject_id = subject1_.id) |
-> Parallel Seq Scan on embedding embedding0_ (cost=0.00..41755.33 rows=101033 width=1080) (actual time=0.007..27.440 rows=80663 loops=3)|
-> Parallel Hash (cost=3891.00..3891.00 rows=108465 width=37) (actual time=91295.915..91295.916 rows=62535 loops=3) |
Buckets: 65536 Batches: 4 Memory Usage: 3872kB |
-> Parallel Seq Scan on subject subject1_ (cost=0.00..3891.00 rows=108465 width=37) (actual time=0.013..21.257 rows=62535 loops=3) |
Filter: ((api_key)::text = 'XXXXXXXXXXXXXXXXXXXXXXXX'::text) |
Rows Removed by Filter: 7 |
Planning Time: 0.214 ms |
Execution Time: 102825.281 ms |
```

I made a small research and found that [this query](https://github.com/exadel-inc/CompreFace/blob/4d826e53bdbe82c2a2ed2229aa27785cdf732247/java/common/src/main/java/com/exadel/frs/commonservice/repository/EmbeddingRepository.java#L31) is used for cache updating:
1) [compreface api receives an update event through pg notify command ](https://github.com/exadel-inc/CompreFace/blob/d862a4eee7afbf9a546d13e2dfd39c3ebdcf4808/java/api/src/main/java/com/exadel/frs/core/trainservice/service/NotificationReceiverService.java#L84C50-L84C50)
2) [compreface api creates new EmbeddingCollection for cache and rewrite the cache entry by api key](https://github.com/exadel-inc/CompreFace/blob/4d826e53bdbe82c2a2ed2229aa27785cdf732247/java/api/src/main/java/com/exadel/frs/core/trainservice/service/EmbeddingService.java#L37)

**Expected behavior**

Compreface API uses more optimal approach to update `EmbeddingCollection` cache

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.