Benchmarking performance #247
Replies: 12 comments 81 replies
-
|
Btw, here's the SQL for the various queries: const SQLForQueryCorrelatedAggregation = `
SELECT
id,
title,
(SELECT COUNT(*) FROM comments WHERE post_id = posts.id) as comment_count,
(SELECT AVG(LENGTH(content)) FROM comments WHERE post_id = posts.id) AS avg_comment_length,
(SELECT MAX(LENGTH(content)) FROM comments WHERE post_id = posts.id) AS max_comment_length
FROM posts`
const SQLForQueryCTE = `
WITH day_totals AS (
SELECT date(created) as day, COUNT(*) as day_total
FROM posts
GROUP BY day
)
SELECT day, day_total,
SUM(day_total) OVER (ORDER BY day) as running_total
FROM day_totals
ORDER BY day`
const SQLForQueryCTERecursive = `
WITH RECURSIVE dates(day) AS (
SELECT date('now', '-30 days')
UNION ALL
SELECT date(day, '+1 day')
FROM dates
WHERE day < date('now')
)
SELECT day,
(SELECT COUNT(*) FROM posts WHERE date(created) = day) as day_total
FROM dates
ORDER BY day`
const SQLForQueryGroupByAggregation = `
SELECT
strftime('%Y-%m', created) AS month,
COUNT(*) as month_total
FROM posts
GROUP BY month
ORDER BY month`
const SQLForQueryJSON = `
SELECT
date(created) as day,
SUM(json_extract(stats, '$.lorem')) as sum_lorem,
AVG(json_extract(stats, '$.ipsum.dolor')) as avg_dolor,
MAX(json_extract(stats, '$.lorem.sit')) as max_sit
FROM posts
GROUP BY day
ORDER BY day`These results used a database with 1,000 posts and 25 comments per post. The post and comments both had 100 "paragraphs" of content. (The correlated aggregation query is the only one that uses comments; the rest only use posts.) |
Beta Was this translation helpful? Give feedback.
-
|
This is great! I've been meaning to do a benchmark along the same lines, but I'm glad someone beat me to the punch. One less thing to do. Also, thanks for doing a set of benchmarks that are more representative of interactive use of SQLite, instead of focussing on raw insert/query performance which is more useful in the "using SQLite to exchange datasettes" approach. Looking at the numbers, especially memory, I suspect most of the difference is down to One other interesting thing here could be to add the https://github.com/zombiezen/go-sqlite driver. This one uses the ModernC transpile under the hood, but does not use the provided |
Beta Was this translation helpful? Give feedback.
-
|
Sorry, I was out of town over the weekend!
What I find curious is that memory allocation doesn't seem to have much of an impact on performance. The populate category is a dramatic example; the driver version of ncruces is ~5% faster — probably within the margin of error — but it allocates 500% more memory. Hmmm.
Yes, that was on my to do list, and I've done it. Generally speaking, your suspicion was correct — performance is about the same, modernc uses more memory. |
Beta Was this translation helpful? Give feedback.
-
|
Btw, I hope to publish the repo tomorrow. I have a few more tweaks I'd like to add today, and tomorrow I'd like to write some documentation. |
Beta Was this translation helpful? Give feedback.
-
|
Few notes. Maybe call It's expected that for pure CPU stuff (complex queries) Wasm is slower; Once you push the repo I'll run profiling to figure out if there are any allocations I can avoid; but there are a few almost insurmountable challenges. It shouldn't be much worse than other I know of a few cheats (not necessarily from these drivers), but I'd rather not go there. Like for instance |
Beta Was this translation helpful? Give feedback.
-
|
Here's a quick version of original (the tagged version); modified (this morning's change); and simpler (this morning's change + simpler query). These changes are particularly dramatic: Here's everything: |
Beta Was this translation helpful? Give feedback.
-
Not sure what you mean; all of the results I've been showing are for driver, not direct? |
Beta Was this translation helpful? Give feedback.
-
Ah, yes, now I understand. :-) But the real problem seems to be that recursive CTEs are particularly expensive to reset and/or step? The times for "modified" above just change the query and leave everything else in place, including the double calls. That one line change (combined with your change from this morning) results in a 2x average overall improvement — and far more on certain benchmarks. That's pretty remarkable. |
Beta Was this translation helpful? Give feedback.
-
|
Right, my point is that I probably didn't benchmark this, so I thought I was making an improvement, and failed. This, by the way, is the reason good benchmarks are important. I don't want to be over fitting to the other Those two lines are all over the codebase, I'm all for optimizing realistic usages, as long as I can get sensible, testable semantics as well, playing nice with the Go scheduler, etc. |
Beta Was this translation helpful? Give feedback.
-
|
I tried the most recent change. It does resolve the performance issue. The difference from v0.24.1 is dramatic. (Again, a caveat: these are quick benchmarks with a single run, etc.) I'm going to open a separate discussion for context cancellation. I read a lot of code over the weekend, and I have some thoughts. It might be easiest to express the thoughts in a pull request, to give us something concrete to talk about, but I'll start a discussion. To be clear: I don't think that what I have in mind would result in (noticeably) better performance; rather, it would just simplify the code. |
Beta Was this translation helpful? Give feedback.
-
|
I'd be curious to hear if golang/go#67546 would help at all. I think it'd be great for at least pgx to have that support so I'm hoping the related CL makes it in for 1.25. |
Beta Was this translation helpful? Give feedback.
-
|
It might, yes, for somethings at least. But it'd take some effort/imagination, so I'll get there once it's the standard. From an initial look, that helps with features more than raw performance. But it might be very useful to bridge the gap between SQLite's dynamic typing and Go. I went the opposite way and added So I'm actually a bit surprised my driver generates more garbage than others, but a benchmark I can profile will help there. |
Beta Was this translation helpful? Give feedback.
-
|
In case it's of interest, here's a table comparing the compile-time options of each of the implementations.
|
Beta Was this translation helpful? Give feedback.
-
|
Ah, and here are the compile-time options from the |
Beta Was this translation helpful? Give feedback.
-
|
There's now a repo at https://github.com/michaellenaghan/go-sqlite-bench. I haven't written any docs yet. I will; there's lots to explain in terms of goals, rules, etc. As far as the benchmarks themselves go: I've made various changes. One important change is that I used to run many benchmarks in parallel. I think that made results hard to interpret. Now only the "ReadOrWrite" benchmarks run in parallel; everything else runs in a The results below were generated using: make benchstat-by-category BENCH_BIG=1 BENCH_SLOW=1To run a subset of implementations, use make benchstat-by-category BENCH_BIG=1 BENCH_SLOW=1 TAGS="ncruces_direct ncruces_driver"The first tag listed becomes the baseline in the benchstats. For example, to make everything relative to tailscale, list Benchmarks tee to Benchstat runs off of the Tests tee to The tests log useful info like compile-time options and the values of selected I haven't had time to look into the "why" of any of the differences. I'd like to add On to the results... BaselinePopulateReadWriteQuery/CorrelatedQuery/GroupByQuery/JSONQuery/NonrecursiveCTEQuery/OrderByQuery/RecursiveCTE |
Beta Was this translation helpful? Give feedback.
-
|
The performance of |
Beta Was this translation helpful? Give feedback.
-
|
It doesn't look like you're calling You do it for ncruces_direct: https://github.com/michaellenaghan/go-sqlite-bench/blob/d53efde5fa7a5b28cdcfcd64ead1344c7739cf83/gsb_ncruces_direct.go#L36. But I don't see the equivalent in https://github.com/michaellenaghan/go-sqlite-bench/blob/d53efde5fa7a5b28cdcfcd64ead1344c7739cf83/gsb_ncruces_driver.go. If |
Beta Was this translation helpful? Give feedback.
-
|
I'll add an explicit call, but Having said that, the lack of an explicit call may explain an issue I thought I was seeing with ncruces, an issue that looked like a race condition in parallel tests. That's why I added the get and close calls in the first place! I'll test that too. |
Beta Was this translation helpful? Give feedback.
-
|
The race you're experiencing from parallel tests is probably due to the fact that creating a new database and converting it to WAL mode (databases are always created in rollback mode) is kinda racy. I'm not sure if this is due to something wrong on my end (my VFS), or a property the SQLite locking protocol. I had to do this to fix some flakes in my parallel tests (though they mostly affected BSD). |
Beta Was this translation helpful? Give feedback.
-
|
What you're saying makes sense, but a) the database always needs to be "prepared" for use before any tests start — so one connection is always opened before the rest anyway — and b) In any event, I don't think my workaround is needed anymore — and I never saw this is a major problem, just a quirk that I didn't understand. |
Beta Was this translation helpful? Give feedback.
-
|
The current state on head should address most issues. The only remaining one that I see could potentially be fixed, is unnecessary allocations in These happen because Where my driver allocates more in It's not clear how to fix this without breaking some abstractions, or not automatically decoding timestamps, which IMO is a nice feature. This feature means that, if you bind any of the following types into a cell, you can always scan them back with the same type, regardless of the declared the type of the column: |
Beta Was this translation helpful? Give feedback.
-
|
golang/go#67546 will help a bit if it gets merged. Otherwise, it's possible to improve matters by breaking some abstractions in I can either change it, or add an overload that receives a callback (?) that decides whether to allocate the Or I could go super hack and start addding unexported |
Beta Was this translation helpful? Give feedback.
-
|
I just had an interesting idea. I could add a Well, it turns out there is a way, which I could use for this, and I think all the other exiting APIs, consistently: C-strings returned by SQLite are Wdyt? Not sure if this is ingenious or batshit crazy... |
Beta Was this translation helpful? Give feedback.
-
|
I don't understand the underlying issues here, but it sounds like you're suggesting a heuristic based on the value of the last byte, and it sounds like there would be no way to override the heuristic. I would prefer to opt in to something like that rather than opt out — or, worse, not being able to opt out at all.
Maybe that's the answer there too? Not just because of the performance/memory implications, but because the heuristic might fail — or fail to meet the needs of a particular use case. I've had problems like that in Excel, where it tries to help by doing something automatically but just creates more work for me. |
Beta Was this translation helpful? Give feedback.
-
|
You have 2 options to retrieve
Similar for
Then you have I'm proposing I'm further proposing that the |
Beta Was this translation helpful? Give feedback.
-
|
About timestamps, With the current scheme:
For
The only surprising behavior here, besides some additional allocs we can optimize away, is that some strings (those that exactly match I happen to think this is a good heuristic, @daenney seems to agree. |
Beta Was this translation helpful? Give feedback.
-
|
So I guess #256 is what I'm proposing. |
Beta Was this translation helpful? Give feedback.
-
|
That looks workable to me. The most likely case for timestamps in a database is either RFC3339(Nano) text or int64 epoch counters in my experience. |
Beta Was this translation helpful? Give feedback.
-
|
So #256 improves the allocation issue to my satisfaction. It still allocates twice as much as |
Beta Was this translation helpful? Give feedback.
-
|
Apparently there's also https://pkg.go.dev/modernc.org/sqlite-bench2 for database/sql drivers and https://pkg.go.dev/modernc.org/sqlite-bench for non-database/sql drivers. |
Beta Was this translation helpful? Give feedback.
-
|
Seems to be the same benchmark as I'm a bit sceptical, because I can consistently repro the concurrent issues in Anyway, I've profiled |
Beta Was this translation helpful? Give feedback.
-
|
Yeap, I agree. I looked at the results of those benchmarks and I'm honestly a bit surprised by the numbers. |
Beta Was this translation helpful? Give feedback.
-
|
It's plausible that the concurrency issues were due to the mutex implementation. It's surprising that they didn't affect I also don't really want to get into a competition. I mostly don't love transpilation because it produces something akin to a platform specific blob: it's Go source code, but it's not so easy to inspect, full of unsafe code, and very platform specific. The transpiler is getting better (and libc probably as well), so I know it's become easier to compile your own SQLite, but still harder IMO than just using My version uses off the shelf tools to compile SQLite ( One advantage of their approach is memory usage/copies: not using a sandbox allows you to share memory more effectively. It also allows you to corrupt memory more easily. With wazero it's possible to corrupt the C heap, and even the C stack, but it's much harder to corrupt the C call stack, the Go stack and heap, or to do so across connections. There's a price to pay though. |
Beta Was this translation helpful? Give feedback.
-
|
Released In the future there might be a small regression: the next time I build SQLite, I'm considering enabling I've tried it and it didn't seem to be catastrophic. I used I'll pour over the code and check how useful it is for memory safety, which IMO, beats a few percentage points. |
Beta Was this translation helpful? Give feedback.
-
|
@michaellenaghan many of the allocs in the [DB].SetMaxIdleConns(runtime.GOMAXPROCS(0))
[DB].SetMaxOpenConns([MAX])The reason is that the queries you're benchmarking are so fast, that the default for idle connections (2) is insufficient to deal with the churn. So the pool is opening and closing connections left and right, and that's what you're actually benchmarking (opening a connection for every e.g. 4 queries). Also 256 connections is a lot. |
Beta Was this translation helpful? Give feedback.
-
|
I added eatonphil. Sometimes faster than tailscale, sometimes slower. When it's slower it's because it exhibits the same kind of concurrency issue as modernc and mattn. |
Beta Was this translation helpful? Give feedback.
-
|
I can't figure out why two CGO implementations, eatonphil and mattn, and all of the modernc implementations, have concurrency problems in benchmarks like this one: but not in benchmarks like this one: while one CGO implementation, tailscale, and one WASM implementation, ncruces, do not. Here's the SQL for the first benchmark: WITH day_totals AS (
SELECT date(created) as day, COUNT(*) as day_total
FROM posts
GROUP BY day
)
SELECT day, day_total,
SUM(day_total) OVER (ORDER BY day) as running_total
FROM day_totals
ORDER BY dayHere's the SQL for the second benchmark: WITH RECURSIVE dates(day) AS (
SELECT date('now', '-30 days')
UNION ALL
SELECT date(day, '+1 day')
FROM dates
WHERE day < date('now')
)
SELECT day,
(SELECT COUNT(*) FROM posts WHERE date(created) = day) as day_total
FROM dates
ORDER BY dayWhat could be causing the difference? (The compiler options and pragmas are available in the tests/ directory, but so far I haven't found anything that accounts for the difference.) It doesn't really matter, but I'm curious. And maybe there's an interesting lesson in the answer? |
Beta Was this translation helpful? Give feedback.
-
|
The README file now contains the benchmark results and various implementation details (compile-time options, pragmas, SQLite version). I still need to document more things, but you no longer need to jump in and out of files to review everything. |
Beta Was this translation helpful? Give feedback.
-
I tracked the difference down to this: #cgo CFLAGS: -DSQLITE_DEFAULT_MEMSTATUS=0For eatonphil, for example, here's the before (without the and here's the after (with the So:
"Faster" here means parallel performance, not sequential. But, you know, wow; not bad for a one line change! |
Beta Was this translation helpful? Give feedback.
-
|
That probably increases While I don't wrap Might be a good tip for |
Beta Was this translation helpful? Give feedback.
-
|
This HN thread lead to #257 which seems a nice improvement, though, overall it's not measurable on speedtest. Can't believe this'll lead me to reimplement libc. 🤷 |
Beta Was this translation helpful? Give feedback.
-
|
Yes, which is nice: that doesn't cause a regression, and improves “undefined” behavior. For the most part, this means “use after close” of prepared statements, blobs, backups, should now more consistently return For connections, I tear down wazero on close so it shouldn't matter much, but if mess up pointers that I can't use in a type safe manner, this should catch mistakes. |
Beta Was this translation helpful? Give feedback.
-
|
Most checks are just a The interpreter would probably gain from me doing the check on Go before the call, but I may forget. I'm sure the SQlite developers are more consistent. |
Beta Was this translation helpful? Give feedback.
-
It seems the new The combination of |
Beta Was this translation helpful? Give feedback.
-
|
OK, so #262 "fixes" It also warms my heart that I wasn't crazy, most allocs are 16 byte aligned after all. I think this covers everything SQLite needs. |
Beta Was this translation helpful? Give feedback.
-
|
Nope, this never ends: #263. 200 LoC, dragons at every corner. 🙄 |
Beta Was this translation helpful? Give feedback.
-
I've created a benchmark for Go SQLite drivers.
At the moment, it includes ncruces, modernc, mattn, and tailscale.
For ncruces it benchmarks both "direct" and "driver" performance.
(All of the "driver" implementations use exactly the same code for everything other than opening the database.)
I hope to push the repo next week, but in the meantime here are some results.
I've broken them into sections:
"Baseline" measures the performance of a "SELECT 1" query.
"Populate" measures populating a database of posts and comments with no transactions; one transaction for the entire population; and a transaction per post + comments.
"ReadWrite" measures various combinations of reading and writing posts and comments, with and without transactions.
"Query" measures various complicated queries.
TL;DR
Baseline
Populate
ReadWrite
Query
Correlated Aggregation
CTE
CTE (Recursive)
Group By Aggregation
JSON
Beta Was this translation helpful? Give feedback.
All reactions