MySQL Forums
Forum List  »  InnoDB

Re: random-access I/O
Posted by: Rick James
Date: February 09, 2012 10:52AM

Let's look at 3 cases:

** 300GB of data, 1GB of buffer_pool --> This means that a _random_ read will have to hit the disk 299/300 times. If you double the buffer_pool_size, then it becomes 298/300 -- That is _not_ a significant improvement.

** If you are always reaching into one spot (or a small number of spots) in the table, then even 1GB buffer_pool might suffice to make all accesses non-I/O. (This does not seem to be your case.)

** In between -- some randomness, but not totally random. Or some mixture of purely random and "hot spots". In these cases, a bigger buffer_pool will show a noticeable improvement.

> Also it's not possible to have multiple threads, since this way I will not add the records sorted by ID.
On the contrary... Each thread can do its own sorting. This gives you some benefit of sorting, and some benefit of multiple threads.

Let's pursue the PARTITION questions in one of your other threads:
http://forums.mysql.com/read.php?106,514281,514281

I still need to see the TABLE STATUS.

Going way back...
> More specifically the SELECT joins 2 tables and afterwards sorts the ouput by the primary key of the first table.
Why copy the record over? Can't you simply copy over the ids, then JOIN back to the original data?

Options: ReplyQuote


Subject
Views
Written By
Posted
3570
January 31, 2012 03:50AM
1081
January 31, 2012 08:25PM
1019
February 01, 2012 05:19AM
982
February 03, 2012 01:41AM
1240
February 03, 2012 02:39AM
1064
February 03, 2012 11:08PM
1010
February 04, 2012 08:54AM
1533
February 05, 2012 12:56PM
1029
February 08, 2012 07:22AM
1487
February 08, 2012 11:17PM
926
February 09, 2012 05:30AM
1011
February 09, 2012 01:48AM
846
February 09, 2012 01:58AM
Re: random-access I/O
1056
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.