Take the 2-minute tour ×
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.

Let's say I have a MyISAM table with a data length of 4.8GB and an index length of 6.2GB. So, a total data size of eleven gig. How much memory would this require, were I to convert it to a MEMORY table? 11 gig, or more?

share|improve this question
    
Be aware that MEMORY tables are temporary in nature, and all data is lost when shutting down the server. If you have a table that's becoming that large, I'm going to guess the data isn't temporary. –  db2 Jan 30 '12 at 15:25
    
Actually, it is - it pulls together data from a dozen tables, so that other tables (which won't reside in memory) can pull consolidated data from it. It's temporary, is created once a week and once those tables get their data it can go away. –  Andrew Jan 30 '12 at 15:29
    
Gotcha, sort of a temporary materialized view, then. Just making sure. –  db2 Jan 30 '12 at 15:33
    
No problem. :) It was a valid concern, and I expected someone to make it, so it was my fault for putting in the question to begin with. –  Andrew Jan 30 '12 at 15:34
    
Can you post the SHOW CREATE TABLE output in your question? –  Derek Downey Jan 30 '12 at 15:49
show 1 more comment

3 Answers

up vote 5 down vote accepted

The exact memory requirement of a row is calculated from the following formula:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*)) 

[src]

ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

So, on a 64-bit machine, replace sizeof(char*) with 8.

You can get an estimate of the length_of_row from the Information Schema:

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM information_schema.tables WHERE  table_schema='foo' AND table_name='bar';

Then you add up all your BTREE keys and then HASH keys. Note that it might be worth it space-wise to convert any keys to HASH, as they require less memory.

I was going to mention the limitation of maximum MEMORY size dependent on max_heap_table_size, but gbn beat me to it.

share|improve this answer
1  
nice answer especially on the indexes (+1 !!!). Hey Andrew, here is a heads up for you: If your queries do range searches, stay away from HASH indexes and use BTREEs only. If your queries do indexes search (eq_ref), vice versa. –  RolandoMySQLDBA Jan 30 '12 at 16:13
add comment

It can't be a MEMORY table (selective quote below) if it exceeds "max_heap_table_size". This is max 4GB for 32 bit

The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To enforce different size limits for MEMORY tables, change the value of this variable

You can set this per table by setting max_heap_table_size per session.

But 12GB or so is a lot of memory for a caching table...

share|improve this answer
    
Hey, good answer on MEMORY table size limitation. +1 !!! –  RolandoMySQLDBA Jan 30 '12 at 16:15
1  
I think you mean the maximum possible max_heap_table_size is 4 GB for 32-bit (and 16 exabytes for 64-bit). –  Matthew Flaschen Jan 30 '12 at 17:51
add comment

@gbn and @DTest provided great answers already on using MEMORY tables in terms of indexes and size limits.

Here is yet another perspective to keep in mind:

The MEMORY storage engine

  • uses full table locking for INSERTs, UPDATEs, and DELETEs
  • Cannot perform concurrent INSERTs
  • uses the hash indexes instead of BTREE indexes by default
  • can use BTREEs indexes, but must be explicitly specified at CREATE TABLE time
  • has no transaction support
  • Single row queries are just great against MEMORY tables, especially using HASH indexes
  • Ranged queries and sequential access are just horrific unless you use BTREE explicitly (more memory consumption required)

Even though you have data in RAM, mysqld will always hit the .frm file to check for the table existing as a reference point, thus always incurring a little disk I/O. Proportionally, heavy access to a MEMORY storage engine table will have noticeable disk I/O.

You must also remember to strike a proper balance of MEMORY table usage with

  • Database caches
    • MyISAM Key Cache
    • InnoDB Buffer Pool)
  • OS caches
  • OS operation
share|improve this answer
add comment

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.