On Nov 3, 2012, at 7:32 AM, Richard Hipp <d...@sqlite.org> wrote: > 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.
Plenty, 14GB of free space available to /tmp (it's all one big partition). > 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. Yes! I compiled up that version and it solves the problem. I eagerly await the release of SQLite version 3.7.15. Thank you for your help. -Tod > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
