SwiftPackageIndex-Server: DB reset too slow with PG 16 db

First seen in https://github.com/SwiftPackageIndex/SwiftPackageIndex-Server/pull/3278, using a PG 16 test db to run the tests makes them run really slowly (~6min vs 1.5min).

This is due to the function _resetDb, which TRUNCATEs all tables, taking much longer. I added

    let start = Date()
    defer { print("Resetting database took: \(Date().timeIntervalSince(start))s") }

to the start of the function and then ran the AnalyzeErrorTests (4 tests):

swift test --disable-automatic-resolution --filter AnalyzeErrorTests

With PG 13 we get

Resetting database took: 0.06859397888183594s
Resetting database took: 0.08555293083190918s
Resetting database took: 0.0502779483795166s
Resetting database took: 0.045484066009521484s
Resetting database took: 0.05070698261260986s

And with PG 16

Resetting database took: 0.3590719699859619s
Resetting database took: 0.44095301628112793s
Resetting database took: 0.3530939817428589s
Resetting database took: 0.34445297718048096s
Resetting database took: 0.3584989309310913s

i.e. about a factor of ~5-6 slower.

We can’t stay on PG 13 forever but we can’t accept a x5 regression in test speed, either. I’ve asked around and it looks like folks use a createdb strategy to create and restore db snapshots with a blank schema instead of truncating tables. That should solves this and might even be faster than the original version 🤞

Also notable: this test class has four tests, yet there are five log statements. For some reason we’re running reset one extra time per test class. (That’s because we call _resetDb recursively once in order to populate the table names cache.)

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 17 (16 by maintainers)

Most upvoted comments

Just to summarise the resolution of the issue on the PostgresNIO side:

  • Postgres changed their default auth algorithm from md5 to scram-sha-256 in version 14
  • PostgresNIO had a rather big performance problem in the scram-sha-256 implementation (fixed in version 1.26.0)
  • This led to the huge overhead in our tests
  • It was mainly a test issue, because our tests run new connections for each query essentially, paying the much larger cost for the faulty scram-sha-256 implementation each time
  • Using md5 auth in our tests with PG 16 fixes the performance issue

The problem is that I’m testing in slightly different ways.

The original figures from within our test suite are generated as follows:

  • spin up db with target Pg version
  • run the test 10 times via
for _ in {1..10}; do
    swift test --skip-build --disable-automatic-resolution --filter AnalyzerTests.test_analyze | grep "Elapsed:"
done
  • the test’s set up method I log the run time for createSnapshot
  • repeat this with a different target Pg version

In the perf test project the procedure is

  • spin up two dbs with Pg version 13 on port 6432 and Pg 16 on port 7432
  • run the test 10 times via
    for port in [6432, 7432] {
        print("Testing DB on port \(port)")
        for _ in (1...10) {
            try await createSnapshot(port: port, original: "spi_dev", snapshot: "snapshot")
        }
    }

The crucial difference seems to be that in the first test, each timing of createSnapshot is run on a new db connection. In the perf project, subsequent iterations are run on an existing connection. That difference is actually visible in the plot where the first timings are higher.

Now the crucial test is the first one, because that’s what we do (and have to do) in our setup: we’re creating the snapshot with a new db connection. (I guess we could keep the connection alive but really this seems like an upstream regression.)

I’ll adjust the standalone perf test to reflect this different test scenario.

In terms of moving forward, I think we’ll want to go ahead and move our CI to use PG 16 (i.e. merge #3278 with a slight tweak, see below). The regression is ~18m → 22m. That’s not great but we should be testing the same db version as prod and that’s the price to pay.

For local test running, we could stay on 13 for a moment longer until we figure out (and hopefully fix) the underlying issue.