Additional Resources
1307
LOAD DATA INFILE
into this file to load the table from a persistent data source. See
Section 5.1.3,
“Server Command Options”
, and
Section 13.2.6, “
LOAD DATA INFILE
Syntax”
.
• A server's
MEMORY
tables become empty when it is shut down and restarted. However, if the server
is a replication master, its slave are not aware that these tables have become empty, so they returns
out-of-date content if you select data from these tables. To handle this, when a
MEMORY
table is
used on a master for the first time since it was started, a
DELETE
statement is written to the master's
binary log automatically, thus synchronizing the slave to the master again. Note that even with this
strategy, the slave still has outdated data in the table during the interval between the master's restart
and its first use of the table. However, if you use the
--init-file
[409]
option to populate the
MEMORY
table on the master at startup, it ensures that this time interval is zero.
• The memory needed for one row in a
MEMORY
table is calculated using the following expression:
SUM_OVER_ALL_BTREE_KEYS(
max_length_of_key
+ sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(
length_of_row
+1, sizeof(char*))
ALIGN()
represents a round-up factor to cause the row length to be an exact multiple of the
char
pointer size.
sizeof(char*)
is 4 on 32-bit machines and 8 on 64-bit machines.
As mentioned earlier, the
max_heap_table_size
[468]
system variable sets the limit on
the maximum size of
MEMORY
tables. To control the maximum size for individual tables,
set the session value of this variable before creating each table. (Do not change the global
max_heap_table_size
[468]
value unless you intend the value to be used for
MEMORY
tables
created by all clients.) The following example creates two
MEMORY
tables, with a maximum size of 1MB
and 2MB, respectively:
mysql>
SET max_heap_table_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql>
CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)
mysql>
SET max_heap_table_size = 1024*1024*2;
Query OK, 0 rows affected (0.00 sec)
mysql>
CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
Both tables will revert to the server's global
max_heap_table_size
[468]
value if the server restarts.
You can also specify a
MAX_ROWS
table option in
CREATE TABLE
statements for
MEMORY
tables to
provide a hint about the number of rows you plan to store in them. This does not enable the table to
grow beyond the
max_heap_table_size
[468]
value, which still acts as a constraint on maximum
table size. For maximum flexibility in being able to use
MAX_ROWS
, set
max_heap_table_size
[468]
at least as high as the value to which you want each
MEMORY
table to be able to grow.
Additional Resources
• A forum dedicated to the
MEMORY
storage engine is available at
http://forums.mysql.com/list.php?92
.
14.5. The
BDB
(
BerkeleyDB
) Storage Engine
Sleepycat Software has provided MySQL with the Berkeley DB transactional storage engine. This
storage engine typically is called
BDB
for short.
BDB
tables may have a greater chance of surviving
crashes and are also capable of
COMMIT
and
ROLLBACK
operations on transactions.
Support for the
BDB
storage engine is included in MySQL source distributions, which come with a
BDB
distribution that is patched to make it work with MySQL. You cannot use an unpatched version of
BDB
with MySQL.
BDB support will be removed
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
