Re: [sqlite] Heuristics of when to vacuum

[Tito Ciuro]

Hi Lawrence,

On Apr 11, 2009, at 7:51 PM, Lawrence Gold wrote:

> I can't offer a formula, but I suggest making it an option for the
> users of the software, with sufficient warning that it could take some
> time, as well as a Cancel button.  Another thing you could do is to
> schedule the vacuum for a time when you know the software won't be in
> use -- for example, those of us who write software for K-12 schools
> can safely schedule operations like this for midnight on  
> weekends.  :-)

It's not an application. It's a framework which is used by a daemon  
process. There can't be a UI, and scheduling a vacuum when it's not  
needed is wasteful, especially because the databases can be quite  
large. This is why I was looking for some way to determine whether  
vacuum is needed, so that it's performed when it makes sense to do so.

Thanks anyway, I appreciate your input! :-)

Regards,

-- Tito
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Heuristics of when to vacuum

[Lawrence Gold]

Sent from my iPod

On Apr 11, 2009, at 10:40, Tito Ciuro  wrote:

> Hello,
>
> I'm sure this question has been asked before, but I haven't been able
> to find it in the archives: when does it make sense to vacuum? If an
> application which deals with a large database vacuums say, on
> termination, it may take a long time to process them and not gain much
> from that operation. I understand that vacuum is needed when lots of
> "holes" are left behind after many objects (table, index, or trigger)
> have been removed from the database.
>
> The question is: what is the threshold I'm looking for? Is there a
> formula I can apply that would hint to me when this op would be
> beneficial?

I can't offer a formula, but I suggest making it an option for the  
users of the software, with sufficient warning that it could take some  
time, as well as a Cancel button.  Another thing you could do is to  
schedule the vacuum for a time when you know the software won't be in  
use -- for example, those of us who write software for K-12 schools  
can safely schedule operations like this for midnight on weekends.  :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple connections from different threads

[P Kishor]

On Sat, Apr 11, 2009 at 8:58 PM, Vinnie  wrote:
>
> Sorry for asking such a basic question, and it seems I know the answer but I 
> would like a confirmation.
>
> If I am executing the same SQL statement from multiple database handles to 
> the same database file, I still need to prepare a distinct sqlite3_stmt for 
> each connection, even though the SQL statement is the same and the database 
> is the same. It seems that the database handle is "bound" to the statement, 
> and there is no way to specify which database you want to use after the 
> statement has been prepared.

I know nothing about threads (except what I wear), but you answered
your question above -- the db is specified in the db handle. The
statements are bound to each db handle. It doesn't matter whether the
statements are the same or different, or meant for same or different
db... the statements belong to a db handle, and the db handle
specifies the db.




>
> Right?
>
>
> Thanks
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple connections from different threads

[Vinnie]

Sorry for asking such a basic question, and it seems I know the answer but I 
would like a confirmation.

If I am executing the same SQL statement from multiple database handles to the 
same database file, I still need to prepare a distinct sqlite3_stmt for each 
connection, even though the SQL statement is the same and the database is the 
same. It seems that the database handle is "bound" to the statement, and there 
is no way to specify which database you want to use after the statement has 
been prepared.

Right?


Thanks

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION

[mw]

Hi, Dave

I have read your post. I have also read many of the recent archived posts,
and googled the web for hints. I'm at a loss here.

The fun thing is that my wrapper class (which I use for a while now in
non-MT environments) allows me to track most of what's going on in SQLite.
And even that does not help.

My protocol shows (for the same thread!)

BEGIN EXCLUSIVE TRANSACTION
INSERT...
COMMIT

BEGIN EXCLUSIVE TRANSACTION
INSERT
< and here it blocks for ever >


I wonder why the INSERT blocks after the BEGIN is through, and why it works
for the first sequence.
>From what I can tell, all other threads as suspended, have their statements
properly reset or finalized etc. Since I use wrappers  for statements etc.
they all clean up proper after themselves. 

I would wish I could somehow see _why_ and _where_ SQLite is blocking a
connection.

I'm hoping that somebody here on the list comes up with a tip :-)


-Original Message-

Wow this sounds exactly like my post of a few days ago titled "Strange
sqlite_busy deadlock behavior".


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVE TRANSACTION

[Dave Brown]

Wow this sounds exactly like my post of a few days ago titled "Strange
sqlite_busy deadlock behavior".

On Sat, Apr 11, 2009 at 7:11 AM,  wrote:

> Hi,
>
> I'm using the latest amalgation of sqlite on Windows NTFS, compiled with
> SQLITE_THREADSAFE=1, from Visual C++.
>
> I have two threads which update a database.
> Each thread uses sqlite_open_v2 to open a connection.
> Both threads do essentially this:
>
> if BEGIN EXCLUSIVE TRANSACTION successful then {
>  INSERT INTO...
>  DELETE FROM...
>  COMMIT
> }
>
> In the scenario I'm facing thread A blocks (as expected) in the BEGIN
> EXCLUSIVE CALL and waits.
> Thread B successfully opens the exclusive transaction, but then fails with
> SQLITE_BUSY in the INSERT INTO (in the step() function).
> How can this be?
>
> My wrapper class uses a busy handler, and waits for quite a long time for
> the lock to unblock. But it never unblocks. And this is within a
> successfully opened exclusive transaction.
>
> As far as I understood the documentation, BEGIN EXCLUSIVE makes sure that
> no
> other thread/process has locks open etc. If it returns success, other
> operations from within the same thread using the same connection cannot
> fail
> with SQLITE_BUSY...
>
> What do I overlook here? I'm puzzling with this for two days now, but
> without success...
>
> Thanks in advance for your ideas.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "extension-functions.c" in windows mobile

[Liam Healy]

On Fri, Apr 10, 2009 at 3:40 AM, Jay A. Kreibich  wrote:
> On Thu, Apr 09, 2009 at 04:34:53PM +0200, Thibaut Gheysen scratched on the 
> wall:
>
>> I have found the "extension-functions.c" in the
>> contrib page of the SQLite website but I'm not able to build it for windows
>> ce. Anybody can help me ?
>
>  What specific problems are you having?  I was able to compile this
>  file into a DLL and load it into sqlite3 without problems under
>  MinGW/MSYS for XP.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >

Jay,

Would you mind posting the steps you used?  I will add them to the
instructions at the top of the file.  This question gets asked before,
and I'm not a Windows user, so I don't know what to say.  For those
that know how to compile it would be helpful to have the specific
commands needed, as I have done for Linux and OSX.

Thank you.

Liam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Heuristics of when to vacuum

[Tito Ciuro]

Hello,

I'm sure this question has been asked before, but I haven't been able  
to find it in the archives: when does it make sense to vacuum? If an  
application which deals with a large database vacuums say, on  
termination, it may take a long time to process them and not gain much  
from that operation. I understand that vacuum is needed when lots of  
"holes" are left behind after many objects (table, index, or trigger)  
have been removed from the database.

The question is: what is the threshold I'm looking for? Is there a  
formula I can apply that would hint to me when this op would be  
beneficial?

Thanks in advance,

-- Tito
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk I/O error...?

[Alberto Simões]

Hello.

On Sat, Apr 11, 2009 at 3:04 PM, Jim Wilcoxson  wrote:
> You'll get this if you have a database with an active journal
> (incomplete transactions) and you don't have write access to the
> database.  In other words, the database needs a rollback from some
> prior operations done under a different userid, but now you don't have
> write access to do the rollback.

Hmms, these databases where being created and were shut down by other
script (kill).
It might happen that they have some kind of lock active.

I can't find any lock/journal file.Thus, it should probably be inside
the database file.

Is there anyway to 'clean' a database status?

Thank you
Alberto

>
> To me, it should be a permission error instead, to make it clear
> what's going on.
>
> Jim
>
> On 4/11/09, Alberto Simões  wrote:
>> Hello
>> I am getting disk I/O error with:
>>
>> [a...@search1 align]$ du -hs T.3.sqlite
>> 122M  T.3.sqlite
>> [a...@search1 align]$ sqlite3 T.3.sqlite
>> SQLite version 3.6.6
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> .schema
>> CREATE TABLE trigrams (w1,w2,w3,occs);
>> CREATE INDEX idxT3w1 ON trigrams (w1);
>> sqlite> CREATE INDEX idxT3w2 ON trigrams (w2);
>> SQL error: disk I/O error
>> sqlite> [a...@search1 align]$ df -h .
>> Filesystem            Size  Used Avail Use% Mounted on
>> /dev/sdc1             148G   42G  100G  30% /export3
>> [a...@search1 align]$
>>
>> I tried to get I/O errors with other commands (for instance, yes > _)
>> but couldn't get any error.
>>
>> Any hint on what I can check to discover the problem?
>> Thank you
>> Alberto
>>
>> --
>> Alberto Simões
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Software first.  Software lasts!
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVE TRANSACTION

[mw]

Hi,
 
I'm using the latest amalgation of sqlite on Windows NTFS, compiled with
SQLITE_THREADSAFE=1, from Visual C++.
 
I have two threads which update a database.
Each thread uses sqlite_open_v2 to open a connection.
Both threads do essentially this:
 
if BEGIN EXCLUSIVE TRANSACTION successful then {
  INSERT INTO...
  DELETE FROM...
  COMMIT
}
 
In the scenario I'm facing thread A blocks (as expected) in the BEGIN
EXCLUSIVE CALL and waits.
Thread B successfully opens the exclusive transaction, but then fails with
SQLITE_BUSY in the INSERT INTO (in the step() function).
How can this be?
 
My wrapper class uses a busy handler, and waits for quite a long time for
the lock to unblock. But it never unblocks. And this is within a
successfully opened exclusive transaction. 

As far as I understood the documentation, BEGIN EXCLUSIVE makes sure that no
other thread/process has locks open etc. If it returns success, other
operations from within the same thread using the same connection cannot fail
with SQLITE_BUSY...
 
What do I overlook here? I'm puzzling with this for two days now, but
without success...
 
Thanks in advance for your ideas.
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk I/O error...?

[Jim Wilcoxson]

You'll get this if you have a database with an active journal
(incomplete transactions) and you don't have write access to the
database.  In other words, the database needs a rollback from some
prior operations done under a different userid, but now you don't have
write access to do the rollback.

To me, it should be a permission error instead, to make it clear
what's going on.

Jim

On 4/11/09, Alberto Simões  wrote:
> Hello
> I am getting disk I/O error with:
>
> [a...@search1 align]$ du -hs T.3.sqlite
> 122M  T.3.sqlite
> [a...@search1 align]$ sqlite3 T.3.sqlite
> SQLite version 3.6.6
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .schema
> CREATE TABLE trigrams (w1,w2,w3,occs);
> CREATE INDEX idxT3w1 ON trigrams (w1);
> sqlite> CREATE INDEX idxT3w2 ON trigrams (w2);
> SQL error: disk I/O error
> sqlite> [a...@search1 align]$ df -h .
> FilesystemSize  Used Avail Use% Mounted on
> /dev/sdc1 148G   42G  100G  30% /export3
> [a...@search1 align]$
>
> I tried to get I/O errors with other commands (for instance, yes > _)
> but couldn't get any error.
>
> Any hint on what I can check to discover the problem?
> Thank you
> Alberto
>
> --
> Alberto Simões
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] disk I/O error...?

[Alberto Simões]

Hello
I am getting disk I/O error with:

[a...@search1 align]$ du -hs T.3.sqlite
122MT.3.sqlite
[a...@search1 align]$ sqlite3 T.3.sqlite
SQLite version 3.6.6
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE trigrams (w1,w2,w3,occs);
CREATE INDEX idxT3w1 ON trigrams (w1);
sqlite> CREATE INDEX idxT3w2 ON trigrams (w2);
SQL error: disk I/O error
sqlite> [a...@search1 align]$ df -h .
FilesystemSize  Used Avail Use% Mounted on
/dev/sdc1 148G   42G  100G  30% /export3
[a...@search1 align]$

I tried to get I/O errors with other commands (for instance, yes > _)
but couldn't get any error.

Any hint on what I can check to discover the problem?
Thank you
Alberto

-- 
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users