How to size a MySQL server

Hi everyone,

I've got a question about how to size the server hardware for a MySQL
system that I've got to buy and build. We're going to be putting a
database on MySQL that will contain upwards of 20 million rows of data.
This data will be used by Java applications running under Tomcat to
provide web pages, Java data objects and probably most importantly SOAP
web services. The total number of concurrent users of this data is not
know, but could be thousands / hour. The database will be essentially
read only, no transactions or updates by users. The whole thing will be
running on a RedHat Enterprise Linux AS server. What I'd like to know
is what kind of machine should I be buying; CPU speed wise, memory size
wise and disk size? My initial impression is that the requirements I've
outlined above could be handled readily by even a modest machine, but I
wanted to ask for feedback in case my impression is wrong.

Thanks in advance,
Doug Farrell

Re: How to size a MySQL server

writeson@charter.net wrote:

> I've got a question about how to size the server hardware for a MySQL
> system that I've got to buy and build. We're going to be putting a
> database on MySQL that will contain upwards of 20 million rows of data.
> This data will be used by Java applications running under Tomcat to
> provide web pages, Java data objects and probably most importantly SOAP
> web services. The total number of concurrent users of this data is not
> know, but could be thousands / hour. The database will be essentially
> read only, no transactions or updates by users. The whole thing will be
> running on a RedHat Enterprise Linux AS server. What I'd like to know
> is what kind of machine should I be buying; CPU speed wise, memory size
> wise and disk size? My initial impression is that the requirements I've
> outlined above could be handled readily by even a modest machine, but I
> wanted to ask for feedback in case my impression is wrong.

Thousands of queries/hour to a 20-million-rows-table cannot be handled
by one (normal) machine. I think you should definitely setup more than
1 machine and keep the buffering machines up-to-date from one master.

I would also suggest the use of crontabs to make as much HTML as
possible, in order to save CPU when it's not strictly necessary to
query the database.

--
Bart

Re: How to size a MySQL server

Bart Van der Donck wrote:
> writeson@charter.net wrote:
>
> > I've got a question about how to size the server hardware for a MySQL
> > system that I've got to buy and build. We're going to be putting a
> > database on MySQL that will contain upwards of 20 million rows of data.
> > This data will be used by Java applications running under Tomcat to
> > provide web pages, Java data objects and probably most importantly SOAP
> > web services. The total number of concurrent users of this data is not
> > know, but could be thousands / hour. The database will be essentially
> > read only, no transactions or updates by users. The whole thing will be
> > running on a RedHat Enterprise Linux AS server. What I'd like to know
> > is what kind of machine should I be buying; CPU speed wise, memory size
> > wise and disk size? My initial impression is that the requirements I've
> > outlined above could be handled readily by even a modest machine, but I
> > wanted to ask for feedback in case my impression is wrong.
>
> Thousands of queries/hour to a 20-million-rows-table cannot be handled
> by one (normal) machine. I think you should definitely setup more than
> 1 machine and keep the buffering machines up-to-date from one master.
>
> I would also suggest the use of crontabs to make as much HTML as
> possible, in order to save CPU when it's not strictly necessary to
> query the database.
>
> --
> Bart

Bart,

What's your basis for feeling one server would be overloaded by the
requests I've outlined? I'm not questioning you as much as wanting to
know your experience with this.

Thanks for your reply,
Doug