On Fri, 11 May 2001, Kevin McBrearty wrote:
> I have re-read the appropriate manual sections several times, trying to
> decide what is the best option for my database table types. I am using
> 3.32.37 on
> Linux, and am torn between InnoDB, BDB, and Gemini table types. Transaction
> handling is very important for my application (commit, rollback, etc.).
>
> I am leaning toward InnoDB at this point but was wondering if anyone could
> offer any information, other than what is in the manual, regarding the pros
> and cons of these table types.
Aside from what's in the manual, I have a little to offer. I just got
InnoDB working yesterday, so take what I have to say with a grain of salt.
BDB: I've worked with Berkley DB files (ver 1.8 and 2.? directly from C,
not through MySQL) for several years at work, and they're rather fast for
small databases, but the speed (for mixed inserts/lookups) drops off fast
when you start getting past 10K records. By the time you've reached 100K
records, you're running at 1/10th the speed you were at 1K records. For
the tests I was doing, BDB was the one MySQL table type that ran slower
than PostgreSQL (7.1.1) for 250K inserts, and dramatically slower than
all others for selects that didn't involve an index. I didn't even run
the rest of the tests.
Gemini: Currently in Beta testing, with no source code available. You
might be able to get in on the beta program. I did, I figured we could
test it using Linux emulation on FreeBSD. Boy, was I wrong. Not blaming
NuSphere, however. I was just quite surprized when the remote install
hung. After going into the area with the server, I found that some part
of the install had triggered a halt on the server.
InnoDB: Some little voice inside my head is saying it's too good to be
true. I wrote a program to take about a months worth of our live data
(750K records) and stuff it into a database with a perl script via
DBI. InnoDB ran about 50% faster than MyISAM, and about 18 *TIMES* faster
than PostgreSQL. I wasn't expecting InnoDB to be faster than MyISAM,
since InnoDB is transactional and MyISAM isn't, which is one of the
reasons everyone uses to explain why (and when) MySQL is faster than
PostgreSQL.
I'm not claiming that is a fair comparison, as I'm doing more
comparisions, and won't decide until I've crammed an entire years worth of
data through both databases, including all necessary
deletions/purges/lookups, etc, and it could be that the problem with
PostgreSQL was the Pg DBI interface.
A few other differences: Gemini doesn't support BLOBs, and InnoDB is
limited to 8000 byte BLOBs. Gemini is limited to 2G tables, and I'm not
sure about InnoDB. You can have multiple InnoDB files, but I don't know
if a table has to reside entirely in one file.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
