mysql and volume

[Jerry Feldman]

On Fri, 16 May 2003 08:14:25 -0400
Jerry Feldman <gaf at blu.org> wrote:

> hen at Polaroid on contract, I implemented a database based on Al
> Stephen's public domain btree based system. We used it for drivers
> license demographics in another state. I believe that Ingress had been
> used in another state. When they hit a bug in Ingress, they could not
> get a fix (at least in a reasonable time frame). So, your
> considerations should also be things like backup/restore, and time to
> repair a bug. Relational databases like MySQL may not have the
> performance that a non-relational engine might, but the other
> consideration is the nature of the queries (eg selects). 
My stress test simulated 200 drivers license offices, average of 2
stations per office, 1 transaction every 2 minutes per station. This
comes out to about 24,000 transactions per hour. In this implementation
each dl station was a separate process. In my stress test, I simulated
the transactions. The problems were the locking of the database.
Berkeley DB uses shared mmap for its locks. I could never get Berkeley
to hold up anywhere near 100 processes. The btree database was highly
modified (by me) from Al's original code. I used a series of read/write
locks to allow multiple reads, but single thread the writes. The locks
were global and used flock(). I also tested performance with a database
containing about 2,000,000 records, which was the estimated size of the
database at the end of the contract (eg. Polaroid and that state). The
host machine was a Sun Ultrasparc. The test systems in the lab were a
solaris 7 PC and a debian pc. Most of my testing and development was on
an DEC Alpha Multia running Red Hat 6. The only implementation problem
we had on the homegrown system is that the btree indexes contained a
key/pointer pair. When implementing on the Sparc, we found that this
caused alignment faults, and I had to hack the database code to handle
this. (Eventhough the Alpha had the alignment faults, there was fixup
code to prevent a crash). In retrospect, had I been given a bit more
time I would have restructured the btree indexes to force the proper
alignment of the pointer. Additionally, the locking mechanism for a
database should be record based and not file based. The design of the Al
Stephen's database did not take locking into consideration. 
-- 
Jerry Feldman <gaf at blu.org>
Boston Linux and Unix user group
http://www.blu.org PGP key id:C5061EA9
PGP Key fingerprint:053C 73EC 3AC1 5C44 3E14 9245 FB00 3ED5 C506 1EA9
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://lists.blu.org/pipermail/discuss/attachments/20030516/b2b192d8/attachment.sig>