FreeBSD 5.4 or 6 for DB server with 9500S-4 ?

[Nathan Vidican]

ray at redshift.com wrote:
> Hi Olaf,
> 
>   Congrats on getting the 3ware card.  Keep in mind you can download the manuals
> in PDF from 3ware if you want to get a head start on things.  I think when you
> build the RAID in under 10 minutes, you'll be very happy you went with 3ware.
> The 9500S is an excellent card.
> 
Same - trust me on this one, you got them to make the right decision there as 
far as S-ATA RAID goes.

> | 1-What would you recommend: Fbsd 5.4 or 6.0 (we're speaking about the 
> | AMD64 version here, and AFAIK they bought a dual core processor (I think 
> | an Athlon)). I have found mention of the 6.0 version having much better 
> | multithreading mechanisms, and it being better for DB servers, yet, is 
> | it already deemed stable enough for a live DB server? Any ideas???
> 
> My policy is to never be the first kid on the block to test something new -
> especially not in production.  FreeBSD 5.4 has been hammered on for a long time
> and works really well.  5.4 is an evolution of 5.2.1 and 5.3 - all of which are
> very mature and very stable.  I've been running FreeBSD in production since
> version 5.2.1 and have been using FreeBSD since 4.9 (previously we used Redhat
> linux for our production servers).  Anyway, when I build a machine, here is
> exactly what I use:
> 

I disagree. First-off, 6.0-RELEASE may be new to the block, but 6.0 has been 
kicking it almost as long as 5.4-RELEASE has been out. Most of the experiences 
I've heard back from other users are saying 6.0, even RC-1 before -RELEASE 
actually runs better than 5.4-RELEASE. Certain key optimizations in 6.0 will 
help you out, (eg filesystem changes, SMP support, hardware drivers, etc). 
Considering the newer hardware you've chosen (dual core amd64 cpu) - 6.0 is much 
better prepared to handle it in the kernel. I've been running 6.0-RC1 on one of 
the dual opterons we have here now for a couple of months now - performance is 
great and havn't had a moments downtime yet. Seriously, do not discount 6.0 
because it's a '.0' release, really it's not. As Ray pointed out, 5.4 was an 
evolution of 5.2 - 5.3, etc... what he did not potentially realize, is that 6.0 
is of that same evolution. Read the notes as to why the version skipped from 5.4 
-> 6.0, and you'll realize that 6.0 was simply a new version number to a release 
that would have otherwise been say 5.5, the reason for the skip was because of 
the large number of new capbilities and features. And yes, new features generall 
can equal new bugs - but if you're not relying upon them and you're doing the 
same thing you would with a 5.4 machine, then why sacrafice the added filesystem 
performance and hardware support by not running 6.0?


> FreeBSD 5.4 from the 5.4 ISO CD-ROM images.  I don't apply any patches or do any
> CVS stuff.  Just the basic install from the CD's, then tweak the kernel by
> stripping out unneeded drivers.
> 
> PHP 4.3.11 or 4.4.1 (built from source)
> 
> APC built from ports (this is optional, but does speed up PHP by a factor of 2)
> 
> MySQL 4.1.15 (built from Source)
> 
> Apache 1.3.33 (built from source)
> 
> I run SSH to access the machines and use the bash shell.
> 
> That's all I ever use.  Our SSL stuff is handled by dedicated hardware, so I
> don't bother with OpenSSL.  If you need a firewall on the actual server (as
> opposed to having a router provide it), then use ipf and setup an /etc/ipf.rules
> file.  
> 
I agree, short of large security patches, stick with a -RELEASE code. In any 
given production environment stability is key. While cvsup'ing and building the 
new box everyday to stay current sounds good - it is in practice not good for 
production servers - if it isn't broke, don't fix it.

> As far as 6.0 having better threading, that is quite possible.  I personally
> haven't performed any benchmarks on 5.4 vs. 6.0.  However, keep in mind, a lot
> of people get all caught up squeezing the last possible ounce of performance
> from machines, when really that's not the best idea.  Using a properly setup
> FreeBSD machine, you should have no problem handling 2000 to 3000 (if not more)
> connections per second.  I have several machines here running dual AMD opterons
> that have no problem doing 6000 to 7000 httpd connections per second on 1k .html
> files and 
> 
> Same for the MySQL server, you should be able to easily handle 1000 to 2000
> inserts and 4000 to 5000 simple selects per second from your average dual
> Opteron server.
> 
> Even a single Xeon 2.4 Ghz machine can do about 50% to 60% of those numbers
> above.  The point is that once you reach this level, then usually CPU/server
> power isn't your problem.  Typically it's bandwidth.  Even with 500 connections
> per second, it's easy to swamp a 10 or 20 mbit connection.  So if you are using
> anything less than a full 100/mbit connection, then I wouldn't worry too much
> about the threading vs. non-threading stuff.  Most applications run multiple
> processes, more than they do multiple threads.  And like I say, most of the
> time, the problem isn't speed of execution, it's bandwidth.  Either way, at
> least at this point, I would stick with 5.4 myself.  I've yet to see a well
> configured server run much above a load of 3 or 4 when everything is setup
> properly.  
> 
> Now if you are setting up the next google or yahoo, then maybe threading has
> more of an impact.  But even so, keep in mind that multiple threads don't get
> you more CPU power.  It's still the same number of CPU's executing the code -
> albeit perhaps slightly more effectively.  Usually at the end of the day, it's
> better to not try to squeeze every last ounce of power from the machine.  That
> will often times result in instability problems.  You are usually money and time
> ahead by just getting a simple load balancer and tossing a couple of more mildly
> tweaked servers into your cluster.  Breaking the load across 2 or 3 machines is
> always better than trying to tweak out 1 solitary machine to run at 150% of what
> it really likes to run at :-)

All good points, but at 4000+ connections, updates, etc per second... I/O 
becomes the issue not so much bandwidth. So long as load were to stay consistent 
with that many updates, I really wouldn't be worried about bandwidth as much as 
I would disk storage to handle that. I'm running 15,000RPM Ultra160 SCSI arrays 
that have hard time dealing with keeping the disk current doing around 10,000 
updates/minute accross 4 tables and about 60 fields of data... queries of course 
are another matter. - Just something to think about, every installation has 
differing requirements, if you're planning on running a website and having a 
small office of 15-25 people connecting to mysql from some app - then I wouldn't 
worry about it.
	As Olaf pointed out many times in this thread (or related threads) - budget is 
key with this deal, so I doubt load balancing and plopping another machine in is 
an option his client really wants to hear about - until they've gotten to a 
point to justify doing so. Why scare customers away telling them they can buy 
more, if you just sold them something new why isn't it going to be good enough - 
guarantee that's not a question you'll want to provoke.
>  
> | 2-Regarding the kernel: I'll have to add in DVD-RW support, so I'll have 
> | to do a custom kernel compilation anyway. You mentioned I need to use 
> | SMP support. What would you think would be best; stick with the generic 
> | base version, and add it SMP support, or SMP kernel config included in 
> | sys/amd64/conf?
> 
> Are you building a server or a workstation?  The "SMP" kernel in amd64/conf is
> just the generic (base) kernel with 1 extra line.  However you slice it, most
> likely you'll end up having to recompile the kernel at some point (for example,
> if you need to run a fire wall (ipf), then you are going to have to recompile it
> anyway.  When you do that, just make sure you don't accidently omit the SMP line
> by working strictly off the generic kernel.  Look at GENERIC and SMP in amd/conf
> and you'll see what I mean.  
> 
> All you need to do is add a maxusers line and comment out drivers that aren't
> needed.  That will get you on the right track.
>  

Same response. Look at the SMP file, it's basically "options smp; include 
GENERIC"... just copy the GENERIC to your own config, remove the driver(s) you 
don't require, add 'options SMP' and any other options you may wish - rename the 
config and go from there.


> | 3-What would exactly be the differences in D/L-ing the MySQL (4.1.x) 
> | source from MySQL and compiling that, vs. rebuilding the ports tree, and 
> | then 'make - make install'-ing that version? The difference (in 
> | particular: the advantage) in the former isn't clear to me.... I would 
> | have guessed the ports version to be tuned for the AMD64 version of 
> | FBSD, or am I missing out on something here?!?
> 
> Usually building from ports is pretty close to building from source.  All the
> "make install clean" command does is download the source, then compile it.  I've
> had problems with the binaries and the ports in the past, so I am just in the
> habit of compiling from source.  It gives you a little more control and a better
> feeling for what's going on if you build it from source.  It also takes into
> account any tweaks you do in /etc/make.conf and it also let's you do a configure
> command with whatever settings you want.  

The ports collection has been optimized for 'the average install', leveraging 
performance for functionality in most situations to be ideal. The ports usually 
have added patches or adjustments to code which integrates them better with 
FreeBSD, if you're not all that familair with the code you're working with, (in 
this case mysql), then I'd reccomend you stick with the ports collection.
	However, that being said - note that the ports collection isn't always up to 
date, and you may want a differing version of mysql. Follow the documentation on 
mysql.com and you should be just fine - fairly staght forward install and 
differs little from doing so via the FreeBSD ports collection. In any production 
system though, I highly reccomend compiling from source vs downloading binaries 
- that way nothing has been linked to/from librairies which may or may not be 
the same on your system.

>  
> | 4-Regarding MySQL: a similar question as for FBSD itself: v5 sounds 
> | sweet, yet it's only been a few weeks since their first stable release 
> | has been out there... What do you think would it be better/safer to 
> | stick with 4.1.x? I'm definitely partial towards the latter as I don't 
> | think the people involved will need the additional v5 features, but 
> | then, as an ulterior motive for me it could be sweet to get some 
> | hands-on experience with v5. ;)
> 
> For production, I would stick with 4.1.15.  I'm sure MySQL 5 is very nice, but
> as you say, it's only been out for a few weeks.  I have not done any
> benchmarking between MySQL 4 and 5, so I can't tell you which is faster.
> However, in the case of both PHP and Apache, the older version out perform the
> later versions hands down (this is why I use apache 1.3.33 and PHP 4.3/4.4).  A
> lot of times, as code becomes more bloated with additional features, it slows
> down and usually moves towards a higher level application.  And usually
> applications that move to higher levels (which means it's easier for the
> programmer to code) sacrifice some amount of speed and incur a larger memory
> foot print in the process.  That's not always the case, but often times it is.
> 
> In the case of MySQL 5, you would have to look into the features, but most of
> the stuff I saw in MySQL didn't strike me as being needed for most
> applications/database stuff - at least as far as relating to the average
> webserver.  As I recall, they added views, procedures and triggers, etc.  Most
> of that stuff, while nice, isn't going to make or break any web app out there.
> Yes, you can use something like procedures to offload some of the code from
> something like PHP to MySQL - but is that actually faster?  I couldn't tell you
> - you'd have to test it first hand to see if it makes a difference.  For me, I
> benchmark test everything and believe no one :-)  So just because MySQL 5 is
> new, in my book that doesn't make it faster, better, more stable.  It just makes
> it new.
> 
> Since MySQL 4.x.x has been working all across the internet for years, that's
> good enough for me.  As mentioned above, stability (to me) is far more important
> than getting 4200 instead of 4000 selects per second.  I can always add another
> $2000 machine and jump right to ~8000 per second and not have to worry about it :)
> 

I agree, stick with 4.1.x, but for different reasons. Compatability; not sure 
what you are doing, the size or scope of the database you're dealing with, but 
if for example you're going to be writting and app in windows which uses the 
myodbc driver to connect... then you may run into problems with newer versions 
of mysql. I encountered a LOT of this, when we moved from 3.x up to 4.1, largely 
because of changes in the client - at least I was aware of that before I 
started, but I did have to rebuild client librairies on all webservers, and 
re-install odbc drivers on client machines throughout the building because of 
it. Might not be something you're willing/ready to undertake for the features 
you've already mentioned not being required in mysql 5.

> Anyway, I think you get the idea.  If you have any questions, feel free to let
> me know.  I'll be happy to help where I can.
> 
> Ray
> 
> 
> 
> 
> 
> 

Good luck with it all, just my experience reflecting here - so hope it helps.

-- 
Nathan Vidican
nvidican at wmptl.com
Windsor Match Plate & Tool Ltd.
http://www.wmptl.com/