From: Tod O. <to...@uc...> - 2012-10-31 20:53:50
|
Has anyone run into size limits building the browse indexes? We're having a problem with building our browse indexes, the first one blows up: + java -Dfile.encoding=UTF-8 -cp 'browse-indexing-multi-title.jar:../solr/lib/*' CreateBrowseSQLite sorted-title.tmp title_browse.db Exception in thread "main" java.sql.SQLException: disk I/O error at org.sqlite.DB.execute(DB.java:275) at org.sqlite.DB.executeUpdate(DB.java:281) at org.sqlite.Stmt.executeUpdate(Stmt.java:103) at CreateBrowseSQLite.buildOrderedTables(CreateBrowseSQLite.java:115) at CreateBrowseSQLite.create(CreateBrowseSQLite.java:139) at CreateBrowseSQLite.main(CreateBrowseSQLite.java:154) The BrowseIndexer code that blows up is: stat.executeUpdate ("create table headings " + "as select * from all_headings order by key;"); All of our 8M+ rows got into all_headings, but we blow up creating headings. We're nowhere near filling the disc, so I figure this is maybe some kind of Java or SQLite limitation. The created DB file is about 1.8GB. This sort of feels like a 32bit limit . If this were an out of memory error, I would expect something other than an I/O error. Has anyone run into this? -Tod |
From: Mark T. <ma...@di...> - 2012-11-01 11:20:59
|
Hi Tod, I tried a similar experiment here--created a 2GB+ table and then cloned it using 'create table ... as select ...', but that worked as expected. Are you running on a 32-bit platform? If so, I might need to repeat my test on a virtual machine to see what you're seeing. One thing I did notice: while it was creating the sorted table, SQLite was writing a pretty large temporary file into /var/tmp, even though my test database was on a different filesystem. Is there any chance that one of your system disks is quietly filling up while the browse build is happening on your other filesystem? We definitely had browse indexes bigger than 2GB when I was working at the NLA, so don't despair just yet :) Cheers, Mark Tod Olson <to...@uc...> writes: > Has anyone run into size limits building the browse indexes? > > We're having a problem with building our browse indexes, the first one blows up: > > + java -Dfile.encoding=UTF-8 -cp 'browse-indexing-multi-title.jar:../solr/lib/*' CreateBrowseSQLite sorted-title.tmp title_browse.db > Exception in thread "main" java.sql.SQLException: disk I/O error > at org.sqlite.DB.execute(DB.java:275) > at org.sqlite.DB.executeUpdate(DB.java:281) > at org.sqlite.Stmt.executeUpdate(Stmt.java:103) > at CreateBrowseSQLite.buildOrderedTables(CreateBrowseSQLite.java:115) > at CreateBrowseSQLite.create(CreateBrowseSQLite.java:139) > at CreateBrowseSQLite.main(CreateBrowseSQLite.java:154) > > The BrowseIndexer code that blows up is: > > stat.executeUpdate ("create table headings " + > "as select * from all_headings order by key;"); > > All of our 8M+ rows got into all_headings, but we blow up creating > headings. We're nowhere near filling the disc, so I figure this is > maybe some kind of Java or SQLite limitation. The created DB file is > about 1.8GB. This sort of feels like a 32bit limit . If this were an > out of memory error, I would expect something other than an I/O > error. Has anyone run into this? -- Mark Triggs <ma...@di...> |
From: Tod O. <to...@uc...> - 2012-11-01 13:24:13
|
Good question about the 32-bit environment. We're running with the OpenJDK 64-Bit Server VM, though maybe I should give it the -d64 switch, just to be safe. But this also has me thinking about the import process. Since we have a sorted file, it seems like we should be able to bulk import into SQLite and not have to create headings from all_headings. The .import command might work, if it preserves the order of the data. -Tod On Nov 1, 2012, at 6:20 AM, Mark Triggs <ma...@di...> wrote: > Hi Tod, > > I tried a similar experiment here--created a 2GB+ table and then cloned > it using 'create table ... as select ...', but that worked as expected. > Are you running on a 32-bit platform? If so, I might need to repeat my > test on a virtual machine to see what you're seeing. > > One thing I did notice: while it was creating the sorted table, SQLite > was writing a pretty large temporary file into /var/tmp, even though my > test database was on a different filesystem. Is there any chance that > one of your system disks is quietly filling up while the browse build is > happening on your other filesystem? > > We definitely had browse indexes bigger than 2GB when I was working at > the NLA, so don't despair just yet :) > > Cheers, > > Mark > > > > Tod Olson <to...@uc...> writes: > >> Has anyone run into size limits building the browse indexes? >> >> We're having a problem with building our browse indexes, the first one blows up: >> >> + java -Dfile.encoding=UTF-8 -cp 'browse-indexing-multi-title.jar:../solr/lib/*' CreateBrowseSQLite sorted-title.tmp title_browse.db >> Exception in thread "main" java.sql.SQLException: disk I/O error >> at org.sqlite.DB.execute(DB.java:275) >> at org.sqlite.DB.executeUpdate(DB.java:281) >> at org.sqlite.Stmt.executeUpdate(Stmt.java:103) >> at CreateBrowseSQLite.buildOrderedTables(CreateBrowseSQLite.java:115) >> at CreateBrowseSQLite.create(CreateBrowseSQLite.java:139) >> at CreateBrowseSQLite.main(CreateBrowseSQLite.java:154) >> >> The BrowseIndexer code that blows up is: >> >> stat.executeUpdate ("create table headings " + >> "as select * from all_headings order by key;"); >> >> All of our 8M+ rows got into all_headings, but we blow up creating >> headings. We're nowhere near filling the disc, so I figure this is >> maybe some kind of Java or SQLite limitation. The created DB file is >> about 1.8GB. This sort of feels like a 32bit limit . If this were an >> out of memory error, I would expect something other than an I/O >> error. Has anyone run into this? > > -- > Mark Triggs > <ma...@di...> |
From: Mark T. <ma...@di...> - 2012-11-03 21:59:04
|
Ah, excellent. With any luck SQLiteJDBC will pull that in too (or we can always attempt our own build) Mark Tod Olson <to...@uc...> writes: > Resolution: I was running into a 32-bit integer overflow error that was fixed on Oct. 26 and will be in SQLite 3.7.15: > > http://www.sqlite.org/src/info/e24ba5bee4 > > (and my thanks to the sqlite-users list). > > -Tod -- Mark Triggs <ma...@di...> |
From: Tod O. <to...@uc...> - 2012-11-07 16:16:44
|
Sadly, no. The SQLiteJDBC bundles the native drivers directly into the jar file. Here's some more of what I find: The new sqlite3 (built from the source at the link below) does let me build the headings table from the command line.[1] And I can query it. There are three selects that the browse-handler code executes, and I can execute them by hand via sqlite3. But when the browse handler is queried for title browse data, it returns an HTTP 500 error with the message "database disk image is malformed"; however, it is quite happy with the smaller browse indexes. I did look at compiling up a new SQLiteJDBC. The driver that ships with VuFind seems to be from 2008 (based on opening up the jar file) so could probably use an update anyhow. It looks the current SQLiteJDBC is at the Xerial project[2]. When building from source[3], the build process downloads the SQLite 3.7.8 code. (3.7.15 will be released on 12/12.) The build is structured such that it is not immediately obvious to me how to substitute in the patched code without reworking some of the Makefile. I'm not really certain what my next action should be. Probably work on the SQLiteJDBC recompile. If anyone is familiar with the build process, I could use some tips on how to get the desired SQLite source into the build. -Tod [1] headings and all_headings are each 9,173,673 rows, the title_browse.db is 5.2GB. [2] http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC [3] http://code.google.com/p/sqlite-jdbc/source/checkout On Nov 3, 2012, at 4:58 PM, Mark Triggs <ma...@di...> wrote: > Ah, excellent. With any luck SQLiteJDBC will pull that in too (or we > can always attempt our own build) > > Mark > > > Tod Olson <to...@uc...> writes: > >> Resolution: I was running into a 32-bit integer overflow error that was fixed on Oct. 26 and will be in SQLite 3.7.15: >> >> http://www.sqlite.org/src/info/e24ba5bee4 >> >> (and my thanks to the sqlite-users list). >> >> -Tod > > -- > Mark Triggs > <ma...@di...> |
From: Demian K. <dem...@vi...> - 2012-11-07 16:42:32
|
Not sure if this makes any difference, but there is a notice on the Google Code page that the project has now moved here: https://bitbucket.org/xerial/sqlite-jdbc Looks like there's been a bit of new development (as of September). - Demian > -----Original Message----- > From: Tod Olson [mailto:to...@uc...] > Sent: Wednesday, November 07, 2012 11:17 AM > To: Mark Triggs > Cc: vuf...@li... Mailinglist Tech > Subject: Re: [VuFind-Tech] Large browse indexes: SQLite limits? > > Sadly, no. The SQLiteJDBC bundles the native drivers directly into the jar > file. Here's some more of what I find: > > The new sqlite3 (built from the source at the link below) does let me build > the headings table from the command line.[1] And I can query it. There are > three selects that the browse-handler code executes, and I can execute them by > hand via sqlite3. But when the browse handler is queried for title browse > data, it returns an HTTP 500 error with the message "database disk image is > malformed"; however, it is quite happy with the smaller browse indexes. > > I did look at compiling up a new SQLiteJDBC. The driver that ships with VuFind > seems to be from 2008 (based on opening up the jar file) so could probably use > an update anyhow. It looks the current SQLiteJDBC is at the Xerial project[2]. > When building from source[3], the build process downloads the SQLite 3.7.8 > code. (3.7.15 will be released on 12/12.) The build is structured such that it > is not immediately obvious to me how to substitute in the patched code without > reworking some of the Makefile. > > I'm not really certain what my next action should be. Probably work on the > SQLiteJDBC recompile. If anyone is familiar with the build process, I could > use some tips on how to get the desired SQLite source into the build. > > -Tod > > [1] headings and all_headings are each 9,173,673 rows, the title_browse.db is > 5.2GB. > [2] http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC > [3] http://code.google.com/p/sqlite-jdbc/source/checkout > > On Nov 3, 2012, at 4:58 PM, Mark Triggs <ma...@di...> wrote: > > > Ah, excellent. With any luck SQLiteJDBC will pull that in too (or we > > can always attempt our own build) > > > > Mark > > > > > > Tod Olson <to...@uc...> writes: > > > >> Resolution: I was running into a 32-bit integer overflow error that was > fixed on Oct. 26 and will be in SQLite 3.7.15: > >> > >> http://www.sqlite.org/src/info/e24ba5bee4 > >> > >> (and my thanks to the sqlite-users list). > >> > >> -Tod > > > > -- > > Mark Triggs > > <ma...@di...> > > > ------------------------------------------------------------------------------ > LogMeIn Central: Instant, anywhere, Remote PC access and management. > Stay in control, update software, and manage PCs from one command center > Diagnose problems and improve visibility into emerging IT issues > Automate, monitor and manage. Do more in less time with Central > http://p.sf.net/sfu/logmein12331_d2d > _______________________________________________ > Vufind-tech mailing list > Vuf...@li... > https://lists.sourceforge.net/lists/listinfo/vufind-tech |
From: Tod O. <to...@uc...> - 2012-11-07 17:59:00
|
So, our sysadmin Peggy figured out how to make the FreeBSD ports system to do the heavy lifting on compiling SQLiteJDBC with the updated SQLite source. Upshot is: with an SQLiteJDBC build on the updated SQLite, we can now read 5.2G dbs! Huzzah! I'm also rebuilding the indexes from scratch again, just to be certain. So far so good. Good to see the more recent activity. I'd taken the source URL from the project page and overlooked the Google Code notice. I think that after 12/12 rolls around and SQLite 3.7.15 is released, it might be worth updating the SQLite JDBC that VuFind ships with. Best, -Tod On Nov 7, 2012, at 10:42 AM, Demian Katz <dem...@vi...> wrote: > Not sure if this makes any difference, but there is a notice on the Google Code page that the project has now moved here: > > https://bitbucket.org/xerial/sqlite-jdbc > > Looks like there's been a bit of new development (as of September). > > - Demian > >> -----Original Message----- >> From: Tod Olson [mailto:to...@uc...] >> Sent: Wednesday, November 07, 2012 11:17 AM >> To: Mark Triggs >> Cc: vuf...@li... Mailinglist Tech >> Subject: Re: [VuFind-Tech] Large browse indexes: SQLite limits? >> >> Sadly, no. The SQLiteJDBC bundles the native drivers directly into the jar >> file. Here's some more of what I find: >> >> The new sqlite3 (built from the source at the link below) does let me build >> the headings table from the command line.[1] And I can query it. There are >> three selects that the browse-handler code executes, and I can execute them by >> hand via sqlite3. But when the browse handler is queried for title browse >> data, it returns an HTTP 500 error with the message "database disk image is >> malformed"; however, it is quite happy with the smaller browse indexes. >> >> I did look at compiling up a new SQLiteJDBC. The driver that ships with VuFind >> seems to be from 2008 (based on opening up the jar file) so could probably use >> an update anyhow. It looks the current SQLiteJDBC is at the Xerial project[2]. >> When building from source[3], the build process downloads the SQLite 3.7.8 >> code. (3.7.15 will be released on 12/12.) The build is structured such that it >> is not immediately obvious to me how to substitute in the patched code without >> reworking some of the Makefile. >> >> I'm not really certain what my next action should be. Probably work on the >> SQLiteJDBC recompile. If anyone is familiar with the build process, I could >> use some tips on how to get the desired SQLite source into the build. >> >> -Tod >> >> [1] headings and all_headings are each 9,173,673 rows, the title_browse.db is >> 5.2GB. >> [2] http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC >> [3] http://code.google.com/p/sqlite-jdbc/source/checkout >> >> On Nov 3, 2012, at 4:58 PM, Mark Triggs <ma...@di...> wrote: >> >>> Ah, excellent. With any luck SQLiteJDBC will pull that in too (or we >>> can always attempt our own build) >>> >>> Mark >>> >>> >>> Tod Olson <to...@uc...> writes: >>> >>>> Resolution: I was running into a 32-bit integer overflow error that was >> fixed on Oct. 26 and will be in SQLite 3.7.15: >>>> >>>> http://www.sqlite.org/src/info/e24ba5bee4 >>>> >>>> (and my thanks to the sqlite-users list). >>>> >>>> -Tod >>> >>> -- >>> Mark Triggs >>> <ma...@di...> >> >> >> ------------------------------------------------------------------------------ >> LogMeIn Central: Instant, anywhere, Remote PC access and management. >> Stay in control, update software, and manage PCs from one command center >> Diagnose problems and improve visibility into emerging IT issues >> Automate, monitor and manage. Do more in less time with Central >> http://p.sf.net/sfu/logmein12331_d2d >> _______________________________________________ >> Vufind-tech mailing list >> Vuf...@li... >> https://lists.sourceforge.net/lists/listinfo/vufind-tech |
From: Mark T. <ma...@di...> - 2012-11-07 19:32:14
|
Many thanks for all the investigative work, Tod--that all sounds great to me. I've made a note in my diary to check back on the 12th of Dec and will build and upgrade the browse version of SQLiteJDBC at that point. Cheers, Mark Tod Olson <to...@uc...> writes: > So, our sysadmin Peggy figured out how to make the FreeBSD ports system > to do the heavy lifting on compiling SQLiteJDBC with the updated SQLite > source. Upshot is: with an SQLiteJDBC build on the updated SQLite, we can > now read 5.2G dbs! Huzzah! > > I'm also rebuilding the indexes from scratch again, just to be certain. > So far so good. > > Good to see the more recent activity. I'd taken the source URL from the > project page and overlooked the Google Code notice. > > I think that after 12/12 rolls around and SQLite 3.7.15 is released, it > might be worth updating the SQLite JDBC that VuFind ships with. > > Best, > > -Tod -- Mark Triggs <ma...@di...> |
From: Demian K. <dem...@vi...> - 2012-11-07 20:30:24
|
Thanks, Mark. Once you've built it to your satisfaction, send me a reminder and I'll do a little extra testing over here and then push it into trunk/master. - Demian > -----Original Message----- > From: Mark Triggs [mailto:ma...@di...] > Sent: Wednesday, November 07, 2012 2:32 PM > To: Tod Olson > Cc: Demian Katz; vuf...@li... Mailinglist Tech > Subject: Re: [VuFind-Tech] Large browse indexes: SQLite limits? > > Many thanks for all the investigative work, Tod--that all sounds great > to me. I've made a note in my diary to check back on the 12th of Dec > and will build and upgrade the browse version of SQLiteJDBC at that > point. > > Cheers, > > Mark > > > Tod Olson <to...@uc...> writes: > > > So, our sysadmin Peggy figured out how to make the FreeBSD ports system > > to do the heavy lifting on compiling SQLiteJDBC with the updated SQLite > > source. Upshot is: with an SQLiteJDBC build on the updated SQLite, we can > > now read 5.2G dbs! Huzzah! > > > > I'm also rebuilding the indexes from scratch again, just to be certain. > > So far so good. > > > > Good to see the more recent activity. I'd taken the source URL from the > > project page and overlooked the Google Code notice. > > > > I think that after 12/12 rolls around and SQLite 3.7.15 is released, it > > might be worth updating the SQLite JDBC that VuFind ships with. > > > > Best, > > > > -Tod > > -- > Mark Triggs > <ma...@di...> |
From: Mark T. <ma...@di...> - 2013-01-25 01:21:12
|
Just a note that I haven't forgotten about this, but I need to find some time to have a think about it. I tried the official SQLiteJDBC build, but its bundled Linux build of SQLite requires a glibc version that's newer than what ships with my (and, I suspect, other people's) distro. I'm not sure whether I'll need to do a custom build, or whether we can just tell people to use the latest SQLiteJDBC as a drop-in replacement if they hit problems on Solaris. More investigation needed :) Mark Mark Triggs <ma...@di...> writes: > Many thanks for all the investigative work, Tod--that all sounds great > to me. I've made a note in my diary to check back on the 12th of Dec > and will build and upgrade the browse version of SQLiteJDBC at that > point. > > Cheers, > > Mark > > > Tod Olson <to...@uc...> writes: > >> So, our sysadmin Peggy figured out how to make the FreeBSD ports system >> to do the heavy lifting on compiling SQLiteJDBC with the updated SQLite >> source. Upshot is: with an SQLiteJDBC build on the updated SQLite, we can >> now read 5.2G dbs! Huzzah! >> >> I'm also rebuilding the indexes from scratch again, just to be certain. >> So far so good. >> >> Good to see the more recent activity. I'd taken the source URL from the >> project page and overlooked the Google Code notice. >> >> I think that after 12/12 rolls around and SQLite 3.7.15 is released, it >> might be worth updating the SQLite JDBC that VuFind ships with. >> >> Best, >> >> -Tod -- Mark Triggs <ma...@di...> |