Hi, playing with my admin about cache size (there was a question today related to the cache size), noticed a strange thing with cache. It's Windows, initially it was v3.6.10, but the same is for 3.7.2
I sometimes mentioned that I can track vfs requests for every select in my admin. In this case it was interesting to look at the numbers related to the cache size. The default PRAGA page_size=2000; so the threshold level is about 2000*1024 = 2,048,000 bytes. A select for a test table filled with wikipedia abstract article was SELECT Count(*) FROM WikiData WHERE Id < ? This query doesn't produce tiny result in the front-end, but at the same time uses cache and tried to keep the pages accessed in it. And the number of ? that corresponds to the cache_size threshold is about 9813. The things I noticed: - If the select accesses the pages total size of whose is lower than the cache size, everything is ok. I mean, trying to select any subset of the data leads to only 16 bytes reading. SELECT Count(*) FROM WikiData WHERE Id < 9812 => VFS 2,045,968 SELECT Count(*) FROM WikiData WHERE Id < 1000 => VFS 16 SELECT Count(*) FROM WikiData WHERE Id > 5000 and id < 7000 => VFS 16 these are expected results - But when there was a request that filled all the cache and the following request is a subset of the first one, the latter unfortunately doesn't take advantage of the pages already in cache SELECT Count(*) FROM WikiData WHERE Id < 10000 => VFS 2,082,838 SELECT Count(*) FROM WikiData WHERE Id < 9800 => VFS 2,023,440 - Also when there are two consecutive requests that probably larger than the cache size, they will do the full reading (or almost full reading) SELECT Count(*) FROM WikiData WHERE Id < 10000 => VFS 2,082,838 SELECT Count(*) FROM WikiData WHERE Id < 10001 => VFS 2,062,352 SELECT Count(*) FROM WikiData WHERE Id < 9999 => VFS 2,061,328 Is it ok for cache to behave like this or some optimization is possible to fix this? I don't know if it's possible to do similar tests with other front-ends. If someone really interested, I can upload my tool, but it's just not completely ready for mainstream. Thanks, Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
