Slow performance on bulk insert
Nobody has claimed this yet.
- Dominant language
- C
- Stars
- 9.2k
- Forks
- 1.2k
- Avg merge
- 19m
- Merged PRs (30d)
- 4
Description
Bulk inserts appear to take quadratic time over the number of statements being processed. Using v1.14.16, I find that a bulk insert that finishes quickly for the sqlite3 shell does not finish at all when run in golang.
My supposition is that this is because the sqlite3_prepare_v2 function returns a pointer to the remaining unprocessed sql, which we then copy back into a Go string. On the next run of the loop in exec(), we do another roundtrip of copying strings Go->C->Go. By contrast, the sqlite3 shell performs the same task without copying the underlying buffer, and finishes significantly faster.
A representative stack trace shows indicates time spent in runtime.memmove():
runtime.memmove()
src/runtime/memmove_amd64.s:432 +0x513 fp=0xc0005db3d0 sp=0xc0005db3c8 pc=0x19fca13
github.com/mattn/go-sqlite3._Cfunc_CString({0xc00a3d6001, 0x9254ac8})
_cgo_gotypes.go:200 +0x7b fp=0xc0005db400 sp=0xc0005db3d0 pc=0x27ee11b
github.com/mattn/go-sqlite3.(*SQLiteConn).prepare(0xc0010ca000, {0xb2ff30?, 0xc00021a008?}, {0xc00a3d6001?, 0x0?})
rh/external/com_github_mattn_go_sqlite3/sqlite3.go:1805 +0x5e fp=0xc0005db490 sp=0xc0005db400 pc=0x27fff3e
github.com/mattn/go-sqlite3.(*SQLiteConn).exec(0x0?, {0xb2ff30, 0xc00021a008}, {0xc0177da000?, 0x0?}, {0x3433fd0, 0x0, 0x0})
rh/external/com_github_mattn_go_sqlite3/sqlite3.go:853 +0x99 fp=0xc0005db5d8 sp=0xc0005db490 pc=0x27f9939
github.com/mattn/go-sqlite3.(*SQLiteConn).ExecContext(0xc0005db6c8?, {0xb2ff30, 0xc00021a008}, {0xc0177da000, 0x9255f37}, {0x3433fd0, 0x0, 0xc0010ca000?})
sqlite3_go18.go:41 +0xbb fp=0xc0005db670 sp=0xc0005db5d8 pc=0x27edb5b
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the bulk-insert slowdown described in the issue, then inspect sqlite3.go around prepare (line 1805) and exec (line 853), along with sqlite3_go18.go. Trace the Go-to-C string handling highlighted by the stack trace and compare the runtime with the sqlite3 shell. Done means the representative bulk insert no longer shows quadratic behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, go, sqlite
- Domain
- backend, databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100