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: sqlite-users-boun...@sqlite.org on behalf of Eric Smith
Sent: Sat 6/19/2010 11:58 AM
To: Jay A. Kreibich
Cc: sqlite-users@sqlite.org
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
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
