Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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?

share|improve this question
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

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

1 Answer

up vote 3 down vote

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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