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
