Hmmm....a 6.5X speed diff between RAM and disk? Sounds pretty good to me. Not sure why you expect better. 10,000/sec is crappy? And you think this because....???? Several things you need to provide. #1 What OS are you on? There are numerous disk speed testing programs depending on your OS. #2 Are you multi-threading? A seperate reader process could help. #3 How many records total? #4 Final size of database? #5 How fast can you read your input file? #6 What happens if you just insert the same records the same # of times #7 What does your CPU usage show? I assume you're multicore (as most are now I think). Depending on what you're doing with this data are you sure you need a database solution? I don't recall you really explaining your ultimate goal... Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: [email protected] on behalf of Eric Smith Sent: Sat 6/19/2010 11:58 AM To: Jay A. Kreibich Cc: [email protected] Subject: Re: [sqlite] unexpected large journal file Jay A. Kreibich wrote: > Try getting rid of the PK definition and see how much that buys you. > It might be worth it, or it might not. and Simon Slavin wrote: > We know you are doing a huge amount of writing to this database. Are > you also reading it frequently ? If not, then it might be worth making an > index on that primary key only when you're about to need it. I think the use case will usually be (only) writes followed by (only) reads. There may be incremental writes later, but they will hopefully be small compared to the initial seed. I intend to create a set of maybe 7 indices to serve the particular queries I think the user intends to run. Let's all hope we can update the db with those indices at a higher rate than the user can generate data. :p I tried removing the PK definition as you both suggested, and the journal stays fixed at less than 20Kb, even against a db size of (at the moment) 37Gb. My insert batch run times are improved by a factor of ~2.5 and seem to be O(1). So, bingo. :) Insert rates are still a rather crappy 10k records/second (when we were in RAM we were doing ~65k recs/sec, which I think was bound by my app's speed). I think I'm at the stupid raid5's limit -- not really sure what I should expect there, or how to find out what I should expect. Anyway, I'll build the indices after the seed. I'll go out on a limb and assume that'll be a lot faster than it would've been under my initial approach. You guys were incredibly helpful -- thanks very much! Eric -- Eric A. Smith Carperpetuation (kar' pur pet u a shun), n.: The act, when vacuuming, of running over a string at least a dozen times, reaching over and picking it up, examining it, then putting it back down to give the vacuum one more chance. -- Rich Hall, "Sniglets" _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
