MySQL / PostgreSQL limitation

[guy keren]

On Tue, 2011-08-23 at 11:35 +1000, Amos Shapira wrote:
> 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).
> 
> I can't answer these specific questions directly. We have a chubby
> (2TB right now, and growing) PostgresQL database on Hitachi FC SAN 15k
> RPM RAID 1+0 SAS disks, 84Gb RAM, 8 CPU cores, controlled to fail-over
> to an identical stand-by secondary using RedHat Cluster Suite (running
> on top of CentOS and Xen) and it can hardly handle the load of some
> queries (it's not a web site, it's mostly data warehouse loading
> thousands of new items per minute and allowing customers to query and
> configure stuff through a web "portal"). Our developers are looking at
> solutions to cluster Postgres, and using SSD for disks. I'm not sure
> how much a larger single PostgresQL instance would help. There are
> quite a few anecdotes and howto's about large PostgresQL databases on
> the web (blogs, wiki.postgresql.org, etc).

did you first try to isolate what is causing your troubles? i.e. is it
indeed related to disk wait times? or the CPUs choke? or perhaps it's a
RAM problem? it's not always simple to know where the bottleneck is, and
if you fix it - how soon until you hit the next bottleneck (e.g. you
could have I/O problems, and when lifting them - immediately run into
memory problems or CPU problems). without knowing what your problems it
- you're too likely to spend time and money on a solution that won't
help you at all.

--guy