At 08:23 AM 7/6/99 +0000, Matt wrote:
>This raises an interesting point guys... we have just started running a
>system that uses MySQL as the backend. I am running the system on a Sun
>, 333mhz cpu,256mb ram, lots of hd space etc. I was running the system on
a Pii400 linux box.
>Now, I am interested in how much the system will scale. Currently we have
>'tickets', but also included are various other tables, status codes,
>(btw is there a way of finding out the total number of rows in the db?).
>What sort of amount of rows can MySQL handle comfortably??? Are we talking
>(i.e. the figure of 70 million was mentioned, is this with mysql?).
The scalability bottleneck is *not* the number of rows in a table. There
are a couple different potential performance bottlenecks. If your hardware
is underpowered you'll be I/O bound. If you have big hardware but are only
running "read" queries, then you'll be CPU bound. Specific to MySQL, if
you have a lot of users writing, you'll be lock-bound (contention). As
long as you don't run out of diskspace, the number of rows in a table is
not generally a factor.
With respect to the "70 million rows", this was part of a database for a
web app (for a utility company) I architected about two years ago. It was
actually Oracle running on Sun, but it was the first example that came to
mind. This particular case was unusually pathological: We needed to do
complex mathematical grouping operations in four dimensions. My role in
this was to design and tune an architecture so that they could meet the
performance requirements (when I got there, they were missing their
performance specs by more than an order of magnitude). It was actually
very interesting which things turned out to be the bottleneck; it wasn't
what you normally expect. A major pain to work on, but pathological cases
such as this are the best learning experiences.