800,000+ Assets, Performance is unusable #7166
Comments
|
How is the performance on the web client? |
|
Moving cursor on the right vertical timeline, freeze up the browser most of the time. Initial loading is ok (when open the "Photos" page), but when click on timeline (vertical to the right), the browser freeze up |
|
try run "ALTER DATABASE immich SET log_min_duration_statement = 500;" and the console log will show all query taking too long |
|
Above is sequencial scan report |
|
Running android mobile app (on XiaoMi 13T Pro), "Building the timeline" on my 1GB LAN from Immich Server running on Unraid (Intel I7 13Gen), my sync data transfer is less than 2MB/s, most of time just idle, I hope the sync could be much faster. I remember before than paging change, transfer stay constant some faster than 8MB/s This time snapshot shows how long it takes to go from OFFSET 320000 to OFFSET 820000, it took 9 minutes. The worst part is after the "Building the timeline" is completed, only small amount of photo/videos (less than 50) are show up on my app, after pull down to refresh, the "Building the timeline" start all over againLIMIT 5000 OFFSET 320000 LIMIT 5000 OFFSET 335000 LIMIT 5000 OFFSET 425000 LIMIT 5000 OFFSET 550000 LIMIT 5000 OFFSET 625000 ASC LIMIT 5000 OFFSET 820000 |
|
Thank you for the report. This is very useful as we haven't been able to setup an instance with this many assets yet ❤️ |
|
Thank you, Alex and the team, amazing work!! |
|
Would you be able to add |
|
Thank you @mertalev for looking into this issue. Below is query: This is the result: Screenshot of the result, you can see it takes 1s 613ms |
|
It looks to me like the slowest parts are the filtering for |
|
It looks like this query is a lot more complex than it needs to be. It ultimately just returns Lastly, since pagination is implemented with LIMIT/OFFSET, the query is executed many many times since the mobile app fetches each page during sync. This part is harder to optimize, but the query being faster should still help a lot. Can you run this to compare the performance? CREATE INDEX IF NOT EXISTS idx_asset_file_created_at ON assets ("fileCreatedAt");
SELECT "AssetEntity"."id" AS "AssetEntity_id", "AssetEntity"."fileCreatedAt" AS "AssetEntity_fileCreatedAt"
FROM "assets" "AssetEntity"
WHERE "AssetEntity"."ownerId" = 'c8247244-cb7e-405d-970c-6bf5c34a8cb3' AND
"AssetEntity"."isVisible" = 't' AND
"AssetEntity"."updatedAt" <= '2024-02-17 07:14:14.296-08'
ORDER BY "AssetEntity_fileCreatedAt" DESC, "AssetEntity_id" ASC
LIMIT 5000 OFFSET 215000; |
|
I have a correction, After shutdown all my Unraid Immich background jobs (this morning videos encoding background job was running.) This is EXPLAIN ANALYZE: Result:
|
|
That's interesting, it was still doing a seq scan for me until I added the index so I'd expect it to make a difference. Overall though I think this is probably the best this query's going to get until we switch to cursor-based pagination. Switching to that would mean instead of running the query each time to get the next page, the query just continues from where it left off. |
|
I am doing complete "Building the timeline", I uninstalled the Immich Android app, reinstall the latest [v1.94.1], login 1. Log every SQL query attached (Start from Login, until I stop video recording recording) 2. Video recording of the android "Build the timeline" (record start right after login) 3. Check the Android Immich app log: Android app Error log Detail: Error while getting remote assets: ApiException 400: HTTP connection failed: GET /asset (Inner exception: Connection closed before full header was received) #0 IOClient.send (package:http/src/io_client.dart:90) FROM Stack TRACES I hope from the SQL query log, you can find more problems with the database query |
|
I'm not very familiar with how the app sync works, but it might be fetching 5000 IDs (with the query you posted) and querying the server for the assets of those IDs. |
|
Also thanks for sharing the query logs! That will be very helpful |
|
Oh, I think this is actually something the ORM is doing. It gets the IDs with one query and does a second query for the other fields. |
|
More error log reported by Android App
|
IF ORM is doing from Client app, it is very large query data need to send over the network. I hope there is way to optimize it, since the same ID was return from the paging, any additinal data, should be query along the first paging and send over the network at the same time, to prevent secondary query by giving it all 5000 IDs back to the server (which just returned from the server). Sorry, I am very new to Immich library, but I do a programmer, some of the things I say, may not be accurate |
|
Oh wow. I don't know anything about Isar (the local database in the app), so maybe one of the mobile devs can comment on what's happening there.
The new query won't have this issue since it gets all the data in one pass. |
|
For the Flutter developer, please check out this link, it may explain about the Isar DB Full exception, and possible solution: |
|
Thanks for the link! I think the way the mobile app syncs is by fetching all the assets from all pages before adding them to the local DB. With a large number of assets, this is probably too much for the database to handle in one transaction. Syncing one page at a time should avoid this issue. |
800,000 Assets need to Isar upsert 800,000/5000 = 160 times, If Isar 160 write is a performance issue, please consider adjusting 5000 per page, maybe increase it to 10000, or even higher. Not sure how 5000 per page was decided |
|
We should be able to increase it. That would also help with the fact that the query is run for each page: a page of 10000 reduces the number of queries from 160 to 80. |
|
Google Chrome Browser and Microsft Edge Web Browser, clicking on the timeline (jump to different date) will freeze up the browser, I couldn't find any log to help fixing this issue. I hope developer could find a way to test with 1M+ assets Immich server to reproduce this issue |
|
We're planning on adding OpenTelemetry integration throughout the server in the future. That will make it easier to identify these kinds of performance issues. |
The bug
Love Immich
I have 800,000+ Photos and Videos, but the performance is unusable. Mobile app never able to finish "Building the timeline". It appears this query takes too long. My Unraid server are Intel 13th Gen I7. I run query manually, following query take 1 to 2 second to finish. I hope all database query should be optimized
The OS that Immich Server is running on
Unraid
Version of Immich Server
v1.94.1
Version of Immich Mobile App
v1.94.1
Platform with the issue
Your docker-compose.yml content
UnRaidYour .env content
Reproduction steps
Additional information
No response
The text was updated successfully, but these errors were encountered: