2

I have a single InnoDB table on disk, with ~1,107,423 rows and taking up 45.6 MiB disk space. The structure is:

messageid int(11)
signature varchar(255)

When I create a MEMORY-backed version of this table, it takes up 803.5 MiB of memory!

It seems reasonable there'd be some expansion: a lot of extra pointers from the B-tree index or something, but increasing by a factor of 16 seems awfully excessive! What's the reason behind this?

migrated from stackoverflow.com Nov 7 '12 at 11:05

This question came from our site for professional and enthusiast programmers.

  • cache alignment, possibly. you might have only a 16bit int to store, but to prevent crossing certain memory boundaries, mysql might be taking 8 bytes to store it in. – Marc B Nov 6 '12 at 14:28
  • Please post SHOW CREATE TABLE of the table – RolandoMySQLDBA Nov 7 '12 at 19:51
3

VARCHARs are stored with a fixed length in memory, so your max length of 255 will be using a lot of space. Your InnoDB disk table will be storing the rows with a dynamic length.

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html says...

  • MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.

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.