MySQL Forums
Forum List  »  Memory Storage Engine

Re: table size growth
Posted by: Rick James
Date: March 10, 2012 11:47AM

> The CPU are fine
There's an old saying where I come from: "If it ain't broke, don't fix it."

> shows 17 indexes
Possibly some of those are never used. Do you know about "compound" indexes? Do you understand that INDEX(a,b) can be more powerful than INDEX(a), INDEX(b)?

Use EXPLAIN SELECT ... to see which index a query is using. Usually it will use only one index.

Are the indexes HASH or BTree? I don't know the inner workings of this "hash", but some implementations double the memory footprint all at once at certain points in hash-table growth. BTrees inherently have the concept of a "block"; a block split adds exactly one (sometimes two) more block at seemingly random times during growth. MyISAM uses 1KB blocks, InnoDB uses 16KB, I don't know about MEMORY.

700MB+500MB -- Smells like a hash based on a power of 2 doubling itself.

Do you find that INDEX_LENGTH/1024 is always a whole number? Does it increase in uniform amounts?

Everything is a tradeoff. Memory is burned to avoid CPU. Memory allocation is incremented in chunks to make consistency and/or simplicity in the CPU work. Etc.

MEMORY has a drawback -- Datatypes are fixed length. VARCHAR(100) CHARSET utf8 consumes 302 bytes always. Do you have any VAR... fields? This is another tradeoff. In some cases, it leads users to say "MyISAM is actually better overall than MEMORY."

Options: ReplyQuote

Written By
March 08, 2012 04:53AM
March 09, 2012 09:45AM
March 09, 2012 10:31AM
Re: table size growth
March 10, 2012 11:47AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.