Does calling LENGTH cause SQLite to read the full BLOB data into memory? That would certainly explain why it took 9 seconds to calculate the length of a 1.1 GB file.
It worked! Page load time dropped to 8ms.
I was confident that the SUM(LENGTH(chunk)) line was causing the latency.
I was able to reproduce the issue locally by uploading a 1.1 GB file. The page load time jumped to 9.3 seconds.
I suspected that the problem was on the SUM(LENGTH(chunk)) line because it's the only one that's touching the file data.
Storing file data in SQLite leads to odd behavior like this, so I suspected the bottleneck was somewhere in my SQLite logic.
Here's the code to retrieve file metadata. I keep one table for file metadata and another table for file data, broken into 32 KB chunks.
One of the wacky architectural choices I made with PicoShare was to store all file data in SQLite as BLOB data. This is an unusual choice, as most apps store file uploads on the filesystem. But putting it in SQLite means I get cloud replication "for free" with @litestreamio
The first thing I noticed was that they had uploaded a 1.24 GB file. The files I use with PicoShare tend to be images and documents in the 5-50 MB range, so 1.24 GB is larger than I've tested with.
Here's what I got done this week:
* Led TinyPilot's monthly dev meeting
* Cut release candidate for next TinyPilot Pro release
* Added private notes to PicoShare
https://whatgotdone.com/michael/2022-04-29
I didn't recognize the benefit at first, but it's nice to have all of your test assertions fit a regular, recognizable pattern.
RT @phil_eaton
I wrote up my little adventure yesterday trying to understand how HTML event attribute values are evaluated.
Thanks @simonw and @cldellow for helping me find the way! 😆
https://datastation.multiprocess.io/blog/2022-04-26-event-handler-attributes.html
RT @zekjur
New blog post!
My upgrade to 25 Gbit/s Fiber To The Home 🚀 😍
https://michael.stapelberg.ch/posts/2022-04-23-fiber7-25gbit-upgrade/
Here's what I got done this week:
* Reviewed TinyPilot hardware improvements
* Learned to build Debian packages
* Added guest uploading to PicoShare
https://whatgotdone.com/michael/2022-04-22
Solo developer. Lover of unit tests. Builder of TinyPilot. ex-Google, ex-Microsoft