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> select count(*) from Item where issue between 97 and 155;
+----------+
| count(*) |
+----------+
| 78158 |
+----------+
1 row in set (0.71 sec)
mysql> insert into jontmp SELECT Item.id, 0 AS relevance, NULL AS description,
NULL AS docinfo FROM Item WHERE issue BETWEEN 97 AND 155;
ERROR 1114: The table 'jontmp' is full
mysql>
mysql>
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!
If i create the table as MyISAM instead of HEAP, there's no problem, and the
files created are:
{mysql} gnpd$ ll jontmp*
-rw-rw---- 1 mysql mysql 1563160 Jun 20 14:25 jontmp.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 20 14:25 jontmp.MYI
-rw-rw---- 1 mysql mysql 8652 Jun 20 14:25 jontmp.frm
{mysql} gnpd$
although this doesn't agree with my estimate for the table size, it's still
significantly less than the 16M limit, but the insert fails if the table is of
type HEAP
If I create the table as TEMPORARY and don't specify a type, it also works fine
(presumably because the table is automagically converted to MyISAM when the size
hits the HEAP table limit)
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
Why should such a simple query be creating such a huge HEAP table?
I get identical results on two different machines with the same data, where the
version info is:
mysql Ver 11.12 Distrib 3.23.32, for unknown-freebsdelf3.4 (i386)
and
mysql Ver 11.12 Distrib 3.23.32, for unknown-freebsdelf4.0 (i386)
thanks for any help,
Jon
Notice
********
This email may contain information that is privileged,
confidential or otherwise protected from disclosure. It
must not be used by, or its contents copied or disclosed
to, persons other than the addressee. If you have received
this email in error please notify the sender immediately
and delete the email. Any views or opinions expressed in
this message are solely those of the author, and do not
necessarily reflect those of Mintel International Group Ltd.
Warning
**********
It is the responsibility of the recipient to ensure that
the onward transmission, opening or use of this message
and any attachments will not adversely affect their systems
or data. Please carry out such virus and other checks, as
you consider appropriate.
---------------------------------------------------------------------
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
