Oracle Storage for BackWave

[DCarlson12]

Hello. I wanted to gauge the level of effort it might take to introduce an Oracle storage for BackWave. I have started the process of testing this library for potentially broad use in replacing Hangfire in several applications, but the lack of an Oracle storage adapter limits me to using in-memory and SQLite as my available options, which does not allow me to test multi-node queues and processing. Hangfire also lacks an official Oracle storage provider, and public implementations are either unoptimized or outdated. Specifically I would be looking for compatibility with a minimum Oracle version of 19c. Initial testing looks pretty good so far, so this would be a great opportunity for me to switch over if you are able to write up a storage provider. Let me know what your thoughts are.

[pdevito3]

👋 @DCarlson12 -- Thanks for the input and details here. I'll need to dig into it a bit to confirm there aren't any gotchas, but initial thoughts are this should be reasonable and I'll work on getting it into the next release.

[pdevito3]

@DCarlson12 update: first pass at an Oracle adapter is done and seems to be working well. I'm hitting it with a hammer now with the test suite but it should be able to go out as a part of 1.7

[pdevito3]

I just pushed a suite of v1.7.0-preview.1 packages, including a new BackWave.Oracle package. Give it a whirl and let me know what you think!

[DCarlson12]

Great! I will get testing. Thanks for the quick turnaround.

[DCarlson12]

So maybe I’m not understanding how PollSize, MaxClaimBatch, and other TimeSpan parameters work together harmoniously—but I’m seeing strange behavior with how a node claims jobs. I’m running a test bench where I schedule 1000 jobs every 2 minutes through a recurring job on one node, then another node processes those jobs on a specific queue with a strict policy. The worker node I want to cap at 20 concurrent jobs, and the docs and my configuration would tell me I shouldn’t see more than 20 leased jobs at a time if I have PoolSize at 20 since it can’t claim more than it can run at one instance. I have the polling interval set to 5 seconds.

I’ve tested this against Oracle and SQLite and I see the same claim behavior. Through the dashboard I’m seeing inconsistent spikes of up to 400-500 leased jobs at the same time, maybe this is just a symptom of the claim system not having written success on the jobs yet? However for Oracle the result is much worse, and after a large batch is processed, the node sits idle for an unpredictable amount of time—usually twice, three times or more longer than the PollInterval (which I have set to 5 seconds) is set for. This to me points to some kind of transaction conflict or missed optimization. Do you have any test results against Oracle to compare this with?

[DCarlson12]

A few other things to note.

• Oracle CLOB and BLOB reading may suffer immense read time-penalties without a larger, tunable, or unlimited (inlined) initial LOB fetch size on read commands as job payload size scales. The OracleDataReader hands a pointer to retrieve a LOB and costs a round trip on each LOB read. You probably don’t see this running in your test suite with a local database. This goes for any other CLOB or BLOB column in the schema.
• Inserts made in enumerations cost round trip time because they are executed in serial fashion. Bulk inserts using an ArrayBindCount lower the round trip time accrual. Other storage adapters share this pattern which might have an optimization path. On a similar note for updates and deletes in enumerations which do not require individual transaction scopes—batch these commands with an ArrayBindCount.

[pdevito3]

No you're right, the leaded count shouldn't be ahead of pool size like that. I'm working on a fix for that now, thanks for the callout.

For your idling ask, I actually noticed on some tests myself last night that on SQLite and Postgres, a new enqueue wakes a sleeping pump at once. I missed that wake on the Oracle wake path, so the group is waiting out the poll interval. Will get this resolved as well.

And thanks for the other oracle callouts I'll account for those in the build

Thanks for the input and feedback so far. I'll get a new prerelease up today or tomorrow for this