`BDB' or `BerkeleyDB' Tables
============================
Overview of `BDB' Tables
------------------------
version 3.23.34
and is activated in the MySQL-Max binary. This storage engine is
typically 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. The
MySQL source distribution comes with a `BDB' distribution that has a
couple of small patches to make it work more smoothly with MySQL. You
can't use a non-patched `BDB' version with MySQL.
We at MySQL AB are working in close cooperation with Sleepycat to keep
the quality of the MySQL/BDB interface high.
will be
forwarded to Sleepycat who in turn will help us find and fix the
problem. As this is a two-stage operation, any problems with `BDB'
tables may take a little longer for us to fix than for other storage
engines. However, as the BerkeleyDB code itself has been used by many
other applications than MySQL, we don't envision any big problems with
this. *Note Support::.
Installing `BDB'
----------------
If you have downloaded a binary version of MySQL that includes support
for BerkeleyDB, simply follow the instructions for installing a binary
version of MySQL. *Note Installing binary::. *Note `mysqld-max':
mysqld-max.
To compile MySQL with Berkeley DB support, download MySQL Version
3.23.34 or newer and configure `MySQL' with the `--with-berkeley-db'
option. *Note Installing source::.
cd /path/to/source/of/mysql-3.23.34
./configure --with-berkeley-db
Please refer to the manual provided with the `BDB' distribution for
more updated information.
Even though Berkeley DB is in itself very tested and reliable, the
MySQL interface is still considered gamma quality. We are actively
improving and optimising it to get it stable very soon.
`BDB' startup options
---------------------
If you are running with `AUTOCOMMIT=0' then your changes in `BDB'
tables will not be updated until you execute `COMMIT'. Instead of
commit you can execute `ROLLBACK' to forget your changes. *Note
COMMIT::.
If you are running with `AUTOCOMMIT=1' (the default), your changes will
be committed immediately. You can start an extended transaction with
the `BEGIN WORK' SQL command, after which your changes will not be
committed until you execute `COMMIT' (or decide to `ROLLBACK' the
changes).
The following options to `mysqld' can be used to change the behaviour of
`BDB' tables:
*Option* *Description*
`--bdb-home=directory'Base directory for `BDB' tables. This
should be the same directory you use
for `--datadir'.
`--bdb-lock-detect=#'Berkeley lock detect. One of
(`DEFAULT', `OLDEST', `RANDOM', or
`YOUNGEST').
`--bdb-logdir=directory'Berkeley DB log file directory.
`--bdb-no-sync' Don't synchronously flush logs.
`--bdb-no-recover' Don't start Berkeley DB in recover mode.
`--bdb-shared-data'Start Berkeley DB in multi-process mode
(Don't use `DB_PRIVATE' when
initialising Berkeley DB)
`--bdb-tmpdir=directory'Berkeley DB temporary file directory.
`--skip-bdb' Disable usage of `BDB' tables.
`-O Set the maximum number of locks
bdb_max_lock=1000' possible. *Note `bdb_max_lock': SHOW
VARIABLES.
If you use `--skip-bdb', MySQL will not initialise the Berkeley DB
library and this will save a lot of memory. Of course, you cannot use
`BDB' tables if you are using this option. If you try to create a
`BDB' table, MySQL will instead create a `MyISAM' table.
Normally you should start `mysqld' without `--bdb-no-recover' if you
intend to use `BDB' tables. This may, however, give you problems when
you try to start `mysqld' if the `BDB' log files are corrupted. *Note
Starting server::.
With `bdb_max_lock' you can specify the maximum number of locks (10000
by default) you can have active on a `BDB' table. You should increase
this if you get errors of type `bdb: Lock table is out of available
locks' or `Got error 12 from ...' when you have do long transactions
or when `mysqld' has to examine a lot of rows to calculate the query.
You may also want to change `binlog_cache_size' and
`max_binlog_cache_size' if you are using big multi-line transactions.
*Note `COMMIT': COMMIT.
Characteristics of `BDB' tables:
--------------------------------
* To be able to rollback transactions, the `BDB' storage engine
maintains log files. For maximum performance you should place
these on another disk than your databases by using the
`--bdb-logdir' option.
* MySQL performs a checkpoint each time a new `BDB' log file is
started, and removes any log files that are not needed for current
transactions. One can also run `FLUSH LOGS' at any time to
checkpoint the Berkeley DB tables.
For disaster recovery, one should use table backups plus MySQL's
binary log. *Note Backup::.
*Warning*: If you delete old log files that are in use, `BDB' will
not be able to do recovery at all and you may lose data if
something goes wrong.
* MySQL requires a `PRIMARY KEY' in each `BDB' table to be able to
refer to previously read rows. If you don't create one, MySQL will
create an maintain a hidden `PRIMARY KEY' for you. The hidden key
has a length of 5 bytes and is incremented for each insert attempt.
* If all columns you access in a `BDB' table are part of the same
index or part of the primary key, then MySQL can execute the query
without having to access the actual row. In a `MyISAM' table the
above holds only if the columns are part of the same index.
* The `PRIMARY KEY' will be faster than any other key, as the
`PRIMARY KEY' is stored together with the row data. As the other
keys are stored as the key data + the `PRIMARY KEY', it's
important to keep the `PRIMARY KEY' as short as possible to save
disk and get better speed.
* `LOCK TABLES' works on `BDB' tables as with other tables. If you
don't use `LOCK TABLE', MySQL will issue an internal
multiple-write lock on the table to ensure that the table will be
properly locked if another thread issues a table lock.
* Internal locking in `BDB' tables is done on page level.
* `SELECT COUNT(*) FROM table_name' is slow as `BDB' tables doesn't
maintain a count of the number of rows in the table.
* Sequential scanning is slower than with `MyISAM' tables as the
data in `BDB' tables stored in B-trees and not in a separate
datafile.
* The application must always be prepared to handle cases where any
change of a `BDB' table may make an automatic rollback and any
read may fail with a deadlock error.
* There are often holes in the `BDB' table to allow you to insert
new rows in the middle of the key tree. This makes `BDB' tables
somewhat larger than `MyISAM' tables.
* The optimiser needs to know an approximation of the 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 optimiser, but as
MySQL only stores the number on close, it may be incorrect if
MySQL dies unexpectedly. It should not be fatal even if this
number is not 100% correct. One can update the number of rows by
executing `ANALYZE TABLE' or `OPTIMIZE TABLE'. *Note ANALYZE
TABLE:: . *Note OPTIMIZE TABLE::.
* If you get full disk with a `BDB' table, you will get an error
(probably error 28) and the transaction should roll back. This is
in contrast with `MyISAM' and `ISAM' tables where `mysqld' will
wait for enough free disk before continuing.
Things we need to fix for `BDB' in the near future:
---------------------------------------------------
* It's very slow to open many `BDB' tables at the same time. If you
are going to use `BDB' tables, you should not have a very big
table cache (like >256) and you should use `--no-auto-rehash' with
the `mysql' client. We plan to partly fix this in 4.0.
* `SHOW TABLE STATUS' doesn't yet provide that much information for
`BDB' tables.
* Optimise performance.
* Change to not use page locks at all when we are scanning tables.
Operating systems supported by `BDB'
------------------------------------
Currently we know that the `BDB' storage engine works with the following
operating systems:
* Linux 2.x Intel
* Sun Solaris (sparc and x86)
* FreeBSD 4.x/5.x (x86, sparc64)
* IBM AIX 4.3.x
* SCO OpenServer
* SCO UnixWare 7.1.x
It doesn't work with the following operating systems:
* Linux 2.x Alpha
* Linux 2.x AMD64
* Linux 2.x IA64
* Linux 2.x s390
* Max OS X
Note: The above list is not complete; we will update it as we receive
more information.
If you build MySQL with support for `BDB' tables and get the following
error in the log file when you start `mysqld':
bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init databases
This means that `BDB' tables are not supported for your architecture.
In this case you must rebuild MySQL without `BDB' table support.
Restrictions on `BDB' Tables
----------------------------
Here follows the restrictions you have when using `BDB' tables:
The effect of this is that `BDB' tables are not movable between
directories!
data directory named `log.XXXXXXXXXX' (ten digits); The `BDB'
storage engine stores unfinished transactions in the log files and
requires these logs to be present when `mysqld' starts.
Errors That May Occur When Using `BDB' Tables
---------------------------------------------
* If you get the following error in the `hostname.err log' when
starting `mysqld':
logs from
your database directory (the files with names that have the format
`log.XXXXXXXXXX') and restart `mysqld'. We would also recommend
you to do a `mysqldump --opt' of your old `BDB' tables, delete the
old tables, and restore the dump.
001119 23:43:56 bdb: Missing log fileid entry
001119 23:43:56 bdb: txn_abort: Log undo failed for LSN:
1 3644744: Invalid
This is not fatal but we don't recommend that you delete tables if
you are not in auto-commit mode, until this problem is fixed (the
fix is not trivial).
[Назад] [Содержание] [Вперед]
| Главная |