MySQL Forums
Forum List  »  Performance

Re: alter table ... order by ...
Posted by: Nick Matheson
Date: December 28, 2005 04:32PM

James,

Thanks for the suggestion. I was looking into the option of InnoDB. I have been trying to read up on optimizing inserts for InnoDB.

From the articles I have been able to read it looks like people are seeing a max of about 10K inserts per second with InnoDB.

I have only been able to get around 1000 per second, but I am sure that is because of poor tuning on my part.

Questions
1. Can you point me to any good resources on optimizing InnoDB inserts for maximum throughput?
2. Does the 10K per second goal seem reasonable to you? On good hardware 4GB Quad Xeon SCSI RAID.
3. If I know specifics about the data loading pattern is there any way to improve the InnoDB (or MyISAM for that matter) data creation or sorting? See Background below

Thanks,

Nick

p.s. Sorry for the slow response, but I was having problems posting earlier.

Background

1. Data is inserted as follows
a. Every hour the same 250,000 rows added per data table
b. Currently 50 data tables, but will grow to 500 over 2 years
c. This amounts to 125M rows an hour at full build out
2. Rows are queried as follows
a. User requests from 1 to 50 rows of data for each day/hour in a range of dates
b. I have covering indexes so once the data is ordered the queries should perform well

Am I right in thinking that the insertion order is certain to generate a worst case sort performance?

Options: ReplyQuote


Subject
Views
Written By
Posted
3188
December 22, 2005 11:12AM
1274
December 23, 2005 02:24PM
Re: alter table ... order by ...
1379
December 28, 2005 04:32PM
1397
December 30, 2005 10:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.