> Le 29 f?vr. 2016 ? 12:34, Simon Slavin <slavins at bigfraud.org> a ?crit : > > On 29 Feb 2016, at 9:14am, Olivier Mascia <om at integral.be> wrote: > >> we have found the sweet spot (for us, and for now) to be 4K page sizes, >> which just happen to be the virtual memory page size of Windows system > > Yes. That's the idea. Match the page size of the database to the size of > the chunks your operating system is using. It can be 1K. It can be 16K. > > It might be useful for other readers to know what experimenting you used to > find your 'sweet spot'. Was it simple an obvious or did you have to play > tricks ? > > Simon.
Short story: quite simple and obvious as soon as we tested on real life scenarios. Tricks (like playing fool with sync) were out of question anyway. We test-migrated about 100 real-life databases of our software over these last weeks, with sizes ranging from about 200 MB to about 4 GB and measured the time for complete migration: creating schema, pumping data, check referential integrity after the fact, adding additional required indexes, and running analyze on the resulting DBs. We then also compared some results from sqlite3_analyzer. The overall tests was run with various page sizes and cache sizes for about 50 of those databases. Then we checked the results by actually running typical tasks of the software to get sure it fitted our usual performance level (or was better) by comparing to current results (not using SQLite). The results (4 K) is of course closely related to our specific datasets and we were focused on short-bursts write performance, without using any integrity degradation tricks and with the added comfort of writer does not block readers that wal provides. The easiest way to tune for pure read performance would be to use memory mapping mode (and 4K pages to match the virtual memory physical page size), but we don't need that added boost for reads, they are already very good as such. In all test cases, we kept the cache-size x page-size mostly constant (2000 pages of 1K, 1000 pages of 2 K, 500 pages of 4K), without going below 500 pages. Overall, we wanted to 'feel' the real-life behavior of the application, within specific goals of memory usage, and not synthetic test cases which you can always write to prove whatever you want and its contrary. 4 K and 8 K are very hard to distinguish in our results. 1K and 2K are clearly inferior though. Settings above 8 K brought very mixed results on very different work-loads. So be it for 4 K as our own default for now. Indeed out of our compilation settings, here are those mostly significant on performance (in either way) - again within our specific needs and application architecture - don't draw generalizations from our (rather short) experience: SQLITE_THREADSAFE=2 SQLITE_DEFAULT_CACHE_SIZE=500 SQLITE_DEFAULT_PAGE_SIZE=4096 SQLITE_DIRECT_OVERFLOW_READ SQLITE_SECURE_DELETE SQLITE_DEFAULT_FOREIGN_KEYS=1 SQLITE_ENABLE_STAT4 -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 842 bytes Desc: Message signed with OpenPGP using GPGMail URL: <http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160229/058ee774/attachment.pgp>
