MySQL – How Much Memory Will a MEMORY Table Take Up?

memorymyisamMySQL

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?

Best Answer

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.

Related Solutions

SQL Server – How Much Memory is a Table Taking Up?

with bd as (
    select count(*) as pages_in_memory, bd.allocation_unit_id
    from sys.dm_os_buffer_descriptors bd
    where bd.database_id = db_id()
    group by bd.allocation_unit_id)
select p.object_id,
    p.index_id,
    p.partition_number,
    bd.pages_in_memory,
    au.total_pages as pages_on_disk,
    au.type_desc
from bd 
join sys.allocation_units au 
    on au.allocation_unit_id = bd.allocation_unit_id
join sys.partitions p
    on p.partition_id = au.container_id

MyISAM Performance – Settings to Keep Everything in Memory

MySQL only caches the indexes in MySQL's memory, not the data. The data is only cached by the underlying operating system's filesystem caches. The most important value to tune in my.cnf is key_buffer_size which will allocate memory for MyISAM to cache indexes.

However, unless you have some specific reason for using MyISAM tables you should convert those tables to InnoDB. In this day and age, there are less than a handful of reasons to use use MyISAM and MyISAM is not good for concurrency, data integrity, or crash recovery.

If you switch to InnoDB, the most important setting is innodb_buffer_pool_size which is the internal memory cache that will cache data pages & indexes. Other things to consider are the innodb_log_file_size and make sure to set innodb_file_per_table=1. With 4GB of data+indexes, setting innodb_buffer_pool_size=4G is reasonable. If you are running in a highly concurrent environment, you could experiment with values for innodb_thread_concurrency. My recommendation is to start with innodb_thread_concurrency=0, but you might find it necessary to explicitly set it to be somewhere between 4-8 depending on your workload and version of MySQL.

Best Answer

Related Solutions

SQL Server – How Much Memory is a Table Taking Up?

MyISAM Performance – Settings to Keep Everything in Memory

Related Question