[thelist] MySQL vs MS SQL

[Jonathan Lambert]

On Wed, 2004-02-11 at 09:04, Hershel Robinson wrote:
> [ NOTE: I am reposting this because my first post was sent for
> administrative review due to a suspicious Subject line--it has not yet been
> approved. I have changed the Subject line now. Didn't know 'DB servers'
> could be so suspicious. :) ]
> 
Happens to the best of us. ;-)

> I have a client with a completely Perl-based, somewhat compute-intensive
> website. It was built initially to use the MS Access Jet DB Server and today
> still runs on that server.
> 
Wow, Jet! ;-)

> The client now says that the system "has grown tremendously and ideally the
> new server set-up should be built to support up to 1,000 simultaneous
> users."
> 
Ok, this is an important number.

> The new server will be physically just that--a new dedicated Windows Server
> 2003 machine. The client was recommended to use MySQL or MS SQL Server and
> one party even recommended using two machines, one for the web server and
> one for the DB server.
> 
I think this is a possibility.  Let's consider the parameters required
for this particular situation.  You have 1000 simaltaneous users.  Let's
say that on your pages, for the sake of argument, you had 5 database
calls per page (a decently complicated website, built from different
components but all hosted on the same server), and that you had on
average a call every 10 seconds per client, as intensive pages are
averaged out with less intensive pages.  

So, on average, 1000 simaltaneous clients would generate 30000 database
calls a minute.  This is complete conjecture, but you get the point. 
That's 1.8 million db calls an hour, a fairly reasonable number, but it
would most certainly put load on your server.  If you had a traffic
crunch, how would you scale?  You wouldn't.  You'd get killed if you had
it all on one server.

Splitting your database system off frees you to build more scalable
infrastructure going forward.  Also, while you're well within the
performance capabilities of both products, splitting that server (if
possible) is always a good idea.  In general, splitting services up to
individual resources is always good.

> The Perl uses DBI and so potentially a switch to MySQL could be done fairly
> painlessly and without a large investment (which is always an issue with
> this particular client) in man-hours nor software. I understand, however,
> that with sprocs, which are compiled in a MS SQL DB, that the computational
> load on the CPU should be reduced significantly and thus speed up the
> system. I am also told that there are other advantages to MS SQL.
> 

Stored Proceedures is the best (IMHO) way to build massively scalable
web applications, and the performance benefits, especially in high
traffic environments, cannot be overemphasized.  MS SQL has support for
transactions, built in load balancing solutions in the enterprise
product line, and a number of advanced features which might make scaling
the business a lot easier.  ODBC overhead can become a factor in high
traffic environments.

It all really depends on your business needs, and your expansion plans. 
You've got to carefully consider what your next step is after you
exhaust the single dedicated server route.  

I would suggest that you consider going with a simple dedicated server
to save costs, and then later consider using load balanced or
virtualized solutions to build inexpensive scalable infrastructure. 
This will save costs for your client.  But remember, this isn't scalable
infrastructure, and so long as your client accepts that, you should be
in the clear.  

If you build it on mysql, you might want to consider farming out your
database cluster to a linux virtrual server project (platform doesn't
matter with mysql), which will save you costs, and give you redundant
failover configuration.  If he want so spend gazzilions of dollars on a
solution, there are equivalent windows products coming to market this
year.  That way, you can harness the advantages provided by both
platforms (this is enterprise infrastructure, just say "cluster" and
he'll be happy).

In the end, what is all comes down to is whether or not your client
wants to spend the money up front to overbuild the system to make it
robust (always good from IT, always bad from pocketbook), or whether
he/she wants to save money now and get prepared to deal with more costs
later moving things around.   

Whatever you do, make sure that you or the company you host with does
some load testing, so you have some idea of where your application is
going to break.  This will allow you to know when it's time to upgrade. 
Otherwise, you could be in for a nice suprise. ;-)

Have a good one.  Sorry if I don't answer all of your questions.  I'm on
Vicaden from burning the heck out of myself on a lawn mower (a-duh), so
I'm a little slow. ;-)

Jonathan 
-----
http://www.FireBright.com/

> Anyone have any thoughts on these issues?
> 
> Thanks,
> Hershel
> 
> PS: I sincerely doubt the client would be interested in a Linux solution. He
> is one of these corporate-type Windoze fans. I myself hate Windows. :)