Reply to thread

To give you a bit of an example of what problems I face with the ReadTests - most likely due to the windows operating system read cache - I have done some ReadTests now with the current status version of SQLiteDatabase. I've only measured the ReadTimes (not the QueryTimes).


The first result I got was this:

locking_mode=Exclusive / Shared Cache / Page Size 4KB

ReadTimes:

ReadTest 1: 13576ms, 11891ms

ReadTest 2: 330ms, 208ms

ReadTest 3: 27.7ms, 9.6ms

ReadTest 4: 844.8ms, 61.5ms

As you see, this is with shared cache enabled, exclusive locking mode and a page size of 4KB. My first thought was: Damn, now with exclusive locking mode the ImportTest, i.e. write performance, is faster, but the ReadTests are much slower.


So I left everything as it was and just set the locking mode to normal. Restarted MP2 Server and these are the results:

locking_mode=Normal / Shared Cache / Page Size 4KB

ReadTimes:

ReadTest 1: 12079ms, 11809ms

ReadTest 2: 238ms, 208ms

ReadTest 3: 15.4ms, 9.9ms

ReadTest 4: 117.5ms, 42.1ms

In particular the first pass of ReadTest 2 and ReadTest4 made me think that my suspicion was right - exclusive locking mode is slower than normal locking mode. But then again I couldn't believe this because everyone in various forums says, exclusive mode is faster. So again I didn't change anything but setting exclusive locking mode, restarted MP2 server and did exactly the same test again. Results as follows:


locking_mode=Exclusive / Shared Cache / Page Size 4KB

ReadTimes:

ReadTest 1: 12110ms, 11736ms

ReadTest 2: 243ms, 236ms

ReadTest 3: 24.4ms, 12.8ms

ReadTest 4: 117.5ms, 37.2

Now all of a sudden, locking mode exclusive was about as fast as normal locking mode - maybe just a bit slower in ReadTest 3.


This time I left everything as it was, locking mode still exclusive. Restarted MP2 Server and just did the test again:

locking_mode=Exclusive / Shared Cache / Page Size 4KB

ReadTimes:

ReadTest 1: 12208ms, 11647ms

ReadTest 2: 237ms, 218ms

ReadTest 3: 15.7ms, 10.2ms

ReadTest 4: 122.8ms, 45.7ms

Now all the ReadTests - including ReadTest 3 - were about as fast as with normal locking mode.


Once again no changes, a restart of MP2 Server and the same test again:

locking_mode=Exclusive / Shared Cache / Page Size 4KB

ReadTimes:

ReadTest 1: 12191ms, 11761ms

ReadTest 2: 259ms, 207ms

ReadTest 3: 18.9ms, 9.8ms

ReadTest 4: 114.7ms, 43.8ms

Roughly the same results as with the last test...


Now the conclusion of all this is that probably the windows OS read cache makes the test results somehow unpredictable. After a certain number of times that I repeat the same tests (with a restart of MP2 Server between the tests, so the SQLite inbuilt cache is definitely empty) it seems that we get more or less reliable results, but I don't know after what number of repeats - and so I would have to guess whether a result was caused by my optimizations or by the respective parts of the database file being in windows read cache.


So while under normal using conditions there is of course also the windows OS cache, which makes MP2 Server faster. But that doesn't help for testing and improving SQLiteDatabase. As I already mentioned above, I saw a tool somewhere in the internet, which is able to clear the windows read cache. I will try to find that again and then see whether I get consistent results...


Top Bottom