> You should embed the inserts into a transaction. Otherwise every insert > is a > separate ACID transaction = 2 disk spins. > > > I thought putting 128 of the inserts between "BEGIN;" and "COMMIT;" did > that. Am I confused?
No, what you did was correct. Just for grins: If you have a 7200rpm drive you should theoretically be able to get: 7200 rpm / 2 rotations per transaction = 3600 transactions / minute 3600 transactions per minute / 60 seconds per minute = 60 transactions per second. The limiting factor would seem to be how many inserts can you generate and push through in 1/60th of a second. > > This may have been the secret (and what Jay was trying to tell me > above). I pulled out the 'KEY' and the 'NOT NULL' on str and parent, > and added the PRIMARY KEY. Performance started out somewhat higher, > around 1000 inserts per second and seems to have leveled off at 500+ > inserts per second. I'm already up to 500,000 entries in my test. I'd > like even better performance, but this is passable. I have not started > the testing of my query code to see if there are any new problems. On very large loads it's much faster to turn off indexes, load the data, then create the index you'll use to read it. You don't pay for the index tree rebalancing over and over on all the inserts. You might not be able to do that in your case since your data is inter-related. Sorry if I was too opaque in my response! Glad you got it fixed.
