Re: [sqlite] programmatic way of determining fragmentation?

2010-10-22 Thread Max Vlasov
On Sat, Oct 23, 2010 at 2:28 AM, Dustin Sallings wrote: > > On Oct 22, 2010, at 15:12, Max Vlasov wrote: > > > As for your initial question, I think fragmentation evaluation is > possible > > with the help of VFS. I'd keep a total sum of of absolute difference > between > >

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-22 Thread Nicolas Williams
On Sat, Oct 23, 2010 at 02:12:19AM +0400, Max Vlasov wrote: > As for your initial question, I think fragmentation evaluation is possible > with the help of VFS. I'd keep a total sum of of absolute difference between > consequent read offsets for xRead operation. In this case if some xRead >

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-22 Thread Dustin Sallings
On Oct 22, 2010, at 15:12, Max Vlasov wrote: > As for your initial question, I think fragmentation evaluation is possible > with the help of VFS. I'd keep a total sum of of absolute difference between > consequent read offsets for xRead operation. In this case if some xRead > request reads 1024

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-22 Thread Max Vlasov
On Thu, Oct 21, 2010 at 11:32 AM, Dustin Sallings wrote: > >Mostly, I want to have an idea how fragmented I am. My app can > tremendously wreck a sqlite table right now to the point where a > reconstruction (vacuum, etc...) is the difference between three hours and > one

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Jim Wilcoxson
On Thu, Oct 21, 2010 at 1:27 PM, Dustin Sallings wrote: > > On Oct 21, 2010, at 10:05, Pavel Ivanov wrote: > > > I think it's not related to fragmentation, but to fill percentage of > > b-tree pages. I guess your reconstructed table is much less in total > > size than your

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Dustin Sallings
On Oct 21, 2010, at 10:05, Pavel Ivanov wrote: > I think it's not related to fragmentation, but to fill percentage of > b-tree pages. I guess your reconstructed table is much less in total > size than your initial one. Also does changing cache_size changes > above numbers? Interesting.

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Pavel Ivanov
>        Select * from a table took just slightly under three hours. >        Select * from a reconstructed table (insert into select from) in a new > database took 57 seconds. I think it's not related to fragmentation, but to fill percentage of b-tree pages. I guess your reconstructed table is

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Simon Slavin
On 21 Oct 2010, at 5:38pm, Dustin Sallings wrote: > On Oct 21, 2010, at 9:27, Simon Slavin wrote: > >> Have you actually demonstrated this ? In other words do you have an >> operation that's really 'too slow', but after a VACUUM it's fast enough ? > > Yes. > > Select * from a

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Dustin Sallings
On Oct 21, 2010, at 9:27, Simon Slavin wrote: > Have you actually demonstrated this ? In other words do you have an > operation that's really 'too slow', but after a VACUUM it's fast enough ? Yes. Select * from a table took just slightly under three hours. Select *

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Simon Slavin
On 21 Oct 2010, at 5:21pm, Dustin Sallings wrote: > Those provide some info, but not the specific info I'm having problems > with right now. I have too many non-sequential pages and it's making my > application run a couple of orders of magnitude slower than a fresh DB. Have you

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Dustin Sallings
On Oct 21, 2010, at 7:52, Roger Binns wrote: > You'll need to read the docs on the file format: > > http://www.sqlite.org/fileformat.html > http://www.sqlite.org/fileformat2.html > > - From that you can determine a measure of how bad the fragmentation is, and > your code can be quick and

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Dustin Sallings
On Oct 21, 2010, at 1:00, Kees Nuyt wrote: > PRAGMA page_count; and PRAGMA freelist_count; will give you > some info, but not as much as sqlite3_analyzer. > It might be enough in your case. Those provide some info, but not the specific info I'm having problems with right now. I have

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/21/2010 12:32 AM, Dustin Sallings wrote: > Mostly, I want to have an idea how fragmented I am. You'll need to read the docs on the file format: http://www.sqlite.org/fileformat.html http://www.sqlite.org/fileformat2.html - From that

Re: [sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Kees Nuyt
On Thu, 21 Oct 2010 00:32:28 -0700, Dustin Sallings wrote: > > I realize sqlite3_analyzer will give me some good > data for general use, but I'd like to be able to > do this from within my app. Does anyone have a > lib-like thing I can use, or an internal sqlite API > that can

[sqlite] programmatic way of determining fragmentation?

2010-10-21 Thread Dustin Sallings
I realize sqlite3_analyzer will give me some good data for general use, but I'd like to be able to do this from within my app. Does anyone have a lib-like thing I can use, or an internal sqlite API that can help me out here? Mostly, I want to have an idea how fragmented I am.