MySQL / PostgreSQL limitation

[shimi]

2011/8/23 Hetz Ben Hamo <hetzbh at gmail.com>

> Hi,
>
> I'm thinking about some project, and I was wondering about something: lets
> say I'm setting up a really-big-web site. Something in the scale of Walla or
> Ynet.
> My question is about the DB: Where are exactly my limitations that I need
> to go master/slave, replications etc.
> Is it related to memory? processor? I know that many uses replication etc
> due to shortage or RAM and/or CPU power.
>
> Lets say I have a machine with 512GB RAM, 20 cores, 20TB disks. Such a
> machine should handle a huge load without any issue. With such a machine,
> theoretically, can a single MySQL / PostgreSQL handle such a load? (few
> hundred thousands connected users at once).
>
>
>
It really depends on *what* you do, like has been said, ACID consideration,
and of course, the ratio between read/write operations.

There is a certain point where "a bigger box" simply won't cut it, for
various reasons in the way hardware and software are designed.

It would probably be better to scale vertically than horizontally... For
example, if you talk about a site like Ynet - most of Ynet are read
operations (I assume...) - you would be better off with running one master,
to which you'll write updates, and many nodes running slave replication from
the master (and not receiving any updates themselves), and each backend
processing web server, would connect to a matching SQL backend, so the load
is spread between them all...

It's also smart to just avoid accessing SQL altogether where possible (with
caches like memcached, and caches on the web frontends where possible) - and
use low resource-consuming frontends / load balancers (I know you didn't ask
about it, but you would probably return here and ask about it if you would
choose Apache ;)) - I like nginx, which is a performance beast.

NoSQL has been mentioned here - and I re-iterate about ACID considerations
again, see the funny http://www.youtube.com/watch?v=URJeuxI7kHo :)

To sum it all - you need to design what type (and volume, per user,
multiplied by the number of users) of queries you'll be doing... and... hire
a professional DB consultant ;-) Such questions cannot be answered "on one
foot"... Proper queries and indices can change the load on a DB by orders of
magnitude, too.

So I say, "no, this machine would probably not work with the load you
mentioned", simply because I don't think it scales in a linear manner as one
might imagine...

HTH,

-- Shimi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.cs.huji.ac.il/pipermail/linux-il/attachments/20110823/22930559/attachment.html>