MySQL Forums
Forum List  »  InnoDB

random-access I/O
Posted by: Basilis Papadopoulos
Date: January 31, 2012 03:50AM

Hi everyone!

I previously posted a question concerning the performance of mass inserts on Innodb tables by using INSERT...SELECT, nevertheless it seems that the speed problem was a result of "random access I/O" caused by the SELECT part. More specifically the SELECT joins 2 tables and afterwards sorts the ouput by the primary key of the first table.

The join is optimized and the proper indexes are used, nevertheless it results on trying to access several different pages from Hard Disk, almost in a random pattern. The output of the SELECT contains few INTs, 1 varchar (255) and 2 Medium Blobs.

What would you recommend me to do in order to improve the performance?

1) Increase the number of records that I request on the SELECT part? This way I will increase the probability of retrieving more table rows from the Hard disk pages that I request. The trade-off on this case is that I request even more pages from HD and I need to sort more records afterwards.
2) Decrease the number of records that I request on the SELECT part? This way I request far less pages at each time, I can sort the outcome faster but at the same time I reduce also the throughput and the amount of data that I feed on the INSERT part.

Testing is extremely difficult since the DB is huge (+300GB) and the above scripts are actually used to reorder the DB based on a new primary key.

Are there any parameters, configurations or buffers that can help me increase speed? Do you have any other suggestions?

PS: I apologize for posting a new question but I think that this topic is significantly different from the previous one.

Options: ReplyQuote

Written By
random-access I/O
January 31, 2012 03:50AM
January 31, 2012 08:25PM
February 01, 2012 05:19AM
February 03, 2012 01:41AM
February 03, 2012 02:39AM
February 03, 2012 11:08PM
February 04, 2012 08:54AM
February 05, 2012 12:56PM
February 08, 2012 07:22AM
February 08, 2012 11:17PM
February 09, 2012 05:30AM
February 09, 2012 01:48AM
February 09, 2012 01:58AM
February 09, 2012 10:52AM

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.