Hello.
On Wed, Jun 20, 2001 at 02:46:18PM +0100, [EMAIL PROTECTED] wrote:
> I seem to be reaching the max_heap_table_size with heap tables that should be at
> least 10 times smaller than the max size
>
> The following shows what happens, note the error after the insert statement:
>
> mysql> CREATE TABLE jontmp (id INT(11) UNSIGNED NOT NULL , relevance TINYINT
> UNSIGNED NOT NULL, description VARCHAR(255), docinfo VARCHAR(33) BINARY)
> type=heap MAX_ROWS=150000;
> Query OK, 0 rows affected (0.01 sec)
>
[...]
> mysql> show variables;
> [snip]
> | max_heap_table_size | 16777216
> [snip]
>
> how can the table be filling up?
>
> As I see it, the row size should be 7 bytes (INT + TINYINT + VARCHAR + VARCHAR)
> = (4 + 1 + 1 + 1)
> and the table size should be 78158 * 7 = 547106 bytes,
> which is a thirtieth of the max_heap_table_size variable!
>From the section about HEAP tables in the fine manual
(http://www.mysql.com/doc/H/E/HEAP.html):
* HEAP tables use a fixed record length format.
This means, that VARCHAR(255) will need 255 bytes. So the records size
will be about 4+1+255+33 = 293, and therefore the table size 78158*293
= 22900294 > 16777216.
[...]
> Increasing max_heap_table_size to 32M works fine.
> Trying different values of max_heap_table_size I've found that the critical
> value is somewhere between 20M and 24M
[...]
... which confirmes above calculation.
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
