MySQL performance with a BIG database

Anon

How much data (realistically) can a MySQL database handle?

I've got a big mysql database that is currently offline, the main table has circa 1.5 Million rows and I want to use this @ some point in the near future to drive a website.

Now I've used MySQL loads of times before on web projects without any complaints from the server and it's always been sweet, however, a DB as large as this
is taking me into new territory. Also I need to write a script to index the big table for keywords to assist in searching, so I'm probably gonna end up with 2 big tables.

If you people feedback to me and say that MySQL can handle this, what other things should I take into consideration?

For example:
=on launch the website won't be getting a lot of hits, so connections won't be an issue (immediately) but I want the thing to be scaleable and moving it will be a pain.
=the DB will sit on my dedicated webserver, but it also acts as a (small) mailserver and runs one other site (back-end.org) that gets circa 40K page impressions per month.
=The machine is a 500MHz PII with only 64MB Ram, should I upgrade this?

I hope this makes sense, basically, can MySQL deal with a big DB (is this big?) running a site getting say 1 million hits [pages] per month and if so, does it need to be running on it's own machine (without Apache etc)

tia

Bealers

Anon

Are we talking unix or windows here?

About the machine:
64MB is definately way to little memory. This needs to be MUCH more if you want to get any kind of performance.

A second processor allways helps more than increasing the single cpu's MHz,
especially if you are expecting to get many processes running at the same time.

About the db:
Tables of the size you suggest have surtainly been used before, but the bigger they are the slower they get.

What kind of response times are you getting now, and what are you aiming for?
How many concurrent queries are you expecting? 1 million a month could mean 900.000 in one hour and 100.000 in the rest of the month :-)

And how exactly will this "big-ish" table drive the website?

Anon

Hi, and thanks for the response

Are we talking unix or windows here?

Redhat 6.0

A second processor allways helps more than increasing the single cpu's MHz,
especially if you are expecting to get many processes running at the same time.

Mmm, this makes sense, I was thinking about building a second machine that runs MySQL only, no apache etc, if I had, say a dual PIII 500
and 1/2 gig of RAM, would this sound robust/powerful enough

Well this is the big question, I really dont know 😐
It's an offline site at present, until I finish building/indexing the database and the site that goes around it.
The 1Mil hits is what I'm planning to have in say a years time, but I want the infrastructure in place now, so I can only guess.
For a big-sh DB and a fairly substantial # of hits, what is a good benchmark? Say 50/100/500 concurrent transactions?

And how exactly will this "big-ish" table drive the website?

I'll be publishing circa 1% of the data to static files, so that most used stuff is cached, but the other 99% will be availiable by
searching

I envisage approx 5-6 transcation per page to get it loaded on the screen, the big table will be used for 50% of these

regs

Bealers

Anon

That config (Dual Pentiums 1/2 G RAM) sounds plenty robust. You'll be serving data, meaning selects not inserts, at which MySQL excels far beyond any other RDBMS (which is one of the reasons why it's so popular as the db for internet servers, where you are usually doing selects over inserts by 1 to several orders of magnitude).

The only thing you've said which raises a concern about the size of the table is it's total data size. Currently, MySQL only supports tables of up to 4GB in size.

Anon

RK>
The only thing you've said which raises a concern about the size of the table is it's total data size. Currently, MySQL only supports tables of up to 4GB in size.

Well I've just looked and with 1.2 million rows the table has a 0.5GB footprint, I expect to have many another .5 mill rows by launch, so I should be cool there.

Mmm, I'm gonna get my calculator out and start working out best/worst case scenarios here for transactions and server load

Thanks!

Bealers

Anon

We currently serve 30 million pages a month off of MySQL/PHP/Linux - we have 5 nodes and 1 DB server. Our accounts table has over 1.3 million entries and still returns queries fast as lightning. It's the only choice for cheap dynamic web solutions

Anon

What is the hardware configuration of the nodes and the DB Server?

-Bobby

Anon

Well the DB server is a dual Xeon 550 with a Gig o' RAM and the nodes range in dual PIII 650's with 512 MB of RAM to dual 350's with 512MB of RAM. Nothing really spectacular.

Anon

Hello!
Curious..mySQL is good for selects as i'm reading. But what about a "big" database that does many inserts
eg. message boards etc.
What's the deal?