Show newer

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.

Show thread

It worked! Page load time dropped to 8ms.

I was confident that the SUM(LENGTH(chunk)) line was causing the latency.

Show thread

I tried removing the LENGTH() function and just hardcoding the size calculation to 1.

Show thread

I was able to reproduce the issue locally by uploading a 1.1 GB file. The page load time jumped to 9.3 seconds.

Show thread

I suspected that the problem was on the SUM(LENGTH(chunk)) line because it's the only one that's touching the file data.

Show thread

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.

Show thread

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

Show thread

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.

Show thread

I had an interesting time investigating a bug related to SQLite performance today, so I thought I'd share a thread.

A PicoShare user reported that it took 14.57 seconds to load their list of files.

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

I didn't recognize the benefit at first, but it's nice to have all of your test assertions fit a regular, recognizable pattern.

Show thread

Another cool golang idiom for writing consistent unit test code that I learned from the @litestreamio source.

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! 😆

RT @jdeanwallace
Introducing AdSlicer v0.2.0, the 2nd (major) public release 🎉

AdSlicer is a tool to help you keep track of your Facebook Ad optimizations & to visualize how each change affected your results 📈

This ver. removes the public demo, uses customer lingo & focuses on expressed pains

I never had a good way of letting people share large files with me. MegaDrop is decent, but I felt stupid using it for business communication.

Now, I've added that functionality to PicoShare. I can generate a guest link and anyone can upload to it without any signup process.

Here's what I got done this week:
* Reviewed TinyPilot hardware improvements
* Learned to build Debian packages
* Added guest uploading to PicoShare

In the last 24 hours, @Rapid_API has sent me three separate transactions, resulting in three separate PayPal fees. In one instance, the PayPal fee ate 34% of the payment (and that's after RapidAPI took 20%).

RapidAPI is the only platform I've seen that can't bundle payments.

RT @paulg
Confiscate urban space from cars and redistribute it to pedestrians.

Show older
Michael Lynch's Mastodon

Michael Lynch's personal Mastodon instance