High CPU RtlpEnterCriticalSectionContended when using SQLite
(1) By Qifan Zhou (qifzhou) on 2021-12-15 09:10:13 [link] [source]
Hi SQLite Forum, Send this question here for help.
Context :
we switched to use SQLite saving a lot of managed heap, but Process CPU increased 30%. I know this is a trade of between CPU and Memory, but I'm investigating whether this 30% could be optimized further.
Investigation :
Take PerfView trace and dump file. From the result in a perf trace, SQLite related trace occupies around 30% which matching what we observed from process CPU. Here's the results,
25% out of the increased 30% could be located into below call stack, seems CriticalSectionContended.
ntdll!RtlpEnterCriticalSectionContended
ntdll!RtlEnterCriticalSection
sqlite.interop!?
system.data.sqlite!DomainBoundILStubClass.IL_STUB_PInvoke(IntPtr, IntPtr, Int32, IntPtr ByRef, IntPtr ByRef, Int32 ByRef)
system.data.sqlite!System.Data.SQLite.SQLite3.Prepare(System.Data.SQLite.SQLiteConnection, System.String, System.Data.SQLite.SQLiteStatement, UInt32, System.String system.data.sqlite!System.Data.SQLite.SQLiteCommand.BuildNextCommand()
system.data.sqlite!System.Data.SQLite.SQLiteDataReader.NextResult()
system.data.sqlite!System.Data.SQLite.SQLiteDataReader..ctor(System.Data.SQLite.SQLiteCommand, System.Data.CommandBehavior)
system.data.sqlite!System.Data.SQLite.SQLiteCommand.ExecuteReader(System.Data.CommandBehavior)
5% CPU out of increased 30% comes from the Dispose of SQLiteCommand Dispose, also end with the CriticalSectionContended :
ntdll!RtlpEnterCriticalSectionContended
ntdll!RtlEnterCriticalSection
sqlite.interop!?
system.data.sqlite!DomainBoundILStubClass.IL_STUB_PInvoke(IntPtr)
system.data.sqlite!System.Data.SQLite.SQLiteBase.FinalizeStatement(System.Data.SQLite.SQLiteConnectionHandle, IntPtr)
system.data.sqlite!System.Data.SQLite.SQLiteStatementHandle.ReleaseHandle()
mscorlib!System.Runtime.InteropServices.CriticalHandle.Cleanup()
system.data.sqlite!System.Data.SQLite.SQLiteStatement.Dispose(Boolean)
system.data.sqlite!System.Data.SQLite.SQLiteCommand.DisposeStatements()
system.data.sqlite!System.Data.SQLite.SQLiteCommand.ClearCommands()
system.data.sqlite!System.Data.SQLite.SQLiteCommand.set_Connection(System.Data.SQLite.SQLiteConnection)
system.data.sqlite!System.Data.SQLite.SQLiteCommand.Dispose(Boolean)
Also from the dump file I take, I use !cs -a show all CriticalSections, but found no locked ones. This is also making me confusing here.
We already enabled WAL for better concurrency performance by setting : private const string DefaultConnectConfig = @"Data Source='{0}';Version=3;Pooling=True;Max Pool Size=256;Journal Mode=WAL;"
Any help here will be really appreciated !
(2) By Warren Young (wyoung) on 2021-12-15 09:14:07 in reply to 1 [link] [source]
This is a 30% increase relative to what?
What did you switch from?
(3) By Qifan Zhou (qifzhou) on 2021-12-15 09:18:31 in reply to 2 [link] [source]
Hi Warren, I'm comparing with reading from in memory data directly, after we enable reading from SQLite db, memory saved, but CPU increased 30%. I understand this is trade off between CPU and Memory, there need extra CPU to read from SQLite instead of in memory data, just doing an analysis where these CPU goes, and found these RtlpEnterCriticalSectionContended consuming most CPU.
(4) By Warren Young (wyoung) on 2021-12-15 12:49:32 in reply to 3 [source]
If this is a single-threaded application and you're comparing to a :memory: SQLite DB, then I do believe there are settings you can make in SQLite to disable much of the locking.
However, as soon as you start talking about disk storage, you talk about the possibility of multiple readers/writers, in which case you need to talk about locking and the overhead that goes with it, else you dismiss the ACID guarantees SQLite provides.
In other words, the speed hit is ACID versus non-ACID.
(5) By Qifan Zhou (qifzhou) on 2021-12-15 17:42:58 in reply to 4 [link] [source]
Thank you Warren ! And yes, it’s common to have multiple readers and writers in our scenario. So your point is the 30% increased CPU is expected pay for the ACID guarantee. Then I’m thinking about that if I want to optimize the CPU overhead here, the only direction seems I should look into the service logic and try to simplify the sql operation, reduce the contention by reducing the read write calls to db, right ?
Also besides the WAL that we have enabled, is there any other settings could help the performance in this concurrent read and write scenario ?
(6) By Warren Young (wyoung) on 2021-12-15 18:22:16 in reply to 5 [link] [source]
I'm not going to tell you that a specific CPU cost is expected because it depends on your data, your usage pattern, and so forth, all details you've left out of your question.
If this is a synthetic benchmark doing inserts as fast as possible, then it's likely that your 30% CPU hit value is bogus: it only occurs in this unrealistic scenario.
If instead it's inserting at a normal rate and you can still measure such an increase, then we'll want to see what it is you're trying to do to give you any kind of assurance that your measured value is sensible.
All I was actually saying above is that some CPU hit is expected. The only "free" lock is no lock. There do exist lock-free data structures, but SQLite is not such a one.
(7) By Qifan Zhou (qifzhou) on 2021-12-16 09:05:50 in reply to 6 [link] [source]
Totally agree this ! The CPU overhead is expected, but it's just is it reasonable considering our readwrite operation pattern. I will curcle back after having our DB operation statistics peak and non peak time.