Multiple outbound threads to one Sqlite DB

[Mike Strout]

I have a need to send out GT1, IN1, and IN2 segments in outbound lab result messages, but our lab system, while it does take in these segments, will not send these segments out in results or charges.

To deal with this, we grab these segments out of the various inbound message threads and throw them into a Sqlite DB. Then when the outbound message hits Cloverleaf, we pull the segments from the DB and lvarpop them back into the message with a Tcl script. This has worked pretty well for some time, but things are getting more complicated as we need to capture this info from more and more inbound feeds and push it to the same db.

My question is, is it a problem to have several feeds passing messages to a single Sqlite DB? Also, we are about to switch pulling this data from the DB real-time as results go through the engine instead of pulling the data based on batch charges. Will I run into contention issues with several thousand writes to the DB across four threads and 2-400 reads per day from the DB from one thread?

I don’t even know where to look other than Clovertech for design guidance on things like this.

Topic

[Jim Kosloskey]

Mike,

Here is a mail list for SqlLite Users: http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Might be some assistance there.

I would think you would need to also consider the I/O subsystem on your O/S. If it is already saturated or is about to be saturated, I don’t think it will matter much as to whether the DB will support that volume or not.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[David Coffey]

You could create one thread specifically designed to instantiate data in the SQLite database,  All threads needing to store data in the SQLite database would route to it.   This would serialize the feed and (hopefully!) remove potential contention issues.

[Jeff Dinsmore]

Mike,

SQLite’s performance should be able to satisfy your needs.

We have many SQLite databases that we’re writing thousands of records to per day.

Our DBs are used in real-time interface applications that access these databases – some of them 300-400 MB in size – many thousands of times per day – all day, every day.  A few hundred reads per day should be trivial.

You don’t say how you’re retrieving the info, but I’d guess you’re storing encounter number or something like that along with each of the stored segments.  Assuming so, make sure you create an index on your key lookup columns.  That will increase search performance dramatically.

For databases that are accessed by multiple readers, I’ve recently started to use SQLite’s write ahead logging option (WAL) rather than the standard rollback journaling.  It handles concurrent access much better.  Below is a reference page discussing WAL.

<a href="https://www.sqlite.org/wal.html&#8221; class=”bbcode_url”>https://www.sqlite.org/wal.html

To solve any write concurrency issues, you might consider feeding all of the messages you need to write to the DB into a single multi-server thread that can then write them sequentially to the DB.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Rob Lindsey]

I was going to mention about the write vs read issue but I see others brought it up.  You can have multiple reads happening from the DB at one time but only a single write, unless you are using the -wal option.  The exclusive lock might cause you issues unless you program the timeout feature better than just waiting up to a certain amount of time.  This could cause a small delay in data messages going through your engine.

I like the suggestion to feed the data to a singe thread that would write them into the sqlite db so that you dont have to deal with so many processes writing to the same DB at the same time.  I had to do something similar when I wrote a small sub-system to gather all of the stats for the data and try to shove them into sqlite db (one for each site running at the same time).  Huge write contention locks.  Had to write a service to accept the data and do the inserts in a serial fashion.

[Charlie Bursell]

Can you put the thread that writes to the database and the one that reads in the same process?  That way there is no way you could be reading and writing at the same time

With that said, as Jeff noted, there is no reason, even using concurrency locks that sqlite should not be able to keep up.

[Mike Strout]

Thanks all for the great answers. I don’t know why I didn’t think about only having one write thread. I will definitely be implementing that soon. The WAL sounds interesting too, but may be overkill for this relatively low volume DB.

Also, thanks Charlie about the same process idea. I do have a question about that though. We are on 6.1.2 and found that I will get a validation error if I have two db connections in the same process (not Tcl connections). I have another data flow that requires a long-term cache to pend result messages until the ordering provider is added to the order retrospectively (ER workflow). If the result doesn’t have a real ordering provider, I throw it into the DB kill the message. Then each day for three days I query the DB to get the message, CURL over to Epic to see if the ordering provider has been updated yet. If so, I update the message, send it on its way, and archive the record in the DB.

Cloverleaf won’t let me have the OB Database Protocol and the IB Database Protocol in the same process. Anyone know why?

[Charlie Bursell]

Perhaps a Tcl proc would be best for your application.  That is the way we did it prior to database protocol.

I suppose the reason you can’t have the OB Database Protocol and the IB Database Protocol in the same process is possible contention problems.

I am sure if you would ask Support they could get a more definitive answer fro Development.

[Jeff Dinsmore]

Mike:

Regardless of how low your volume is, I would recommend WAL.

Even with just one reader and one writer – assuming they’re in different processes – you’ll have the opportunity for locking problems.  

WAL completely eliminates that.  Writers do not block readers.

You can configure WAL at DB creation or on existing DBs:

Code:

PRAGMA journal_mode=WAL

I was surprised by how easy it was.  It takes two seconds to set and SQLite does the rest.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Todd Horst]

Old topic I know, but for this WAL do you execute it like:

Code:

db eval {PRAGMA journal_mode=WAL}

Or is it in your table creation.

In other words can you provide a more complete example?

[Jeff Dinsmore]

For almost all datbases, I’ll set the WAL pragma at DB creation.

But, you can certainly apply this change to an existing DB.

Either way, it’s the same command – something like this:

Code:

set dbName xyz
set dbFilePath /tmp/abcde

if { [catch {sqlite3 $dbName $dbFilePath} dbErrs] } {
 # handle the error here
} else {
 catch {$dbName eval “PRAGMA journal_mode=WAL”}
 catch {$dbName close}
}

Once set, it persists until reset to something else.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Todd Horst]

Yes, I see both your version and mine would work. Thanks.

I found out that you can test what mode you are in via the following:

Code:

db eval {PRAGMA journal_mode} {
   echo $journal_mode
}

Read more here:

http://www.sqlite.org/pragma.html#pragma_journal_mode

[Jeff Dinsmore]

Your code won’t print journal mode in that form.

You’ll need something like:

Code:

catch {db eval {PRAGMA journal_mode}} jm
puts $jm

or

Code:

set jm [db eval {PRAGMA journal_mode}]
puts $jm

Jeff Dinsmore
Chesapeake Regional Healthcare

[Todd Horst]

Mine and your two ALL work and return the same value of “WAL”, at least for me

[Jeff Dinsmore]

You Are Correct Sir!  I stand corrected and educated.

That’s an interesting and potentially useful function of SQLite’s eval method I’ve not used before.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Todd Horst]

its nice to say, loop over a result set, and create a list of dictionaries

[Author]

Replies