Re: At Performance Limit for 2 GB RAM?

[Nick Matheson]

KimSeong-

Thanks for the response. Sorry I am a little slow in getting back to you, but rebuilding the indexes takes a while. I had already tested the index both ways you suggested originally but just to make sure I changed it back again, and still no luck in fact it seems to be about 25-50% slower than the other index.

I suspected the (stn_id, metadata_id) would perform better as well because of the lower cardinality of stn_id (4000 items) to that of metadata_id (50,000 items). But strangely the results didn't confirm this.

The y_metadatatemp table is a temporary table more for troubleshooting a more complex query so I didn't think to index it, but indexing it doesn't show any significant performance increase.

If you have any other ideas I would be interested in hearing them. I have a white paper on a Dell/MySQL benchmark that shows a database with sizes in the same ball park I have here sustaining 1900 transactions per minute with an average response time of 1.217 (s) per query. (The full details of the test are a little vague, but they lead me to believe that the times I am seeing for comparable row quantities and hardware are a bit low.)

Thanks again,

Nick

p.s. Anyone else have any input?