Re: [sqlite] Still chasing "database schema has changed"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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
[sqlite] Still chasing "database schema has changed"
Thinking some more about the occasional instances of "database schema has changed" that I experience, I took a look through the amalgamation to see where this error might be being detected. Reading the code and the comments I'm reminded of something I keep forgetting: that SQLite has no server. And the way my app operates, 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). 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? -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users