BDB tables can have up to 31 indexes per
table, 16 columns per index, and a maximum key size of 1024
bytes.
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's
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
tbl_name is slow for
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 13.5.2.1, “ANALYZE TABLE Syntax”, and
Section 13.5.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 5.9.1, “Database Backups”.
Warning: 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 tables, for which
mysqld waits for sufficient free disk
space before continuing.
