Space problems with MEMORY tables

I created the first tables using InnoDB. I successfully loaded the
data without a problem. Now, to make intermediate processing faster
for the batch of data loaded on a given day, I want to try to use
MEMORY tables. That has worked to a point, but with one problem. Only
half of the data I used with the InnoDB tables gets loaded into the
MEMORY tables when the load aborts with an error about the table being
full. It can't be a question of the amount of memory available since
the machine has 2 GB RAM, and each data file is only about 32 MB. At
the rate we're importing data, it would take a century or more to fill
a disk that comes with a new entry level machine! That makes it all
the harder to understand wy I would encounter problems with tables
being full.

My first guess is that this is a configuration issue, but I haven't
fund the relevant documentation for how the maximum table size is
determined. I'd assumed that the tables could be filled until the
available disk space has been consumed, except for memory tables which
would be determined by the available memory. I guess I was mistaken,
but if so, what is the truth?

What part of the documentation talks about issues related to tables
being full?

What is the best way to address this issue?

Thanks,

Ted

"Ted" <r.ted.byers@rogers.com> wrote:

> I created the first tables using InnoDB. I successfully loaded the
> data without a problem. Now, to make intermediate processing faster
> for the batch of data loaded on a given day, I want to try to use
> MEMORY tables.

I guess this won't help much (but this depends on what you mean with
"intermediate processing"). If you want to "just load" data into tables
you should load directly. There is absolutely no benefit from loading
data into intermediate MEMORY tables and copy it into the real INNODB
tables later. The manual contains lots of tips on howto load data fast:
RTFM here: http://dev.mysql.com/doc/refman/5.0/...ert-speed.html

Since you are using InnoDB: make sure AUTOCOMMIT is turned off for
loading data into InnoDB tables.

> That has worked to a point, but with one problem.
> ... the load aborts with an error about the table being
> full.

MEMORY tables have a (configurable) size limit. RTFM here:
<http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html>

> My first guess is that this is a configuration issue, but I haven't
> fund the relevant documentation for how the maximum table size is
> determined. I'd assumed that the tables could be filled until the
> available disk space has been consumed, except for memory tables which
> would be determined by the available memory. I guess I was mistaken,
> but if so, what is the truth?

You guessed correctly for on-disk-tables - with few exceptions, read
below. However in most cases it is not a good idea to allow unlimited
use of memory by the database server. So all allocation of memory in
MySQL is limited by respective configuration settings. RTFM here:
<http://dev.mysql.com/doc/refman/5.0/en/memory-use.html>

For most on-disk-tables there is no explicit size limit. Your tables
can grow as long as the corresponding files can grow on your disk.
There are few exceptions though:

1. for efficiency MYISAM data files will be addressed using 32 bit
pointers by default. That means you have a soft limit of 4GB data
per MYISAM table. To overcome this limit you have to explicitly
specify number of rows and avg. row size in ALTER/CREATE TABLE.
If the product of both numbers exceeds 4GB, this MYISAM table will
use 48 bit pointers.

2. InnoDB stores all data/indexes together in one or more tablespaces.
Tablespaces can have a fixed size, in that case there is a size
limit for all database objects. Usually one has the "autoextend"
option in place, that allows a tablespace to grow.

There are some more limits, most of them specific to the storage engine
used. RTFM here:
<http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html>


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/