What could cause a sqlite application to slow down over time with high load?

Question

I'll definitely need to update this based on feedback so I apologize in advance.

The problem I'm trying to solve is roughly this.

The graph shows Disk utilization in the Windows task manager. My sqlite application is a webserver that takes in json requests with timestamps, looks up the existing entry in a 2 column key/value table, merges the request into the existing item (they don't grow over time), and then writes it back to the database.

The db is created as follows. I've experimented with and without WAL without difference.

createStatement().use { it.executeUpdate("CREATE TABLE IF NOT EXISTS items ( key TEXT NOT NULL PRIMARY KEY, value BLOB );") }

The write/set is done as follows

try {
    val insertStatement = "INSERT OR REPLACE INTO items (key, value) VALUES (?, ?)"
    prepareStatement(insertStatement).use {
        it.setBytes(1, keySerializer.serialize(key))
        it.setBytes(2, valueSerializer.serialize(value))
        it.executeUpdate()
    }
    commit()
} catch (t: Throwable) {
    rollback()
    throw t
}

I use a single database connection the entire time which seems to be ok for my use case and greatly improves performance relative to getting a new one for each operation.

val databaseUrl = "jdbc:sqlite:${System.getProperty("java.io.tmpdir")}/$name-map-v2.sqlite"
if (connection?.isClosed == true || connection == null) {
    connection = DriverManager.getConnection(databaseUrl)
}

I'm effectively serializing access to the db. I'm pretty sure the default threading mode for the sqlite driver is to serialize and I'm also doing some serializing in kotlin coroutines (via actors).

I'm load testing the application locally and I notice that disk utilization spikes around the one minute mark but I can't determine why. I know that throughput plummets when that happens though. I expect the server to chug along at a more or less constant rate. The db in these tests is pretty small too, hardly reaches 1mb.

Hoping people can recommend some next steps or set me straight as far as performance expectations. I'm assuming there is some sqlite specific thing that happens when throughput is very high for too long, but I would have thought it would be related to WAL or something (which I'm not using).

Answer 1

I have a theory but it's a bit farfetched.

The fact that you hit a performance wall after some time makes me think that either a buffer somewhere is filling up, or some other kind of data accumulation threshold is being reached.

Where exactly the culprit is, I'm not sure.

So, I'd run the following tests.

  // At the beginning
  connection.setAutoCommit(true);

If the problem is in the driver side of the rollback transaction buffer, then this will slightly (hopefully) slow down operations, "spreading" the impact away from the one-minute mark. Instead of getting fast operations for 59 seconds and then some seconds of full stop, you get not so fast operations the whole time.

In case the problem is further down the line, try

PRAGMA JOURNAL_MODE=MEMORY

PRAGMA SYNCHRONOUS=OFF   disables the rollback journal synchronization

(The data will be more at risk in case of a catastrophic powerdown).

Finally, another possibility is that the page translation buffer gets filled after a sufficient number of different keys has been entered. You can test this directly by doing these two tests:

1) pre-fill the database with all the keys in ascending order and a large request, then start updating the same many keys.

2) run the test with only very few keys.

If the slowdown does not occur in the above cases, then it's either TLB buffer management that's not up to the challenge, or database fragmentation is a problem.

It might be the case that issuing

PRAGMA PAGE_SIZE=32768

upon database creation might solve or mitigate the problem. Conversely, PRAGMA PAGE_SIZE=1024 could "spread" the problem avoiding performance bottlenecks.

Another thing to try is closing the database connection and reopening it when it gets older than, say, 30 seconds. If this works, we'll still need to understand why it works (in this case I expect the JDBC driver to be at fault).

Answer 2

First of all, I want to say that I do not use exactly your driver for sqlite, and I use different devices in my work. (but how different are they really?)

From what I see, correct me if im wrong, you use one transaction, for one insert statement. You get request, you use the disc, you use the memory, open, close etc... every time. This can't work fast.

The first thing I do when I have to do inserts in sqlite is to group them, and use a single transaction to do it. That way, you are using your resources in batches.

One transaction, many insert statements, single commit. If there is a problem with a batch, handle the valid separately, log the faulty, move the next batch of requests.