0

My understanding is even if I need to read single record like select name from customer where id=1, I need to read complete specific block(block address can be found from index on column ID) in memory. Then MySQL goes through all records on that block to find that specific record. Is that correct ?

I believe default block size where MySQL write on disk is of 16KB(from google). Also from diff sources on google I came to know on an average time taken to read 100MB of data from disk(HDD) is around 1 sec . It means an an average seek time to read the block should be around 16/(100* 1000) secs. correct ?

0

Seek time on a spinning disk is a few milliseconds -- a combination of the arm motion and the rotational delay waiting for the sector(s) to come under the read head.

  1. arm motion (a few ms).
  2. 10K RPM (rotations per minute) = 667/sec --> 1.6 ms. Consider half that for the average delay.
  3. fetch the sectors. One sector is 512 bytes; 32 sectors needed for an InnoDB 16KB block. (Timing depends on how many sectors per track.)
  4. Checksum and transfer to RAM
  5. Poke around in the block to find the row(s) desired.
  6. Work with the rows.

10ms is a simple, and often accurate enough, Rule of Thumb for the total time to read 1 block.

The 100MB/1s is probably predicated on optimal conditions of streaming consecutive sectors from each track, and hitting consecutive tracks -- thereby eliminating the first 2 steps in my list. These are often the most costly two.

SSDs replace the first 2 steps by a much faster random fetch.

Yes, all row actions in MyISAM's InnoDB involve first having the desired 16KB block(s) sitting in the buffer_pool (in RAM). Blocks will be fetched as needed, not pre-fetched, so don't expect the benefits of "streaming". Also, most tables are not laid out on disk consecutively, so there will be frequent arm and/or rotational delays. InnoDB is more optimized for efficiency when handling multiple threads than for speed of a single thread.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.