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:
- If you really must read and write an entire block per modification. It seems like you could read just the slice you wanted instead.
- 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.