MySQL Forums :: InnoDB :: random-access I/O

Advanced Search

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

Subject Views Written By Posted
random-access I/O 2281 Basilis Papadopoulos 01/31/2012 03:50AM
Re: random-access I/O 782 Rick James 01/31/2012 08:25PM
Re: random-access I/O 755 Basilis Papadopoulos 02/01/2012 05:19AM
Re: random-access I/O 719 Rick James 02/03/2012 01:41AM
Re: random-access I/O 772 Basilis Papadopoulos 02/03/2012 02:39AM
Re: random-access I/O 790 Rick James 02/03/2012 11:08PM
Re: random-access I/O 743 Basilis Papadopoulos 02/04/2012 08:54AM
Re: random-access I/O 1105 Rick James 02/05/2012 12:56PM
Re: random-access I/O 732 Basilis Papadopoulos 02/08/2012 07:22AM
Re: random-access I/O 1041 Aftab Khan 02/08/2012 11:17PM
Re: random-access I/O 656 Basilis Papadopoulos 02/09/2012 05:30AM
Re: random-access I/O 672 Aftab Khan 02/09/2012 01:48AM
Re: random-access I/O 586 Basilis Papadopoulos 02/09/2012 01:58AM
Re: random-access I/O 758 Rick James 02/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.