"Ted" <r.ted.byersrogers.com> wrote:
I guess this won't help much (but this depends on what you mean with> 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.
"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: [url]http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html[/url]
Since you are using InnoDB: make sure AUTOCOMMIT is turned off for
loading data into InnoDB tables.
MEMORY tables have a (configurable) size limit. RTFM here:> That has worked to a point, but with one problem.
> ... the load aborts with an error about the table being
You guessed correctly for on-disk-tables - with few exceptions, read> My first guess is that this is a configuration issue, but I haven't
> fund the relevant doentation 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?
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:
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:
Axel Schwenke, Senior Software Developer, MySQL AB
Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]