MySQL Forums
Forum List  »  InnoDB

Re: innodb_buffer_pool_size and relationship to max_heap_table_size
Posted by: Rick James
Date: February 07, 2014 05:21PM

> So looking at a pool allocation of about 85G.

That's probably ok for a 96GB machine. Also set innodb_buffer_pool_instances = 16

max_heap_table_size has only a couple of uses:
* Limitation on each MEMORY (not InnoDB) table
* Limitation on temporary tables that arise from complex SELECTs -- such a table may be implemented as MEMORY or (as a fallback) as MyISAM.

There is almost never a reason to set max_heap_table_size higher than, say, 1G.

> In theory this should allow all these large tables to be cached - is this correct?

InnoDB does all of its caching (for both data and indexes) at the block (16KB) level in the buffer_pool.
The two uses (above) of max_heap_table_size are not "caches", per se.

> I have some big tables ( ie. ranging from 1G to 6G currently)

Not really relevant. The SELECTs on those tables _might_ be worth looking at. EXPLAIN SELECT... and SHOW CREATE TABLE are important analysis tools.

> Is there anything else to be considered?

* Is replication involved? If so ...
* How fast (queries/sec) will you be hitting the data? If more than ... then ...

If you provide SHOW VARIABLES and SHOW GLOBAL STATUS, I will look around for more stuff. The buffer_pool is the number-one thing to get right.

It smells like Data Warehousing; is it?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: innodb_buffer_pool_size and relationship to max_heap_table_size
1074
February 07, 2014 05:21PM


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.