Re: [sqlite] Still chasing "database schema has changed"

[Petite Abeille]

On Sep 28, 2011, at 1:25 AM, Richard Hipp wrote:

>  Hence I ask:  have you actually measured the difference?

Please join the BAAG party  :)

http://www.battleagainstanyguess.com/

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

Re: [sqlite] Still chasing "database schema has changed"

[Simon Slavin]

On 28 Sep 2011, at 3:01pm, Tim Streater wrote:

> No, I've done no tests. I'm not concerned that each database be down to its 
> smallest possible size, merely that the app have a mechanism that, from time 
> to time, compresses certain databases through which most of the apps traffic 
> flows (so, plenty of rows being added and deleted).

VACUUM does not do compression.  What it does is recover space that is unused 
because you have deleted data from the database.  However, so does inserting 
new data.  So ...

CREATE TABLE
INSERT 1000 rows

... VACUUM here would not save any space

DELETE 50 rows
INSERT 50 rows

... VACUUM here would not save any space

DELETE 10 rows

... VACUUM here would recover some space

INSERT 10 rows

... but after this operation it is impossible to tell whether you did or didn't 
VACUUM.

(Slightly inaccurate because some rows take up more space than other rows, but 
that's the idea.)

So if you are constantly inserting and deleting rows, but you are inserting as 
much or more data than you are deleting, VACUUM does nothing to save filespace. 
 The only time VACUUM will save space is if you've done a bunch of deleting and 
haven't put as much new data in since then.

VACUUM does do something else: defragmentation.  But since almost no operations 
of SQLite depend on reading many pages in sequence, defragmentation doesn't 
help much.  I did some tests and found it difficult to see significant change 
except in specially designed situations which would never occur in real life.

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 28 Sep 2011 at 00:25, Richard Hipp  wrote: 

> On Tue, Sep 27, 2011 at 7:16 PM, Tim Streater  wrote:
>
>> On 27 Sep 2011 at 18:15, Richard Hipp  wrote:
>>
>>> On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater 
>> wrote:
>>>
 The databases that get vacuumed tend to have a fair amount of traffic in
 and out. So it's good to compress them from time to time.
>>>
>>> Really?  Have you actually measured this to see if it makes a difference?
>>> What happens if you never VACUUM?
>>
>> They'll get bigger and bigger. I imagine the users (if I ever have any)
>> would start complaining.

> You know that SQLite automatically reclaims and reuses space from rows you
> DELETE and tables you DROP, right?
>
> VACUUM repacks and defragments the database file.  The repacking might make
> the file a little smaller, but probably not that much.  Defragmenting might
> help performance, but again, probably not that much.  Hence I ask:  have you
> actually measured the difference?

No, I've done no tests. I'm not concerned that each database be down to its 
smallest possible size, merely that the app have a mechanism that, from time to 
time, compresses certain databases through which most of the apps traffic flows 
(so, plenty of rows being added and deleted).

I'm sure I'm doing the VACUUMing unnecessarily often, but for simplicity I 
simply put all the timer based housekeeping in one pot. I think now I'll 
arrange to do that one aspect much less frequently.

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

Re: [sqlite] Still chasing "database schema has changed"

[Richard Hipp]

On Tue, Sep 27, 2011 at 7:16 PM, Tim Streater  wrote:

> On 27 Sep 2011 at 18:15, Richard Hipp  wrote:
>
> > On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater 
> wrote:
> >
> >> The databases that get vacuumed tend to have a fair amount of traffic in
> >> and out. So it's good to compress them from time to time.
> >
> > Really?  Have you actually measured this to see if it makes a difference?
> > What happens if you never VACUUM?
>
> They'll get bigger and bigger. I imagine the users (if I ever have any)
> would start complaining.
>


You know that SQLite automatically reclaims and reuses space from rows you
DELETE and tables you DROP, right?

VACUUM repacks and defragments the database file.  The repacking might make
the file a little smaller, but probably not that much.  Defragmenting might
help performance, but again, probably not that much.  Hence I ask:  have you
actually measured the difference?



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


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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 27 Sep 2011 at 18:15, Richard Hipp  wrote: 

> On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater  wrote:
>
>> The databases that get vacuumed tend to have a fair amount of traffic in
>> and out. So it's good to compress them from time to time.
>
> Really?  Have you actually measured this to see if it makes a difference?
> What happens if you never VACUUM?

They'll get bigger and bigger. I imagine the users (if I ever have any) would 
start complaining.

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

Re: [sqlite] Still chasing "database schema has changed"

[Richard Hipp]

On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater  wrote:

>
> The databases that get vacuumed tend to have a fair amount of traffic in
> and out. So it's good to compress them from time to time.
>

Really?  Have you actually measured this to see if it makes a difference?
What happens if you never VACUUM?

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 27 Sep 2011 at 12:23, Simon Slavin  wrote: 

> On 27 Sep 2011, at 10:49am, Tim Streater wrote:
>
>> On 27 Sep 2011 at 00:19, Simon Slavin  wrote:
>>
>>> I believe that VACUUM is one of the statements which counts as changing the
>>> schema, because it does its work by rewriting entire tables and/or indexes.
>>> So don't do a VACUUM when you're doing multi-process access.  Cut out the
>>> VACUUMs and see whether you still get this result code.
>>
>> Ah, thanks, that's a good clue. I can do some work in that area to ensure
>> that the VACUUMs are done at a quiet moment.
>
> You might not need VACUUM at all.  I might use it just before I make a copy of
> the database file for transfer or archive, if it was important to me that the
> file was as small as possible.  But I have quite a few databases I've never
> bothered using VACUUM on at all.  Most of them shrink only by small amounts
> and probably start growing immediately afterwards anyway.

The databases that get vacuumed tend to have a fair amount of traffic in and 
out. So it's good to compress them from time to time. Not doing that when there 
is traffic has ended up being a couple of lines of PHP and a couple of lines of 
JavaScript, so if that avoids the issue in future I shall be well pleased.

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

Re: [sqlite] Still chasing "database schema has changed"

[Simon Slavin]

On 27 Sep 2011, at 10:49am, Tim Streater wrote:

> On 27 Sep 2011 at 00:19, Simon Slavin  wrote: 
> 
>> I believe that VACUUM is one of the statements which counts as changing the
>> schema, because it does its work by rewriting entire tables and/or indexes. 
>> So don't do a VACUUM when you're doing multi-process access.  Cut out the
>> VACUUMs and see whether you still get this result code.
> 
> Ah, thanks, that's a good clue. I can do some work in that area to ensure 
> that the VACUUMs are done at a quiet moment.

You might not need VACUUM at all.  I might use it just before I make a copy of 
the database file for transfer or archive, if it was important to me that the 
file was as small as possible.  But I have quite a few databases I've never 
bothered using VACUUM on at all.  Most of them shrink only by small amounts and 
probably start growing immediately afterwards anyway.

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 27 Sep 2011 at 00:19, Simon Slavin  wrote: 

> On 26 Sep 2011, at 11:32pm, Tim Streater wrote:

>> But, very often the database on which the error occurs is :memory: - and I
>> guess each thread will have its own one of those.
>
> Are you doing concurrent access to your :memory: databases ?

Looking back through my logs to early July, there were three instances - and in 
each case a housekeeping script is doing a VACUUM. So that's a strong pointer. 
And in the case of :memory:, it may just appear (to my ill-informed eyes) to be 
related to :memory:. The statement is in fact:

  insert into mem.messages select * from main.messages where absid= some value;

Here, :memory: was attached as mem, and main was opened earlier but will be one 
of those that gets VACUUMed. I guess the select can then fail in the same way.

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 27 Sep 2011 at 00:19, Simon Slavin  wrote: 

> On 26 Sep 2011, at 11:32pm, Tim Streater wrote:
>
>> For the most part it's going to be SELECT, INSERT, UPDATE, BEGIN, COMMIT.
>> Less often, there's VACUUM. Let me have a look at my logs - OK, when I got
>> the error today, there was a thread doing a VACUUM on the db which got the
>> error (within a few seconds, in terms of the log timestamps). The SQL
>> statement being done when the error was reported was in fact:
>
>>  update uids set uid_date='1317028381',rec_date='1317002026' where
>> uid='UID3335-1298893761'
>
> I believe that VACUUM is one of the statements which counts as changing the
> schema, because it does its work by rewriting entire tables and/or indexes. 
> So don't do a VACUUM when you're doing multi-process access.  Cut out the
> VACUUMs and see whether you still get this result code.

Ah, thanks, that's a good clue. I can do some work in that area to ensure that 
the VACUUMs are done at a quiet moment.

>> But, very often the database on which the error occurs is :memory: - and I
>> guess each thread will have its own one of those.
>
> Are you doing concurrent access to your :memory: databases ?

No, that's the odd part. But I'll check back through my logs.

Thanks for the help.

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

Re: [sqlite] Still chasing "database schema has changed"

[Simon Slavin]

On 26 Sep 2011, at 11:32pm, Tim Streater wrote:

> For the most part it's going to be SELECT, INSERT, UPDATE, BEGIN, COMMIT. 
> Less often, there's VACUUM. Let me have a look at my logs - OK, when I got 
> the error today, there was a thread doing a VACUUM on the db which got the 
> error (within a few seconds, in terms of the log timestamps). The SQL 
> statement being done when the error was reported was in fact:

>  update uids set uid_date='1317028381',rec_date='1317002026' where 
> uid='UID3335-1298893761'

I believe that VACUUM is one of the statements which counts as changing the 
schema, because it does its work by rewriting entire tables and/or indexes.  So 
don't do a VACUUM when you're doing multi-process access.  Cut out the VACUUMs 
and see whether you still get this result code.

> But, very often the database on which the error occurs is :memory: - and I 
> guess each thread will have its own one of those.

Are you doing concurrent access to your :memory: databases ?

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

Re: [sqlite] Still chasing "database schema has changed"

[Stephan Beal]

On Tue, Sep 27, 2011 at 12:32 AM, Tim Streater  wrote:

> But, very often the database on which the error occurs is :memory: - and I
> guess each thread will have its own one of those.
>

Not only each thread, but each reference to ":memory:" within a process is
independent of each other. At least that was my experience when i tried it
out a few years ago.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 26 Sep 2011 at 22:40, Simon Slavin  wrote: 

> On 26 Sep 2011, at 10:30pm, Tim Streater wrote:

>> My question now is, if two or more PHP scripts are competing for access to
>> the same database, is "database schema has changed" a possible outcome?
>
> Well, let's see what you're actually doing.  The following SQL commands should
> never cause that error: SELECT, INSERT, UPDATE, BEGIN, COMMIT.  What other SQL
> commands are you issuing ?  VACUUM or ANALYZE ?  CREATE or DROP ?

For the most part it's going to be SELECT, INSERT, UPDATE, BEGIN, COMMIT. Less 
often, there's VACUUM. Let me have a look at my logs - OK, when I got the error 
today, there was a thread doing a VACUUM on the db which got the error (within 
a few seconds, in terms of the log timestamps). The SQL statement being done 
when the error was reported was in fact:

  update uids set uid_date='1317028381',rec_date='1317002026' where 
uid='UID3335-1298893761'

But, very often the database on which the error occurs is :memory: - and I 
guess each thread will have its own one of those.

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

Re: [sqlite] Still chasing "database schema has changed"

[Simon Slavin]

On 26 Sep 2011, at 10:30pm, Tim Streater wrote:

>  it's quite possible that it can ask apache to run three or four threads more 
> or less simultaneously, each of which may want to modify the same set of 
> SQLite databases. Each such thread is a PHP script using the PDO interface; 
> this is under OS X 10.7.1 using whichever apache version/PHP/SQLite that 
> comes with it (it's all pretty recent, IIRC).

I have at least one setup using that combination of software apart from the 
single point that instead of using the PDO interface to SQLite, I'm using PHP's 
SQLIte3:: object-oriented interface.

> My question now is, if two or more PHP scripts are competing for access to 
> the same database, is "database schema has changed" a possible outcome?

Well, let's see what you're actually doing.  The following SQL commands should 
never cause that error: SELECT, INSERT, UPDATE, BEGIN, COMMIT.  What other SQL 
commands are you issuing ?  VACUUM or ANALYZE ?  CREATE or DROP ?

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