SEO

Okay, the previous subject was too narrow, what I am really looking
for are opinions on general disk/memory/cpu configurations,
manufacturer notwithstanding...

As stated previously, I'm configuring a PowerEdge 2950III, and trying
to decide what will provide the best bang-for-buck. The server will be
used strictly as a MySQL database server running atop Red Hat Linux.
Two large databases, each about 2GB, heavy on both Inserts and Selects.

Up until recently, I had spec'd:

2 x Quad-Core Xeon 5430 @ 2.66 GHz (6 MB cache) , 1333 MHz FSB
8 GB Ram (4x2GB)
4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second
set for MySQL data)

....worked out to around $5,500. Now however there is a processor
promotion, such that:

1 x Quad-Core Xeon 5450 @ 3.0 GHz (6 MB cache) , 1333 MHz FSB
8 GB Ram (4x2GB)
4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second
set for MySQL data)

....works out to around $4,500. So what I'm wondering is, do I really
need an eight-core box, since my experience tells me that MySQL's
greatest bottleneck is disk I/O. I'm wondering if I would be better
off with just one processor to start with (are they easy to add later,
btw?), maybe add more RAM, and just save some cash.

Any thoughts or suggestions are much appreciated. I have to pull the
trigger on this soon. I was hoping they would bump the specs or drop
the prices significantly... I've been watching these for months and
there's been promo after promo... I anticipate a major update, has
anyone heard anything? (Should I wait a little longer maybe?)

Thanks.

....Rene

Hi,

Three things...

1. You need to let us know what the DB server will be doing. Many CPU
cores are only important of you have many CPU intensive MySQL
connections in parallel. Will you have a read-intensive or
write-intensive database load? Those 2950III you're considering can
take up to 8 disks. If you're doing very read-intensive operations, you
may want to consider a RAID1 pair for your OS, apps and MySQL tmp tables
and a RAID 5 array for the MySQL data (to get the most disk space for
your money without sacrificing redundancy). However; if you're doing a
heavy work with lots of reads and writes at the same time then you
should consider RAID1+0 for your data. RAM will always help for both
MySQL caches and buffers and don't underestimate the great effect that
lots of RAM for your filesystem cache will have (talking about
unix/linux here, can't speak for windows).

2. All the hardware vendors have promotions running all the time which
they change every month. One month it will be cheaper disk, the next
month will be cut-price RAM etc... The end result will be about the
same...

3. It's very easy to upgrade memory and processors as long as you don't
mind 15 minutes or so of downtime for that server, linux will just see
the new h/w when it comes back up. With hardware like HP and Dell you
won't even need a screwdriver, it's all easy to use clips.

Cheers,

Andrew

-----Original Message-----
From: Rene Fournier [mailto:m5@renefournier.com]
Sent: Sun, 27 April 2008 22:57
To: mysql@lists.mysql.com
Subject: Optimal MySQL server -- opinions?

Okay, the previous subject was too narrow, what I am really looking
for are opinions on general disk/memory/cpu configurations,
manufacturer notwithstanding...

As stated previously, I'm configuring a PowerEdge 2950III, and trying
to decide what will provide the best bang-for-buck. The server will be
used strictly as a MySQL database server running atop Red Hat Linux.
Two large databases, each about 2GB, heavy on both Inserts and Selects.

Up until recently, I had spec'd:

2 x Quad-Core Xeon 5430 @ 2.66 GHz (6 MB cache) , 1333 MHz FSB
8 GB Ram (4x2GB)
4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second
set for MySQL data)

....worked out to around $5,500. Now however there is a processor
promotion, such that:

1 x Quad-Core Xeon 5450 @ 3.0 GHz (6 MB cache) , 1333 MHz FSB
8 GB Ram (4x2GB)
4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second
set for MySQL data)

....works out to around $4,500. So what I'm wondering is, do I really
need an eight-core box, since my experience tells me that MySQL's
greatest bottleneck is disk I/O. I'm wondering if I would be better
off with just one processor to start with (are they easy to add later,
btw?), maybe add more RAM, and just save some cash.

Any thoughts or suggestions are much appreciated. I have to pull the
trigger on this soon. I was hoping they would bump the specs or drop
the prices significantly... I've been watching these for months and
there's been promo after promo... I anticipate a major update, has
anyone heard anything? (Should I wait a little longer maybe?)

Thanks.

....Rene



LOVEFiLM International Limited is a company registered in England and Wales.. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom.

This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately.

This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com

On 29-Apr-08, at 10:41 AM, Andrew Braithwaite wrote:

> Hi,
>
> Three things...
>
> 1. You need to let us know what the DB server will be doing. Many CPU
> cores are only important of you have many CPU intensive MySQL
> connections in parallel. Will you have a read-intensive or
> write-intensive database load? Those 2950III you're considering can
> take up to 8 disks. If you're doing very read-intensive operations,
> you
> may want to consider a RAID1 pair for your OS, apps and MySQL tmp
> tables
> and a RAID 5 array for the MySQL data (to get the most disk space for
> your money without sacrificing redundancy). However; if you're
> doing a
> heavy work with lots of reads and writes at the same time then you
> should consider RAID1+0 for your data. RAM will always help for both
> MySQL caches and buffers and don't underestimate the great effect that
> lots of RAM for your filesystem cache will have (talking about
> unix/linux here, can't speak for windows).

Right now, averaging 13 queries / second.

47% SELECTS
14% INSERTS
22% UPDATES
7% CHANGE DB

I was thinking of doing a RAID 1/1 (four drives, all 15K SAS), with OS/
Apps on the first mirror, and MySQL data on the second.

I'm working at tuning my table definitions and queries and a few other
things in order to bring down the number of queries. Although I'm
using indexes liberally, it seems LEFT JOINs are killing the queries
that touch the 17 million row table. So I'm working on that. I want to
"fix" my own queries before moving to faster hardware, but in any
case, I would still like to get best iron for the task, long-term.

With 8 disks, would something like RAID1 for OS/Apps, then RAID1+0 for
MySQL data allow for substantially higher IO transactions? Row sizes
are very small, it's mainly a latency thing from what I can see.

> 2. All the hardware vendors have promotions running all the time which
> they change every month. One month it will be cheaper disk, the next
> month will be cut-price RAM etc... The end result will be about the
> same...

I've just been hoping that Dell would drop the price on the 2950III.
It's been the same for about six months.

> 3. It's very easy to upgrade memory and processors as long as you
> don't
> mind 15 minutes or so of downtime for that server, linux will just see
> the new h/w when it comes back up. With hardware like HP and Dell you
> won't even need a screwdriver, it's all easy to use clips.

Good advice, thanks. From reading everyone's advice, I'm inclined to
save money on the CPU (just one quad-core Xeon 5430 at 2.66GHZ, 6MB L2
Cache) and spend more money on RAM (16GB instead of 8GB).

....Rene