Hello Kevin, I'd write a utility to do it instead of using the command line tool then add logging to the program in order to note progress.
I like the idea of chopping it into smaller parts too. "ON CONFLICT ROLLBACK" You're doing one large transaction and if it rolls back it'll have to undo everything right? I wonder if that's what you're seeing here. You might want to try "IGNORE" to see if you can even run through all the data. Writing a utility for this would let you manage the rollbacks too. C Wednesday, August 3, 2016, 11:00:12 PM, you wrote: KOG> I'm working on a hobby project, but the data has gotten a bit out of hand. KOG> I thought I'd put it in a real database rather than flat ASCII files. KOG> I've got a problem set of about 1 billion game KOG> positions and 187GB to work KOG> on (no, I won't have to solve them all) that took about 4 hours for a KOG> generator program just to write. I wrote code to turn them into something KOG> SQLite could import. Actually, it's import, build a non-primary index, and KOG> alter table to add a column, all in sqlite3. KOG> The database was growing for about 1-1/2 days. Then its journal KOG> disappeared, the file size dropped to zero, but sqlite3 is still running KOG> 100% CPU time, now for a total of 3800+ minutes (63+ hours). The database KOG> is still locked, but I have no idea what sqlite3 is doing, or if it will KOG> ever stop. All partitions still have lots of space left (most of this is KOG> running in a RAID partition of 11 TiB). Here's what I gave to sqlite3 on KOG> my Linux system: KOG> time sqlite3 qubic.db <<EOF KOG> BEGIN EXCLUSIVE TRANSACTION; KOG> DROP TABLE IF EXISTS qmoves; KOG> CREATE TABLE qmoves ( KOG> qfrom CHAR(64), KOG> qmove INT, KOG> qto CHAR(64), KOG> qweight INT, KOG> PRIMARY KEY (qfrom, qmove) ON CONFLICT ROLLBACK KOG> ); KOG> CREATE INDEX IF NOT EXISTS qmoves_by_dest ON qmoves ( KOG> qto, KOG> qweight KOG> ); KOG> CREATE TABLE IF NOT EXISTS qposn ( KOG> qposn CHAR(64) PRIMARY KEY ON CONFLICT ROLLBACK, KOG> qmaxval INT, KOG> qmove INT, KOG> qminval INT, KOG> qstatus INT KOG> ); KOG> .separator " " KOG> .import am.all qmoves KOG> ALTER TABLE qmoves ADD COLUMN qstatus INT DEFAULT NULL; KOG> .schema KOG> COMMIT TRANSACTION; KOG> EOF KOG> Any clues, hints, or advice? -- Teg mailto:[email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
