Mutliple databases open slow down

[delaneyj]

So I have a []*sqlite.Pool, that maps to a set sqlite databases acting as buckets (say 4 for this example). I have an example the can write sustained at 25k/s and thought a move to buckets that are keyed off a hash would speed things up further.

• The buckets are roughly equally distributed.
• They are independent (no ATTACH calls)
• The only sync is a sync.WaitGroup to make sure they are all written to before returning

Is there something inherent in how the driver works that would cause some global mutex or something to be shared? From what I'm seeing its actually 45% slower sustained and that doesn't make sense to me. Any thoughts?

[zombiezen]

What are the options you're passing to sqlite.OpenConn? And are these connections to different database files or the same database? Broadly speaking, each *sqlite.Conn is intended to be used by a single goroutine, so it doesn't need to synchronize. However, SQLite does use file-level locking, see:

• https://sqlite.org/wal.html for a description of concurrency when using write-ahead logging (aka WAL, default in this package)
• https://sqlite.org/lockingv3.html for a description of concurrency when WAL is disabled

EDIT: This ended up being unnecessary use of the shared cache flag on opening. See below replies for details.

[delaneyj]

It's a set of 4 separate (sqlite|wal|chm) files. Each talked to by a separate go routine using a separate sqlite.Pool (even though only one is talked to at a time and I did try setting the poolSize to 1 with no luck). If I put in 10,000,000 rows they all show up in the separate databases so its technically its working, just slow.. I have a possibly similar issue when using multiple databases prior with crawshaw's original driver. Even though they where separate files, and not using attach, had weird results from the driver. It was more about finding out if its something about the driver's approach or something I'm not understanding. Was going to try updating https://github.com/bvinc/go-sqlite-lite to latest SQLite and see if its happening there as well. Whether it exists there or not I guess I can report back here on my findings.

Currently using

openFlags := sqlite.OpenReadWrite |
		sqlite.OpenCreate |
		sqlite.OpenWAL |
		sqlite.OpenURI |
		sqlite.OpenNoMutex |
		sqlite.OpenSharedCache
		```

[zombiezen]

Maybe try without shared cache enabled? You shouldn't need it for the setup you're describing. It's also possible that you're saturating IOps on the disk you're writing to.

[delaneyj]

Yeah I tried with the other driver and easily getting over 300,000 writes a second so I don't think it's an IOP issue. However I haven't tried it yet with the shared cache in that example. Even with the different buckets I'd still want to have a pool for handling the servicing of the read side.

[delaneyj]

@zombiezen so it 100% was shared_cache causing issues. I think I just assumed shared_cache had to be on because issues existed in mattn's database/sql without it. So that's great news!