Database Dilemma... Please help.

Michael L Torrie torriem at chem.byu.edu
Thu Apr 6 12:34:53 MDT 2006


On Thu, 2006-04-06 at 12:07 -0600, Jason Jones wrote:
>   Fred is luckily open-minded enough to accept the fact that MySQL ($0.00)
> is better than MS-SQL ($15,000.00) at the current time due to our lack of
> data.  However....  He's pretty convinced that this is surely not going to
> be the case when the data grows.
> 
> Fred has concrete evidence of his ability to handle more than 100 million
> rows of data per table with MS-SQL with little to no loss of speed.

I don't know a lot about databases, but I'll pass on what Mark Lewis
(some of you may know him) said.  Mark works with large (multi-gigabyte
databases) on a daily basis, so he has some knowledge of what he speaks.

According to Mark, MS-SQL, MySQL, Sybase and DB2 all suffer from the
same fatal flaws.  These flaws involve how the database engine employs
locking to create coherency in the data  despite having multiple,
simultaneous updates to the data.  The upshot of all this is that under
heavy load with many (how many he didn't say) simultaneous writes to the
database, all of these databases mentioned will be brought to their
knees.  He has several situations where this is the case in his
projects.

I am sure that MS-SQL, Sybase, and DB2 all can scale quite nicely as far
as size goes (as long as you are aware of the other major problem) but
I'm not at all sure about MySQL.  They say MySQL is gaining enterprise
features, but I'm not so sure about that, especially after talking to
Mark.

> 
> I'm dead set on keeping my OSS databases, but am having a hard time finding
> concrete evidence that either Postgres OR MySQL can handle more than 100
> million rows of data per table without suffering speed hits.

There is only one way to find out.  Internal testing.

According to Mark, PostgreSQL 8.x now has quite a bit of speed increase
while maintaining the other enterprise features.

If you are looking for a large, scalable database, there's really only
two possibilities.  Oracle and PostgreSQL.  Both of which support
transactions and can do coherency without any locks (they do use
slightly different algorithms for this).  In other words you can have
two people write to the same table (same row even) without needing to
lock (and block).  Speed-wise, though, both will be slower than MySQL.
But they will both scale without losing a lot of performance.

> 
> Can anyone here point me to something, somewhere that gives numbers on any
> OSS datbase handling that amount of data and maintaining good numbers on
> speed, with possible hints as to its configuration?

Sorry, just here-say.  Besides that, numbers from another source won't
necessarily match your situation.  There are things like hardware,
indexing, coherency mechanisms that can all be tuned.

> 
> I've personally never handled any OSS db with more than a couple hundred
> thousand rows TOTAL, (but have around 3 years exp. handling many various
> smaller dbs) and am kind of twitchy about what's going to happen with our db
> as it grows exponentially to hundreds of millions of rows.

I have MySQL 4.1 running with a 4 million row table (about 2.5 GB) and
it is very very very slow.  On 10k SCSI disks even.  5.0 should be much
faster.

> 
> Hardware is not an issue.  Disk space is not an issue.  The only issue is
> whether MySQL (or PostgreSQL) can be properly configured to handle hundreds
> of millions of rows per table without hacking it into some slashdot-esque
> frankenstein configuration.

Every database engine needs to be tuned.  It will take some work and
time on your part.

> 
> Any takers for this one?  I'm kind of scared I'm going to lose the CEO on
> this battle and switch to MS-SQL.... I'm dealing with a guy who is extremely
> competent in MS-SQL and has demonstrated abilities to handle any amount of
> data.  If I can demonstrate the same ability with an OSS solution, I'm sure
> I'll win and keep the OSS solution, due to the obvious financial advantages.

Well in the long run does it really matter if they use MS-SQL?  Should
the apps you are writing be SQL agnostic anyway?

Michael


> 
> Thanks anyone who points me to any helpful information.
> 
> --Jason
> 
> PS - I have a pretty good amount of experience with MySQL, but am certain
> PostgreSQL is just as good.  If information can be given about *any* OSS db
> solution, I'd be most grateful.  Thank you.
> 
> /*
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
> */
> 




More information about the PLUG mailing list