Where does the time go?

[Mark Modrall]

Hi...

I'm still trying to improve the performance of the example I posted about earlier in the week. Jay's suggestion of using an MD5 hash of the text was some improvement, but it's still taking about 11 minutes per 200,000 INSERT statements which still seems a bit long to me.

I tried using Prepare() to help performance and it did, but there are so many bugs with Prepare() in C# that it's almost impossible to use.

I'm using a C# program running on a windows box connecting to a linux box where the server is. The windows box is using Connector .Net 1.0.4. My app is measuring just the time elapsed executing the INSERT statements (not any ambient processing). The average row size being inserted is about 70 bytes. Client cpu never gets above 25% or about 35% of physical memory available.

The linux server is a quad cpu box with a gig of memory running mysqld 4.1.13. The cpu running the mysqld at the time never gets above 5.5% busy with the inserts; io wait (according to top) never gets above 1.5%.

I lock the table before doing inserts to avoid contention and excess index flushing.

None of the cpus in question are sweating at all, nobody's wanting for physical memory, and at least according to top there's little io wait to speak of. Sure there's some network overhead in having the client on another machine, but it's hard to imagine it would account for such a large bill. I'm at a loss to see where the time's going.

I tried watching active connections in the admin console but dividing 11 minutes by 200,000 the odds of me hitting refresh at just the right moment aren't high. What stats should I be looking for where to tell where the time is going?

Just to refresh, the table being inserted into looks like
CREATE TABLE IF NOT EXISTS inputTable (
qid INT UNSIGNED AUTO_INCREMENT,
input TEXT,
hash TINYBLOB, UNIQUE (hash(16))
);

Thanks
_mark