On Wed, Mar 31, 2010 at 4:02 PM, Mashiah Davidson
<mashiah.davidson(a)gmail.com> wrote:
This figure of 1/8 resources is inaccurate. First, the
limit is set
just to one table, not for all tables user create. Second, when Golem
supposes it will need 4 GB it assumes worst case. Third, Golem at a
time works with just one server (when iwiki spy is switched off), so
even if it takes 1/8 at one server this means 1/24 of all memory
available at s1, s2 and s3.
The question is what we set innodb_buffer_pool_size to. If no one
needs to allocate large memory tables or such, we'd allocate almost
all physical server memory to that variable. (>80% is standard for
all-InnoDB database servers.) In general, MySQL will allocate the
full size of innodb_buffer_pool_size fairly quickly. Memory allocated
there is not available for anything else, including other MySQL things
like creation of temporary tables. The variable cannot be changed
without a reboot of mysqld, it's a fixed amount.
If you *ever* use 4 GB memory tables, even for one single second, we
need to decrease innodb_buffer_pool_size by 4 GB *always*, to avoid
the risk of swapping or memory allocations failing. This is, in a
very real sense, 1/8 of the server's resources that we would have to
reserve for your project. We cannot reduce the buffer pool size only
when you need the extra memory; it's always or never.
If MySQL could automatically reduce buffer pool allocations when large
temporary tables are needed, then there would be little to no issue,
you're right. But that's not the case.