Running large DB's on FreeBSD

[Gavin Atkinson]

[ Replying offlist as pretty much all my points have already been said
by others, so I'm just trying to help reinforce what others have said ]

On Mon, 2006-10-23 at 19:01 -0400, Mike Jakubik wrote:
>     I am in the process of implementing a fairly large mysql server for 
> an even larger company, and naturally i want to use FreeBSD. The 
> hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM,  7 x 15k 
> rpm disks in a RAID5 setup. I'm not exactly informed as to the specific 
> workload yet, however i know the database will have several million rows 
> and be larger than 10GB.

Go for RAID10 rather than RAID5 - in my testing with an LSI LSI MegaRAID
SCSI 320-2 RAID card, this gave roughly 4-5x speed benefit over RAID5,
on the FreeBSD-based database mentioned below.  You'd also want to use a
64 bit operating system on that server, otherwise it will perform far
worse than expected.

>     So, first of all, am i crazy for choosing fbsd+mysql for this rather 
> than something like Solaris + Oracle? :)

I would suggest either FreeBSD/PostgreSQL or Solaris/Oracle.  I have
several databases in excess of 30 gig on each and both perform well once
tuned.  The largest PostgreSQL database I set up tops 100G and grows
relatively steadily.  I've been very impressed with PostgreSQL, and may
well be replacing some of my Oracle licenses with it in the future.

You don't say what data you will be storing, but this may also affect
the decision.  Do you need to easily do full-text searches?  Are you
storing spacial/geographic (GIS) data?  Think about backing up the data
too - can the database be taken down for cold backups or must it be 100%
available?  And can you see a need for multiple servers in the future
for redundancy?  The answers to these questions may influence your
choice more than anything else.

It is probably worth your time testing the three options - I believe
Oracle and Solaris can both be downloaded freely for evaluation
purposes.  Write a few small scripts to basically hammer the database
with selects, inserts and deletes, and see which performs best.  Pull
the power on the server a few times and see which recovers best.  I
suspect you may go off MySQL.

After having being burned several times with MySQL (data loss,
unexplained slowdowns and general lack of scalability with growing
database sizes), I must be honest and say I haven't tried it for a
while, though I also have no desire to as PostgreSQL has never let me
down yet.

Summary:  FreeBSD is a great platform for this, but I would seriously
reconsider RAID5 and MySQL.  No matter which option you choose, be
prepared to put considerable effort into tuning the OS and database.

Hope that helps,

Gavin