(mysql.info.gz) BDB characteristics
Info Catalog
(mysql.info.gz) BDB start
(mysql.info.gz) BDB storage engine
(mysql.info.gz) BDB TODO
14.4.4 Characteristics of `BDB' Tables
--------------------------------------
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 definition, and a `.db'
file contains the table data and indexes.
To specify explicitly that you want a `BDB' table, indicate that with
an `ENGINE' or `TYPE' table option:
CREATE TABLE t (i INT) ENGINE = BDB;
CREATE TABLE t (i INT) TYPE = BDB;
`BerkeleyDB' is a synonym for `BDB' in the `ENGINE' or `TYPE' 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 `BEGIN WORK' statement to
suspend autocommit, or with `SET AUTOCOMMIT=0' to disable
autocommit explicitly.
`COMMIT' COMMIT.
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,
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.
* The `PRIMARY KEY' will be faster than any other index, because the
`PRIMARY KEY' is stored together with the row data. The other
indexes are stored as the key data + 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 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 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 `ANALYZE TABLE'
ANALYZE TABLE. and `OPTIMIZE TABLE' OPTIMIZE TABLE.
* Internal locking in `BDB' tables is done at the page level.
* `LOCK TABLES' works on `BDB' tables as with other tables. If you
don't use `LOCK TABLE', MySQL issues an internal multiple-write
lock on the table (a lock that doesn't block other writers) to
ensure that the table will be properly locked if another thread
issues a table lock.
* To be able to roll back transactions, 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. Backup.
*Warning:* If you delete old log files that are still in use,
`BDB' will not be 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 full disk with a `BDB' table, you will get an error
(probably error 28) and the transaction should roll back. This
contrasts with `MyISAM' and `ISAM' tables, for which `mysqld' will
wait for enough free disk before continuing.
Info Catalog
(mysql.info.gz) BDB start
(mysql.info.gz) BDB storage engine
(mysql.info.gz) BDB TODO
automatically generated byinfo2html