Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Taras Glek
On 08/19/2010 01:27 PM, Taras Glek wrote: Hi, I really appreciate that sqlite got this feature to reduce fragmentation, but why not expose this as a pragma? In many cases it is not feasible to pass the chunk size via a C API. For example with a pragma I could do fragmentation testing via an

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Jay A. Kreibich
On Thu, Aug 19, 2010 at 06:46:07PM -0400, Jim Wilcoxson scratched on the wall: > On 8/19/10, Simon Slavin wrote: > > > > On 19 Aug 2010, at 9:27pm, Taras Glek wrote: > > > >> I really appreciate that sqlite got this feature to reduce > >> fragmentation, but why not expose

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Jay A. Kreibich
On Thu, Aug 19, 2010 at 10:56:58PM +0100, Simon Slavin scratched on the wall: > It might be worth noting that fragmentation is normally seen as an issue > only under Windows which is very sensitive to it however. Most systems are sensitive to fragmentation. It just happens that many other

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Stephen Oberholtzer
> If you make PRAGMA freelist_count writable as you suggest, I would > expect the database to reserve space once, and not use a larger pice of > the disk every time ist has to be expanded in the future. > > Martin To throw in my $0.02, I would suggest a *different* name for the pragma, something

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Martin Engelschalk
Am 20.08.2010 13:38, schrieb Max Vlasov: >> In my case (which is certainly not typical), a (several GB) large >> database is built up in several batches, one table at a time, while in >> parallel many intermediate files on the disk are created. This resulted >> in a very fragmented database

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Simon Slavin
On 20 Aug 2010, at 7:58am, Martin Engelschalk wrote: > Under Windows, the insert speed did not change measurably, but the speed > of the later selects increased by about 15-20%. Also, my customer was > happy. Okay, this is real-world data. In that case there may be some point to

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Max Vlasov
> In my case (which is certainly not typical), a (several GB) large > database is built up in several batches, one table at a time, while in > parallel many intermediate files on the disk are created. This resulted > in a very fragmented database file. After that, also several times, the > data is

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Shawn Wilsher
On Thu, Aug 19, 2010 at 2:56 PM, Simon Slavin wrote: > It might be worth noting that fragmentation is normally seen as an issue only > under Windows which is very sensitive to it however.  Other operating systems > use different ways of handling disk access, however, real

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Martin Engelschalk
Am 19.08.2010 23:56, schrieb Simon Slavin: > On 19 Aug 2010, at 9:27pm, Taras Glek wrote: > >> I really appreciate that sqlite got this feature to reduce >> fragmentation, but why not expose this as a pragma? > Do you have figures which suggest that reducing fragmentation leads to any >

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-19 Thread Max Vlasov
Whether SQLITE_FCNTL_CHUNKS_SIZE is useful is a different discussion. > I myself would love to see features exposed via pragmas whenever > possible, for the simple reason that I don't use the C API and can't > make use of the features otherwise. I would assume that since the > SQLite developers

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-19 Thread Jim Wilcoxson
On 8/19/10, Simon Slavin wrote: > > On 19 Aug 2010, at 9:27pm, Taras Glek wrote: > >> I really appreciate that sqlite got this feature to reduce >> fragmentation, but why not expose this as a pragma? > > Do you have figures which suggest that reducing fragmentation leads to

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-19 Thread Simon Slavin
On 19 Aug 2010, at 9:27pm, Taras Glek wrote: > I really appreciate that sqlite got this feature to reduce > fragmentation, but why not expose this as a pragma? Do you have figures which suggest that reducing fragmentation leads to any improvement in performance ? It might be worth noting

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-19 Thread Max Vlasov
On Fri, Aug 20, 2010 at 12:27 AM, Taras Glek wrote: > Hi, > I really appreciate that sqlite got this feature to reduce > fragmentation, but why not expose this as a pragma? > Taras, I think that you're overestimating the feature. On the OS level it won't matter how far the

[sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-19 Thread Taras Glek
Hi, I really appreciate that sqlite got this feature to reduce fragmentation, but why not expose this as a pragma? In many cases it is not feasible to pass the chunk size via a C API. For example with a pragma I could do fragmentation testing via an sqlite shell, now this option is out