GNU Info
(mysql.info)BDB characteristic
Some characteristic of `BDB' tables:
------------------------------------
* To be able to rollback transactions BDB maintain log files. For
maximum performance you should place these on another disk than
your databases by using the `--bdb_log_dir' options.
* 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.
* Scanning is slower than with `MyISAM' tables as one has data in BDB
tables stored in B-trees and not in a separate data file.
* 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.
* Keys are not compressed to previous keys as with ISAM or MyISAM
tables. In other words, the key information will take a little more
space in `BDB' tables compared to MyISAM tables which don't use
`PACK_KEYS=0'.
* There is 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 optimizer 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 do a lot of `DELETE' or `ROLLBACK':s this number should be
accurate enough for the MySQL optimizer, but as MySQL only store
the number on close, it may be wrong 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.
automatically generated by info2www version 1.2.2.9