Each BDB
table is stored on disk in two
files. The files have names that begin with the table name and
have an extension to indicate the file type. An
.frm
file stores the table format, and a
.db
file contains the table data and
indexes.
To specify explicitly that you want a BDB
table, indicate that with an ENGINE
table
option:
CREATE TABLE t (i INT) ENGINE = BDB;
The older term TYPE
is supported as a synonym
for ENGINE
for backward compatibility, but
ENGINE
is the preferred term from MySQL
4.0.18 on and TYPE
is deprecated.
BerkeleyDB
is a synonym for
BDB
in the ENGINE
table
option.
The BDB
storage engine provides transactional
tables. The way you use these tables depends on the autocommit
mode:
If you are running with autocommit enabled (which is the
default), changes to BDB
tables are
committed immediately and cannot be rolled back.
If you are running with autocommit disabled, changes do not
become permanent until you execute a
COMMIT
statement. Instead of
committing, you can execute
ROLLBACK
to forget the changes.
You can start a transaction with the
START
TRANSACTION
or
BEGIN
statement to suspend autocommit, or with SET
autocommit = 0
to disable autocommit explicitly.
For more information about transactions, see
Section 12.3.1, “START TRANSACTION
,
COMMIT
, and
ROLLBACK
Syntax”.
The BDB
storage engine has the following
characteristics:
BDB
tables can have up to 31 indexes per
table, 16 columns per index, and a maximum key size of 1024
bytes (500 bytes before MySQL 4.0).
MySQL requires a primary key in each BDB
table so that each row can be uniquely identified. If you
don't create one explicitly by declaring a PRIMARY
KEY
, MySQL creates and maintains a hidden primary
key for you. The hidden key has a length of five bytes and
is incremented for each insert attempt. This key does not
appear in the output of SHOW CREATE
TABLE
or DESCRIBE
.
The primary key is faster than any other index, because it is stored together with the row data. The other indexes are stored as the key data plus the primary key, so it is important to keep the primary key as short as possible to save disk space and get better speed.
This behavior is similar to that of
InnoDB
, where shorter primary keys save
space not only in the primary index but in secondary indexes
as well.
If all columns that you access in a BDB
table are part of the same index or part of the primary key,
MySQL can execute the query without having to access the
actual row. In a MyISAM
table, this can
be done only if the columns are part of the same index.
Sequential scanning is slower for BDB
tables than for MyISAM
tables because the
data in BDB
tables is stored in B-trees
and not in a separate data file.
Key values are not prefix- or suffix-compressed like key
values in MyISAM
tables. In other words,
key information takes a little more space in
BDB
tables compared to
MyISAM
tables.
There are often holes in the BDB
table to
allow you to insert new rows in the middle of the index
tree. This makes BDB
tables somewhat
larger than MyISAM
tables.
SELECT COUNT(*) FROM
is slow for
tbl_name
BDB
tables, because no row count is
maintained in the table.
The optimizer needs to know the approximate number of rows
in the table. MySQL solves this by counting inserts and
maintaining this in a separate segment in each
BDB
table. If you don't issue a lot of
DELETE
or
ROLLBACK
statements, this number should be accurate enough for the
MySQL optimizer. However, MySQL stores the number only on
close, so it may be incorrect if the server terminates
unexpectedly. It should not be fatal even if this number is
not 100% correct. You can update the row count by using
ANALYZE TABLE
or
OPTIMIZE TABLE
. See
Section 12.4.2.1, “ANALYZE TABLE
Syntax”, and
Section 12.4.2.5, “OPTIMIZE TABLE
Syntax”.
Internal locking in BDB
tables is done at
the page level.
LOCK TABLES
works on
BDB
tables as with other tables. If you
do not use LOCK TABLES
, MySQL
issues an internal multiple-write lock on the table (a lock
that does not block other writers) to ensure that the table
is properly locked if another thread issues a table lock.
To support transaction rollback, the BDB
storage engine maintains log files. For maximum performance,
you can use the --bdb-logdir
option to place the BDB
logs on a
different disk than the one where your databases are
located.
MySQL performs a checkpoint each time a new
BDB
log file is started, and removes any
BDB
log files that are not needed for
current transactions. You can also use
FLUSH LOGS
at any time to checkpoint the Berkeley DB tables.
For disaster recovery, you should use table backups plus MySQL's binary log. See Section 6.2, “Database Backup Methods”.
If you delete old log files that are still in use,
BDB
is not able to do recovery at all
and you may lose data if something goes wrong.
Applications must always be prepared to handle cases where
any change of a BDB
table may cause an
automatic rollback and any read may fail with a deadlock
error.
If you get a full disk with a BDB
table,
you get an error (probably error 28) and the transaction
should roll back. This contrasts with
MyISAM
and ISAM
tables, for which mysqld waits for
sufficient free disk space before continuing.
User Comments
Add your own comment.