On Fri, Nov 2, 2012 at 6:44 PM, Tod Olson <t...@uchicago.edu> wrote: > I'm having a problem with a create … from … order by when my data starts > approaching 2GB. I'm using SQLite 3.7.14 on FreeBSD 8.3-RELEASE-p3. > > The processing starts with an unsorted table, created thus: > > CREATE TABLE all_headings (key, heading); > > Then it creates a sorted version of the table: > > create table headings as select * from all_headings order by key; > > This is fine on small data, but when I load 1.8GB of data (8.8 million > rows) the second CREATE fails, reporting a disk I/O error.
You might be running out of /tmp space. Do you have plenty of /tmp space available for use by the sorter. You might also be running into the 32-bit integer overflow bug that was fixed at http://www.sqlite.org/src/info/e24ba5bee4 though normally that requires a great deal more than 1.8GB of data. Please enable error logging using SQLITE_CONFIG_LOG ( http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfiglog) and rerun your query and see if that provides any additional clues. > If I remove the "order by" clause, the create succeeds. (SQLite was > compiled with large file support, and I could create a 4GB database using > .import so it's not a file system limitation, and the /tmp space is plenty > large.) > > [At that point it looks like pre-sorting the data before loading has some > appeal, but the code maintainer prefers to treat SQLite as the authority on > sorting rather to mess with the many versions of sort(1) on the various > UNIXes and Windows. I understand his point.] > > So trying to understand the error with the ORDER BY clause, I loaded up > the unsorted all_headings table and then trussed sqlite3 running the CREATE > TABLE…ORDER BY. Before the error, there's a lot of lseek()/read() of the > .db file, and a lot of lseek()/write() to temp file (in /tmp, I assume this > is the sort space). Then there's a read() of the temp file, which returns > an error. From truss: > > read(5,0x800f64108,-1834983915) ERR#22 'Invalid argument' > > man 2 read says read's type signature is: > > ssize_t read(int d, void *buf, size_t nbytes); > > and it says this about read returning errno 22: > > [EINVAL] The pointer associated with d was negative. > [EINVAL] The value nbytes is greater than INT_MAX. > > The pointer doesn't look negative, but that nbytes argument looks possibly > a problem. size_t is 64-bit on this system, but INT_MAX = 2147483647, or > the max for a 32-bit signed int. Though truss doesn't know signed from > unsigned valued, the size_t nbytes value that truss reports is greater than > MAX_INT. So I think that explains the error. > > The main question is: is there anything to be done to get that CREATE > TABLE … ORDER BY to work? Based on the truss output, I suspect not, but > maybe someone here has run into the problem before. > > Thanks, > > -Tod > > > Tod Olson <t...@uchicago.edu> > Systems Librarian > University of Chicago Library > > > > _______________________________________________ > 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
