5

Say I have a column of type VARCHAR(32) in a mySQL database.

On Spetmeber 1st I decide to store the string "tea", but on September 2nd I decide to update it to "coffee".

Clearly, if all the records in this table were squished right up against each other, and we tried to lengthen one record by 3 bytes, then all the records that appeared after this one would need to shift down by 3 bytes. Of course, this is ridiculous; there is no way any DBMS would ever resort to shifting thousands of possible entries.

So what exactly does mySQL do in this eventuality? Does it behave the same way for TEXT and BLOB types?

EDIT: After reading this a day later, I realized that this question was fairly ambiguous. Here is an example that I hope will clear things up:

Say I have a table, fav_drinks with two columns:

  • user_id, which is an INTEGER
  • drink, which is a VARCHAR(32)

Pretend that this table is stored like this in memory:

[1,"juice",2,"tea",3,"soda",4,"hot chocolate"]

That is, all records are stored sequentially one after the other. If we need to update user 2's favourite drink from "tea" to "coffee", in theory we would need to shift down the entries for users 3 and 4. Of course, I don't think this is what would happen in a real database.

So, to reiterate the question, how does mySQL manage this specific case where one table entry suddenly requires more memory?

6
  • You're thinking in terms of a spreadsheet and you need to widen one column. Databases don't normally work that way (except, maybe, for old DBF files). Every row in the database can be of different size. By storing the length of the row in a fixed position relative to the row, you can go the next one even if they don't all have the same length.
    – joanolo
    Jul 2, 2017 at 18:10
  • Okay. So then what does the database do in this case? I get that the rows don't all have to have to the same size. What I'm wondering is what does mySQL do when one particular row needs to be made longer?
    – Mahkoe
    Jul 3, 2017 at 10:36
  • I'm not sure if this is how it works but I think they work by pointers. For instance, in your example, the '1' and the 'juice' would be a pointer to a memory location stored elsewhere. Then expanding it would just be a simple matter of expanding the other location. Now comes the other issue of fragmentation. There are so many ways of solving fragmentation efficiently. Look up on the way Linux avoids fragmentation with its ext file system. Anyways, this is how I predict it to work, not sure if this is how they actually do it. Jul 3, 2017 at 11:14
  • 1
    At least in MSSQL, this is handled by page splits. The data page that contains the information you updated would now include a reference to the next data page that would contain any "overflow". Ideally, an index defragmentation task (you do have a defragmentation scheduled?) comes by at a later date and will readjust all the data pages for optimum storage. I'm sure that MySQL operates similarly. Jul 3, 2017 at 13:16
  • So, under this setup, would I be correct in saying that each row of the table has the same length? The "overflow" memory is of course varying in size?
    – Mahkoe
    Jul 3, 2017 at 14:01

1 Answer 1

5

MySQL's InnoDB stores rows in blocks of 16KB each. Within a block there could be a few big rows or hundreds on narrow rows. Almost always, there is some spare room in a block. When a column in a row is changed, the row is rewritten in the same block (if possible) and things are shuffled around within the block to make room. (The exact details are buried in the code.)

If so much text (or blob or whatever) is added, that there is not room in the block, then there is a "block split". This is where the rows in the original block are split between two blocks.

Blocks are stored in a B+Tree arrangement of blocks. (See Wikipedia.) Therefore, the blocks are not necessarily stored consecutively, but they can be referenced consecutively. Thus, a block split in a billion-row table is about the same small amount of work as in a hundred-row table.

Another aspect is the "MVCC" that goes on to allow for multiple transactions to touch the same rows at the same time. This leads to not just replacing a row with the modified value, but actually hanging onto the previous copy of the row until the transaction that needs it is finished. But again, the spare room in a block, plus block splits, plus the BTree organization handles this.

So, your next question is about deleting? Well, if the text is shrunken a lot, or a bunch of rows are DELETEd, then it seems like a block might be shrunken a lot, possibly even emptied? Yes. In this case, InnoDB checks to see if two adjacent blocks are small enough to combine, and does so, thereby freeing up one of the blocks for future use. (This freed up block is kept in the tablespace and not given back to the OS, hence ibdata1 never shrinks; etc.)

All of that stuff happens under the covers, and we don't need to worry about it.

Really large records: For example a row with a MEDIUMTEXT column with 1MB of data won't fit in 16KB. What to do? Well, the bulky columns are actually stored in other blocks, separate from the main part of the record. This leads to some confusing limitations on the maximum size of a row and some unexpected performance issues. Again, this is handled transparently, and the unit of allocation is 16KB.

While I am at it, note that the structure of an INDEX is essentially identical to that of a table -- a BTree of 16KB blocks that play block split/merge games under the covers as the index entries are added/deleted for row inserts/deletes.

2
  • But I thought the maximum row size is 64K not 16K?
    – SOFe
    Jul 19, 2018 at 4:19
  • 1
    @SOFe - The max row size is much more complex than that. InnoDB blocks things in 16KB blocks, but won't allow a row to be more than about 8KB. Columns that won't fit (big varchars/text/blob) are stored elsewhere.
    – Rick James
    Jul 19, 2018 at 4:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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