Also note that almost all current storage you can purchase uses 4k basic blocks. So it's not just some weird Windows thing.
In addition to performance advantages of getting the block size right, there is also the advantage that most storage systems strive hard to make sure block operations are atomic in the face of power losses. This is easier to accomplish when writing a full block, as opposed to doing a read-modify-write required to write a smaller block. Circling back to testing page_size, I have noticed that it is easy to mistake cache effects for improvements due to page_size. If your pages are 2x as big, your cache is 2x as big (unless you modify it). You can convince yourself to use huge pages, only to later realize that actually the improvement was from using a huge cache or from preloading the cache. So when experimenting, do try to determine _why_ the improvement happens, because there may be other ways to accomplish the improvement. -scott On Mon, Feb 29, 2016 at 7:28 AM, Keith Medcalf <kmedcalf at dessus.com> wrote: > > The NTFS cluster size is usually 4K, unless you have changed it (you can > use fsutil or chkdsk to see what the cluster size is). Windows does I/O in > units of a cluster. Therefore the smallest (and largest) I/O which Windows > will do is 1 Cluster, or 4K. While you may set the page size larger or > smaller at the application level, Windows will always do I/O in cluster > units. So if you set the page size to 1K, it will still I/O 4K for each > page read/write, and if you set it larger than the cluster size, then it > will scatter-gather the number of clusters required to fill your request. > > > -----Original Message----- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > > bounces at mailinglists.sqlite.org] On Behalf Of Olivier Mascia > > Sent: Monday, 29 February, 2016 02:15 > > To: SQLite mailing list > > Subject: Re: [sqlite] Page_size > > > > > > > Le 29 f?vr. 2016 ? 09:22, Jim Wang <2004wqg2008 at 163.com> a ?crit : > > > > > > hi,all > > > > > > Could different page_size do impact on the speed of retrieving > > record? > > > Is page_size 8192 faster than page_size 1024 or page_szie 4096? or > > The pagesize has nothing to do with the retrieving speed. > > > > > > Best reagard! > > > Jim Wang. > > > > I'm eager to read the comments of seasoned users on this. > > > > On my side, having started using SQLite very recently (December 2015), > > after a lot of tests using real data, 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 (except older Itanium versions). > > Though we use a slightly smaller cache size (between 500 and max 1000 > > pages) instead of the default 2000 pages. We use private caches per > > connection only, and use only wal mode. > > > > You'll obviously pack more record and index cells per page when the page > > size is larger, but you will have larger I/O too to read or write any of > > those pages. Your cache memory usage will obviously be higher with > larger > > page size, unless you cache fewer pages (which we chose to do). > > > > I can't bring any comment regarding other OS. > > But there is probably not ONE answer to your question. > > > > -- > > Meilleures salutations, Met vriendelijke groeten, Best Regards, > > Olivier Mascia, integral.be/om > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
