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)
Just to summarise the resolution of the issue on the PostgresNIO side:
The problem is that I’m testing in slightly different ways.
The original figures from within our test suite are generated as follows:
createSnapshotIn the perf test project the procedure is
The crucial difference seems to be that in the first test, each timing of
createSnapshotis 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.