In learning about how data in a database is saved to disk, I came across the slide below. Earlier in the presentation they mention a block size is roughly 512 to 8,192 bytes. I haven't worked with files much before, I basically think about programming in terms of main memory access. But the presentation says to transfer 1 block into main memory it takes about 1ms. This is opposed to nanosecond-scale main-memory modifications on individual records.

So the slide below says that to modify a single record (a single column of a record even), you must save an entire block back to disk. I assume this is for every single change you make. So for every network request that comes into the database calling UPDATE, that is 1 block read and 1 block write (assuming it's for one record in one table). That is ~2ms per update. If you have 100k requests per second, that's 100 requests per millisecond. Assuming each request did 1 record update, that means you probably have 50 databases handling the request load.

I know my math and assumptions are probably wrong. Which is why I am asking this question:

  1. If you really must read and write an entire block per modification. It seems like you could read just the slice you wanted instead.
  2. How a database optimizes updates so you can fit more into that ~2ms window per update. I can imagine if you batch update a few records, they all are in different blocks, so that means even slower time.

enter image description here

  • 1
    Your information is missing a few things like UNDO and log files. Oh, and Oracle Data Blocks are 2k to 32k – Michael Kutz Jul 1 '18 at 23:22

Your Answer

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

Browse other questions tagged or ask your own question.