Normal
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 4KBReadTimes:ReadTest 1: 13576ms, 11891msReadTest 2: 330ms, 208msReadTest 3: 27.7ms, 9.6msReadTest 4: 844.8ms, 61.5msAs 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 4KBReadTimes:ReadTest 1: 12079ms, 11809msReadTest 2: 238ms, 208msReadTest 3: 15.4ms, 9.9msReadTest 4: 117.5ms, 42.1msIn 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 4KBReadTimes:ReadTest 1: 12110ms, 11736msReadTest 2: 243ms, 236msReadTest 3: 24.4ms, 12.8msReadTest 4: 117.5ms, 37.2Now 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 4KBReadTimes:ReadTest 1: 12208ms, 11647msReadTest 2: 237ms, 218msReadTest 3: 15.7ms, 10.2msReadTest 4: 122.8ms, 45.7msNow 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 4KBReadTimes:ReadTest 1: 12191ms, 11761msReadTest 2: 259ms, 207msReadTest 3: 18.9ms, 9.8msReadTest 4: 114.7ms, 43.8msRoughly 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...
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
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:
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:
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:
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...