Database hardware advice

Question

As a lone developer in my company I'm missing someone to bounce ideas off with. I've been given the task of gathering some hardware requirements for our server so we can make some internal websites public facing.

Current hardware is

SRV1 - Stats processor.

Windows server 2003 Intel Xeon CPU 3.00ghz 1 gig RAM 512 gig mirrored raid drives

SRV2 - Currently being used to replicate certain tables.

Windows Server 2003 Intel Pentium 4 1200MHZ 1.00 gig RAM 40 gig harddrive

The current traffic stats of SRV1 are

Traffic 1 ø per hour Received 149 GiB 1,034 MiB Sent 445 GiB 3,096 MiB Total 594 GiB 4,130 MiB

The machine is running wamp stack. There's a couple of simple java based programs which are doing reads and writes to the database.

App 1:

Loads data into myisam table -> Runs select only queries on myisam table and use information to insert and update a large InnoDB table. -> Summarises the data (insert and on duplicate key update query) in the large InnoDB table (currently 7.5 GiB 13 mill records ) into smaller InnobDB tables (largest 2 gig 2,016,166 records) for fast read access.

App 2:

Reads the smaller InnoDB tables to do some alerting/reporting of the data they contain.

Web usage:

Read only access to the smaller InnoDB tables both internally and externally.

I don't believe the server will have a vast number of connections via the web server even when we off the data stored inside these tables out to customers. So I'm not too concerned about that, I'm more concerned about speed of access to the data.

So, to summarise this stupidly long question.

What would people recommend in terms of OS/RAM/Hard drives/Raid/ Processors. I'm guessing the answer is going to be get the best you can afford. But I'll admit I'm a bit clueless as to what they best I could afford in things such as ram/raid/processing/harddrives is and how much things should cost.

Any extra advice anyone has to spare would be greatly appreciated.

Thanks in advance

Alan

Answer 1

If you want to stay with the familiar Windows setup, you should definitely consider Windows 2008 R2 Standard, but if you don't want to pay for the license you could switch to Linux (CentOS, RHEL, Debian, Ubuntu are good distributions).

You will probably want a quad core, nothing too expensive, perhaps an Intel X3440, with 12GB of RAM, which should help your database sit in RAM so everything is fast, along with 4 HDDs, either regular enterprise SATA ones, or SAS 15K RPM disks, if you need more disk I/O, but with either selection you will want RAID-10 powered by a RAID card with BBU.

Dell could be a good source to get such a server. If you've been doing fine so far on your current hardware, this server will seem blazing fast.

Answer 2

Ok, lets get this straight:

Windows server 2003 Intel Xeon CPU 3.00ghz 1 gig RAM 512 gig mirrored raid drives

This machine is like a porsche without engine. Way too little memory for a modern computer with multiple cores. Mirrired discs are still too slow when you get load. Whether or not that IS a problem you should know (need to check disc counters).

I run a SQL Server on: 4 cores 8 gb memory virtualized. The disc layout will interest you though. I have different sets for tempdb, logfiles and data- and the data is a raid 10 over 6 velociraptors.

Why? Discs LOVE (!) IO, and in my case I run tables with a billion and more rows that I aggregate. RAM dcomes in handy for caching, but besides that - disc IO is critical. When I have to grow them, I may put in SSD as second pair (couple SSD and physical disc in a Raid 10 - My Adaptec controller has some nice features when you do that).

Now, hardware. SUpermicro. Only ne having sensible.... cases.

You can get a case with 4 rack units height and 24 disc slots, or one with 8 units height and 72(!) disc slots, should you ever need them (I love that thing - sadly I dont ahve one).

OS: Makes no difference. Seriously. WIndows HAS overhead - yes. But... who cares. DB servers are memory hogs, and the small overhead of WIndows is not going to break a 16+ gb machine.

Processors: Depending on what you do, db wise irrelevant or not. THey are relevant for data warehousing, data clenaning, but pure transactional processing is NORMALLY a lot more IO bound. IO Is what the live and die for. Velcoiraptors are a VERY nice place between low costs and high performance.... a lot cheaper than SAS drives, a lot faster than most SATA drives. Get a good RAID controller (Adaptec 5805QZ is top on my list).

Now, reality check:

Summarises the data (insert and on duplicate key update query) in the large InnoDB table (currently 7.5 GiB 13 mill records ) into smaller InnobDB tables (largest 2 gig 2,016,166 records) for fast read access.

In my world both qualify as toy size. 13 million is not somethign a db starts sweating over. 7.5gb WAS large when I started with databases professionally, but then the Pentium based server (note: pentium - not even pentium 2) had 128mb memory ;) Today a 7.5 ggb table is something you load into memory on a workstation.

For low cost: * AMD Phenom II mainboard, six coes, 16gb RAM attachable. * Get it passibevely cooled. * If you only need 8 discs you even can fit it into a little cube casee (I have one soon two of those asm my personal data stores).

Answer 3

Some good answers here. I'll try to address some other points.

Anything new will be better than what you have. The Pentium IV is a server in name only. Don't repeat that mistake.

Whatever you get, I suggest dual power supplies, ECC/Buffered memory, at least Mirrored hard disks internally for boot and RAID for the data. Whether you go for SAN like iSCSI or FibreChannel or you decide on Direct Attached, is up to your budget and growth plans. Get a 3 year 4-hour service plan up front, because once you buy it, the boss won't want to renew it year to year. He will gladly risk your job to claim his cost savings.

For scaling, I suggest two servers, one with a high CPU clock rate to feed HTTP, the other with lots of cores and RAM to feed RDBMS. Pay particular attention to the Ethernet on the HTTP system. If you can get dual ethernet and a switch that can handle EtherChannel for your Internet connection, so much the better. Besides performance it will give you redundancy, you can lose an ethernet port (either server-side, switch-side, or bad cable), and service will continue. The web server EtherChannel link should be dedicated straight to the Internet (or DMZ, whatever) so that a local Ethernet broadcast storm won't take out your web service. That means you'll need at least two other Ethernet ports on your web server, really three.

Likewise your web server/RDBMS server link should be a dedicated pair of Ethernet ports. That means your local LAN can get flooded with a broadcast storm, and it won't affect web to RDBMS communications. Your local LAN can wallow in self-pity and poor service while your customers get good service.

As for brand, select a major vendor that has a service company local to you. To find this out, ask for the vendor's small business representative to call you (oh, boy, he should be eager to do that), then drill him with hard questions like, where do you depot your parts, who will service my systems, how far away are they located, etc., etc. He may not know but he better darn well find out and soon, because you'll be talking to 3 other guys just like him from his competitors, and don't let him forget that.