Hacker News Reader

Comment by johnboiles | Hacker News Reader

I've been trying to use SQLite3 in an embedded (ESP32 -- dual core 240MHz w/ qspi NOR flash) project and the performance has been surprisingly bad. Seems like the minimum amount of time to make a query (even for example, selecting from a table that does not exist, takes a minimum of 5ms). For a simple table with 10 short columns I'm getting 10s of milliseconds per record to SELECT. I can parse whole JSON files with the same data to RAM faster then I can query SQLite which seems wrong. SQLite is performing ~10x worse than I'd expected it would.

Anyone had experience running SQLite on low-power embedded platforms? Is this expected performance?

View on HN

In the embedded world the specifics often outweigh the general. Specifics like - what storage are you using? What clock does it run at? What page size are you using (you'd want to pass this to sqlite for best results)? What locks are you using? Is there a 1mS undocumented sleep on startup to allow some peripheral to stabilise that accidentally got pulled in to the main code path (has happened to me - the initialize function didn't set the initialized flag)?

I'd expect closer to 5uS than 5mS, but I'm not that familiar with the ESP32 devices

Is your 5us number from experience? Have you run SQLite on embedded before? Just hoping to hear some real world performance numbers for any hardware remotely similar!

I have run sqlite on a flash chip before (no filesystem, I suppose sqlite _was_ the filesystem) but I never took benchmarks. It was a 200MHz processor as well and it was basically fast enough out of the box that I never spent time on it. For 5uS response you have something like a thousand cycles to work with so any issue is likely storage related. I'd expect writes to be a bottleneck for example

I'm not getting 5us on gigahertz-clock CPUs. ESP32 isn't exactly demon of speed either compared to anything SQLite usually runs on

Could you share some source code around your implementation? Selecting from a non-existing table should be a very quick operation. I suspect you might be doing something like creating a new connection per command if you are seeing this behavior.

There aren't connections in SQLite like there are for network-connected DBs (eg MySQL). My simple code is like this:

  sqlite3_initialize();
  sqlite3 *db;
  sqlite3_open("/data/sqlite.db", &db);
  char *err = 0;
  sqlite3_exec(&db, "DROP TABLE table_name;", NULL, NULL, & err); // This takes ~5ms even when table_name does not exist

> There aren't connections in SQLite like there are for network-connected DBs (eg MySQL)

The handle named `db` in your example is as expensive to create as a connection in the relative sense and should be optimized the same way in your application. It may be less expensive than a network database connection, but its still very expensive relative to any subsequent queries. The database file is opened, its first page is read, its schema is parsed from text (!), some memory is pre-allocated and so on every time you make that call.

The sqlite3_exec call invokes the parser and query optimizer every time it is called. A better benchmark would be to compile a valid statement (sqlite3_prepare). The unit under test should just be sqlite_bind() for relevant parameters followed by sqlite_step(), and maybe (but not necessarily) sqlite_reset().

If you have the RAM to load up a toy dataset, what happens if you utilize a :memory: database instead of disk? If the performance is the same, you know the problem exists outside of the storage.

Oh I didn't realize you could just tell it :memory:. That's a great tip

Have you benchmarked the storage? That might be the bottleneck.

Yeah seems like it would _have_ to be storage related but my profiling tools are limited. I've run Doom on this same processor/flash combo and loading assets from the WAD files is on the order of 1ms and I can run Doom smoothly at 70fps. Not very scientific but that's part of the reason I was surprised by how slow SQLite is running.

I wonder if it could be doing multiple small writes on every operation -- I could definitely imagine small writes getting very costly, especially with wear-leveled file systems that might be moving around pages on even small writes.

This depends entirely on how naive the sqlite implementation is - without exact implementation details and register values for the QSPI peripheral, it's hard to remote diagnose something like that.

Have you used a digital logic analyzer to figure out how long reading the page takes, how many pages SQLite reads for that query, and how much work SQLite has to do before it can fetch the next page? I haven't seen any ESP32 SQLite ports that can eagerly fetch pages, even though it's relatively simple to do with DMA (but would require some significant changes to SQLite because it's designed to be ignorant of OS preemption which is not the case in most RTOSes).

Reply on news.ycombinator.com