Re: [sqlite] Maximum database size?
On 3 Mar 2010, at 5:57am, Collin Capano wrote: > I've been running into some disk I/O errors when doing things such as > vacuuming and/or inserting things into temp tables in a database. The > databases that are giving me trouble are quite large: between 29 and > 55GB. However, as large as that is, I don't think running out of disk > space is the issue as I have about 3TB of free space on the disk. So, my > question is, is there a maximum size that databases can be? Nothing of that level built into SQLite. And even if there was, it would not produce an i/o error, it would complain about pages or filesize. Run a hardware check on your computer: one of those programs which reads every sector of the disk and checks other pieces of hardware. The other thing to do would be to use the command-line tools to turn your database into SQL commands, then use those commands to create a new database file, then delete the old database file and rename the new one. This would definitively get rid of any structure problems in your database file and make sure it wasn't occupying any faulty disk sectors. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maximum database size?
On Wed, Mar 3, 2010 at 8:57 AM, Collin Capano wrote: > The databases in question don't seem to be > corrupt; I can open them on the command line and in python programs > (using pysqlite) and can read triggers from them just fine. It's just > when I try to vacuum and create temp tables that I run into trouble. > > Did you try to perform PRAGMA integrity_check; on theses bases? At least you'll be sure sqlite reads all the necessary data and considers it correct. I don't know what will it take for 55G database, maybe hour maybe a full night, but if you have enough time, I'd do this before anything else Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maximum database size?
On Mar 3, 2010, at 12:57 PM, Collin Capano wrote: > Hello SQLite users, > > I've been running into some disk I/O errors when doing things such as > vacuuming and/or inserting things into temp tables in a database. The > databases that are giving me trouble are quite large: between 29 and > 55GB. However, as large as that is, I don't think running out of disk > space is the issue as I have about 3TB of free space on the disk. > So, my > question is, is there a maximum size that databases can be? If so, > what > is the limiting factor? The databases in question don't seem to be > corrupt; I can open them on the command line and in python programs > (using pysqlite) and can read triggers from them just fine. It's just > when I try to vacuum and create temp tables that I run into trouble. Running out of space on the /tmp partition perhaps. See pragma temp_store_directory: http://www.sqlite.org/pragma.html#pragma_temp_store_directory Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 bug with MATCH plus OR
The recent changes to FTS3 fixed a long standing problem with MATCH and
AND operators combined. Take this schema:
drop table if exists myfts;
create virtual table myfts using fts3 (a);
insert into myfts values ('one');
insert into myfts values ('two');
This following query produced an "unable to use function MATCH in the
requested context" error up to 3.6.21, IIRC. The workaround was to add a
+ sign in front of the rowid. Since 3.6.22 it gladly works even without
the + sign:
select * from myfts where (myfts MATCH 'one') and (rowid=1);
However, a similiar problem is still present using "or" instead "and".
Even more problematic, the +rowid workaround no longer helps. Both these
queries fail:
select * from myfts where (myfts MATCH 'one') or (rowid=1);
select * from myfts where (myfts MATCH 'one') or (+rowid=1);
Is this something that should be addressed?
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maximum database size?
On Wed, Mar 03, 2010 at 12:57:22AM -0500, Collin Capano scratched on the wall: > Hello SQLite users, > > I've been running into some disk I/O errors when doing things such as > vacuuming and/or inserting things into temp tables in a database. Both of those operations require temp space. Depending on your config, that may or may not be on the same disk as the database itself. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 bug with MATCH plus OR
I can't speak to the question of whether it's a real problem, but I
bet you can work around with a sub-select. Something like:
select * from myfts where rowid = 1 OR rowid IN (select rowid from
myfts where (myfts MATCH 'one'));
-scott
On Wed, Mar 3, 2010 at 3:26 AM, Ralf Junker wrote:
> The recent changes to FTS3 fixed a long standing problem with MATCH and
> AND operators combined. Take this schema:
>
> drop table if exists myfts;
> create virtual table myfts using fts3 (a);
> insert into myfts values ('one');
> insert into myfts values ('two');
>
> This following query produced an "unable to use function MATCH in the
> requested context" error up to 3.6.21, IIRC. The workaround was to add a
> + sign in front of the rowid. Since 3.6.22 it gladly works even without
> the + sign:
>
> select * from myfts where (myfts MATCH 'one') and (rowid=1);
>
> However, a similiar problem is still present using "or" instead "and".
> Even more problematic, the +rowid workaround no longer helps. Both these
> queries fail:
>
> select * from myfts where (myfts MATCH 'one') or (rowid=1);
> select * from myfts where (myfts MATCH 'one') or (+rowid=1);
>
> Is this something that should be addressed?
>
> Ralf
> ___
> 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] FTS3 bug with MATCH plus OR
On Mar 3, 2010, at 6:26 PM, Ralf Junker wrote:
> The recent changes to FTS3 fixed a long standing problem with MATCH
> and
> AND operators combined. Take this schema:
>
> drop table if exists myfts;
> create virtual table myfts using fts3 (a);
> insert into myfts values ('one');
> insert into myfts values ('two');
>
> This following query produced an "unable to use function MATCH in the
> requested context" error up to 3.6.21, IIRC. The workaround was to
> add a
> + sign in front of the rowid. Since 3.6.22 it gladly works even
> without
> the + sign:
>
> select * from myfts where (myfts MATCH 'one') and (rowid=1);
>
> However, a similiar problem is still present using "or" instead "and".
> Even more problematic, the +rowid workaround no longer helps. Both
> these
> queries fail:
>
> select * from myfts where (myfts MATCH 'one') or (rowid=1);
> select * from myfts where (myfts MATCH 'one') or (+rowid=1);
>
> Is this something that should be addressed?
Unfortunately it's the nature of the virtual table interface that
not all queries that include MATCH operators can be implemented.
In theory this particular case could be supported, but it would
involve some difficult to test changes to the query planner. And
there would still be other expressions with MATCH that would not
work.
Best approach is probably to use a "rowid IN (...sub-select...)"
clause as Scott suggested.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maximum database size?
Hi all, Thanks so much for the info! The problem does appear to have been due to temp_store_directory. It was set to /var/tmp; on our clusters /var/tmp exits on another disk which only has about 3GB free as opposed to the 3TB I have on the disk that the database lives on. I re-set it the database's directory and the errors went away. Side question: I looked at the sqlite source code and I saw that it tries several temp directories such as /var/tmp when compiling before it will use the current working directory. Why does it prefer to use /var/tmp or some other temp directory as opposed to just using the current directory? Is there some performance advantage in doing this? Either way, thanks again for the help! This cleared up a lot of problems that has been bothering me for awhile now. Collin On 3/3/10 4:52 AM, Dan Kennedy wrote: > On Mar 3, 2010, at 12:57 PM, Collin Capano wrote: > > >> Hello SQLite users, >> >> I've been running into some disk I/O errors when doing things such as >> vacuuming and/or inserting things into temp tables in a database. The >> databases that are giving me trouble are quite large: between 29 and >> 55GB. However, as large as that is, I don't think running out of disk >> space is the issue as I have about 3TB of free space on the disk. >> So, my >> question is, is there a maximum size that databases can be? If so, >> what >> is the limiting factor? The databases in question don't seem to be >> corrupt; I can open them on the command line and in python programs >> (using pysqlite) and can read triggers from them just fine. It's just >> when I try to vacuum and create temp tables that I run into trouble. >> > Running out of space on the /tmp partition perhaps. See pragma > temp_store_directory: > > http://www.sqlite.org/pragma.html#pragma_temp_store_directory > > Dan. > > ___ > 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] Maximum database size?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Collin Capano wrote: > Why does it prefer to use > /var/tmp or some other temp directory as opposed to just using the > current directory? Is there some performance advantage in doing this? The temp tables are for a particular connection only. If the process dies unexpectedly then no other process would ever look for the temp tables. Consequently the temp files backing the temp tables really are temporary files and the OS appropriate locations are used by default. (For example the OS may remove temp files not touched in the last 7 days.) By contrast the journal is looked for by other processes. If a process is writing to the journal and dies unexpectedly then another SQLite based process will look for the journal and do a rollback as appropriate. As you saw you can control the temporary location at compile and run time, and also chance the behaviour between using files and using memory. ie SQLite has sensible defaults but you can still make things work however you want. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuPMukACgkQmOOfHg372QTTVgCgt5Cp3uk+mY/DaTgX+CycOwa2 bt4An31hdkCLYeQG1b8Tp8L3Z8AK4/vQ =zBma -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] game, set, match
sqlite : 1, w3 : 0 "This specification has reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path. Until another implementor is interested in implementing this spec, the description of the SQL dialect has been left as simply a reference to Sqlite, which isn't acceptable for a standard. Should you be an implementor interested in implementing an independent SQL backend, please contact the editor so that he can write a specification for the dialect, thus allowing this specification to move forward." http://www.w3.org/TR/webdatabase/ -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] game, set, match
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 P Kishor wrote: > but we need > multiple independent implementations to proceed along a > standardisation path. They are right. You couldn't even just reference SQLite as it does change with every release so it isn't 100% compatible with itself. Usually the changes are additions so it is no big deal. But some were "mistakes" in earlier versions that were kept to avoid compatibility changes later on. Even if the standard referenced SQLite, it would have to reference a particular version and freeze things at that behaviour. The ongoing SQLite development would then have to have a compile time or runtime way of restricting behaviour to exact compatibility with that reference version. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuPQiYACgkQmOOfHg372QTJ6ACguj3ke8l26/xrV6KoA7mImr7f CCoAoNWrVnB0cgxL3sJEnnDpvzDb20tE =oqRK -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] game, set, match
On Wed, Mar 3, 2010 at 11:16 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > P Kishor wrote: >> but we need >> multiple independent implementations to proceed along a >> standardisation path. > > They are right. Of course they are right. My point was a bit different -- seems like only SQLite offers the right mix of functional punch, agile performance and lightweight footprint to be a viable technology for a web database. The world is full of databases, each claiming to one-up the next one. This little fella is the one that could. > You couldn't even just reference SQLite as it does change > with every release so it isn't 100% compatible with itself. That said, sqlite has a pretty good track record of being backward compatible, so much so that... > Usually the > changes are additions so it is no big deal. But some were "mistakes" in > earlier versions that were kept to avoid compatibility changes later on. > Right you said. Anyway, it was an amusing observation on W3C's impasse. > Even if the standard referenced SQLite, it would have to reference a > particular version and freeze things at that behaviour. The ongoing SQLite > development would then have to have a compile time or runtime way of > restricting behaviour to exact compatibility with that reference version. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iEYEARECAAYFAkuPQiYACgkQmOOfHg372QTJ6ACguj3ke8l26/xrV6KoA7mImr7f > CCoAoNWrVnB0cgxL3sJEnnDpvzDb20tE > =oqRK > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] game, set, match
On 4 Mar 2010, at 5:39am, P Kishor wrote: > My point was a bit different -- seems like > only SQLite offers the right mix of functional punch, agile > performance and lightweight footprint to be a viable technology for a > web database. I don't think any of those characteristics are the ones which made this happen. I think that it's the facts that SQLite is Open Source and compiles in so many compilers. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS & Doc Compression
While I am not directly concerned by the problem, a possibility to transparently compress the text of FTS3 tables (not the indexes, just the contents of the virtual column) using zlib would be great. I cut a database size in half by doing this on non-fts3 text tables. DEFLATE being very efficient in terms of speed even for embedded devices by today's standard, I'm convinced this could do wonders. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
