Library

Fast, Furious, and Cheap: server with good-enough performance for small business database for less than US$1000

(c) Alexey Kovyazin, IBSurgeon, 21 September 2017 (last update - 29 September 2017)

It is easy to get a powerful server if you are ready to spend many $$$: there are leading vendors like Dell, HP, and many less known hardware companies, they will be happy to sell you a powerful machine (usually, with delivery in 4-6 weeks).

However, money is always a problem, especially for small businesses and start-ups, so there is a great demand for «good-enough» cheap solutions. This article describes the practical approach for building «good-enough» database server with the sufficient performance for small business. It also contains calculations and links to the specific products we recommend.
Our company (IBSurgeon) provides database consulting and care services for Firebird databases, and we see many types of servers (or, better say, computers used as servers). Large customers use high-end configurations with SAN on top of SSDs, 350+Gb RAM and dual top Xeons on board, but small and medium business customers use less powerful machines since they don't need much computing power and they don't have a lot of money.

We have noticed, that many servers are actually usual workstations, and, the most interesting thing, they work pretty stable for years and years. There are some really exotic things, like Windows XP on the computer with 2Gb RAM (the computer is built in 2002!), but the majority of them are just «good-enough» self-built hardware solutions.

Of course, it is a matter of statistics, and nobody will recommend using a workstation as a server, but the question is: where is the balance between stability and expenses? Is it a good approach to use a cheap self-made server in terms of money-value? What can we get for USD$1000? What are risks related with non-expensive hardware?

Who can be interested in a database server below usd$1000?

Let's consider the following situation: an office with 20-25 workers, people work 7x5, the database size is around 50Gb. It is a typical situation for the small business company, which uses some ERP or CRM or specific application (for example, dental office automation system) for small business apps based on Firebird.

Let's imagine that the company is not flooded with the money and they want to economize as much as possible, without losing the reliability, to achieve good-enough performance. The responsible person in a company understands that all hardware will fail sooner or later, and wants to decrease related risks.

What kind of server do they need for Firebird database of that size and load? According to Firebird Hardware Guide, the most critical resource for the database is disk IO, after that RAM, and after all – CPU (interesting, that when customers from SMB buy servers from the big vendors, they often choose servers with powerful CPUs and slow disks, probably due to marketing materials which overemphasize the importance of CPU).

Disks

Fast IO means that we need solid state drives (SSDs) since SSD is the king of random reads and writes. Of course, SSD is not a panacea for databases, but it provides really high performance, and now it is the right choice for databases.

SSD comes in 2 flavors – enterprise grade and consumer-grade disks. The key difference between them is a resource, roughly calculated as a number of IO operations that they can perform.

For example, Samsung 850 Pro 256 Gb MZ-7KE256BW – customer-grade SSD (based on MLC technology), it has TBW (total bytes written) 150 Petabytes, and DWPD (Disk Writes Per Day) = 0.16. Or, more simply, the resource can be explained as 40Gb of writes every day during 10 years.
Update: we had a suggestion, that Samsung PM863a ($181.59) or SM863a ($249) are more reliable than 850 Pro, but still affordable - essentially, for extra $60 we can get better stability and some kind of protection from electrical surges.

Is it much or not? We have a test server with consumer-grade SSD where we run load tests to simulate intensive work of 100 connections at 60 Gb database. Every day 3 such tests, for 3 hours each, are started – it means that these tests simulate 9 hours work day. The tests are running for 2 years already, and the remaining resource (according to the firmware) is 54%.

Of course, ~50% of resource means that the drive should be replaced during the next year… Is it so bad? Every several years the speed of SSD is increased and prices drop: the SSD in the example has 6Gb/s and costs $137, in 2-3 years there will be 12G drives for the same price.

Amazon's price from USD$124

Enterprise-grade SSDs are built on the basis of another technology (SLC), and can approximately perform 30x more operations than the customer-grade SSD of the same size. Also, they cost approximately 3x times more than consumer-grade SSDs. The question is: do we really need 30x more IO rounds for the small business server for 3x price?

If we are looking for a fast and affordable solution, it is very attractive to use cheap customer-grade SSDs and replace them on regular basis (and monitor its resource with firmware, of course).

Risks associated with SSD

As adequate people, we understand that customer-grade hardware has less resource and high chances to fail, so we need to reduce these chances. How can we reduce it?

To decrease the chance of sudden hardware failure, it is necessary to use 2 SSD for the database, bundled in RAID1. RAID1 does not mean that we need separate RAID controller, it will be good enough to use RAID capabilities of the modern motherboard chipsets - essentially, there is simple RAID controller without a cache.
Update: since cheap RAID controllers on the motherboard could not support TRIM feature of SSDs, to avoid performance degradation it is recommended to setup software RAID, which orchestrates simultaneous operations on the software level.

If there will be no cache, what will be the performance? Modern SSDs, like the Samsung in the example, have the built-in cache and very smart controllers – so they are fast enough without dedicated RAID controllers.

Of course, it is necessary to check disks health; usually, the firmware provides a good enough estimate of the disks lifespan.

Backups

Of course, we need drives for backups. The best choice will be a pair of SATA drives 2Tb each – for example, Seagate SkyHawk 2TB Surveillance Hard Drive, with 5900 rpm.

Amazon's price is $74,99 

We need 2 HDDs because drives for backup also must be put in RAID1, in order to provide protection from the sudden hardware failure. These backup drives can be pretty slow, their major purpose is to store backups and provide an adequate speed of sequential reading/writing. 5900 rpm drives are slower than7200 rpm, but they are known to be more reliable. This specific series of drives (Seagate SkyHawk) is designed for linear writes: this is what we need for backups.

Risks for backups drives

The risk is the same as for SSD, and we protect from it in the same way: RAID1 and monitoring. There should be a little chance that both drives in RAID1 will fail simultaneously.

System drive

Do we need a separate drive for the operational system? In the cheapest configuration - no, it will be good enough to keep OS and database on the same drive. It will slightly speed up the degradation of SSD, but not so much – in our scenario, it should definitely sustain for 3 years.

CPU

For Firebird database server it is better to choose CPU with many cores and as low frequency as possible. Yes, you read it correctly – there is no need for high-frequency CPUs for a database server. Firebird (if it is configured properly) uses all cores for normal work, but do not load them at 100% rate.

There are exceptions: some maintenance operations (backup and restore) which benefit from high-speed CPU, and badly written SQL queries with the wrong usage of indices – they consume 100% of the core, but this is a clear mark that the database performance optimization is required. In general, it is not possible to compensate slow queries with high-end CPUs: even if CPU is 3x faster, the slow query will still be slow.

On another hand, having several cores can compensate the existence of such CPU-consuming queries: with the number of users under 25 and with 8 cores, there is not so big chance that all cores will be simultaneously occupied by the slow queries.

And, since we are looking for the cheap solution, we are limited to desktop CPUs – and, regret to say it, to AMD chips. Essentially, if Intel could provide the desktop processor with 8 cores for the same price or slightly higher as AMD FX-8320E, we could use Intel too, but at the moment I don’t see such option.

This AMD chip is not so modern and not so fast, but it has 8 cores, and it is cheap, so it is the optimal choice for our fast and cheap server.

Amazon's price is USD$108

Risks associated with desktop CPU

What is the potential risk associated with CPU? For low-frequency (i.e., not overclocked) chips with adequate cooler, there is almost no risk. When did you hear about failed desktop CPU last time – in 2003? Of course, server CPUs are much faster, but we are looking for a good enough solution.

It is a good idea to monitor CPU load and temperature to prevent occasional reboots.

RAM

The amount of needed RAM depends on the database size and the number of users. In our scenario, database size is 50Gb and we have maximum 30 users.

The maximum size that Firebird can consume is database size (in this case it will be fully cached in RAM) plus 200-300Mb per user (in the case of Firebird 2.5 SuperClassic). So, the ideal maximum RAM size is 64Gb, but since we want to economize, and since we have fast SSD, we can use as little as 16Gb.

Some users in such scenario work fine even with 8Gb, but 8Gb is pretty tight: in this case, no other application should be used on the same server. 16Gb is the recommended minimal optimum.

Amazon's price is USD$59.99 x 2 = $120

Risks associated with RAM

RAM is more vulnerable to failures than CPUs but less than disks. Enterprise-grade RAM has special mechanisms to reduce a possibility of errors, like parity checks. How can we estimate and decrease the risk of memory failures?

First, there are methods to check memory chips, and if such tests were successfully passed, RAM chips will work for many years. Second, when RAM fails, usually it is very noticeable: computer either has a blue screen of death or kernel panic, or frequent reboots: all such symptoms require checking and, in case of bad results, replacement of RAM.

Motherboard

The motherboard is an important part of any computer. Luckily, modern motherboards for desktops are very reliable, especially motherboards which were released 4-5 years ago – they have all necessary updates and they are stable. And – yes, they are cheap.

We need a motherboard with RAID support, with embedded video chip, and with enough SATA plugs. Something like the one:

ASUS M5A78L-M Plus/USB3 DDR3 HDMI DVI USB 3.0 760G MicroATX Motherboard
Amazon's price is USD$74,99

It has very positive reviews, and it is easily configured to have RAID1.

Desktop case

We need a desktop case to plug all computer components together, with enough power supply. Something like this: $76,82 http://amzn.to/2ydeYs1

UPS

The one thing which is not a part of the server, but should be equipped with every computer with the important data inside – uninterruptible power source (UPS).

UPS protects a computer from power outages and fluctuations. In our experience, power surge or abnormal power loss of the computer is the leading source of databases corruptions.

Something like CyberPower CP1200AVR, for $117.97, should be good enough. 

Total

As a result, we have the total price of all computer components and UPS = USD 855,27 – it is even less than USD1000, however, before these components will become the working computer, they should be assembled, equipped with OS and tested.

Assembling the server

To assemble all these parts into the working server and install there operation system (we would recommend some flavour of Linux, like CentOS7+ or Ubuntu Server 16+), all these components should be assembled/correctly plugged.

It is not difficult, just requires a screwdriver and some patience and time to read instructions carefully and watch some YouTube videos. 10 years ago almost every developer was able to assemble a computer from these parts, nowadays is different, but still not difficult.

What if you don't want to assemble the server?

There is shortcut – it is possible to buy a computer with the similar configuration which almost fits our needs, and plug there additional components (i.e., disks).

I would recommend taking a look at this computer – originally designed as game PC, it has 8-cores CPU and 16Gb RAM, and costs only USD$709. 

It will be necessary to equip it (open case and plug in) with a pair of SSD drives (+$248,48), one more backup drive (+$74,99) and, of course, UPS (+119.82). Total will be USD 1153 – it is higher than $1000 goal, mostly due to the video card and nice desktop case. You still need to configure RAID1 (in BIOS) and, of course, install there server OS instead of pre-installed Windows 10 Home.

This approach can be used to turn a powerful desktop computer into the server – simply add there redundant SSDs and backup drives.

In the past many SMBs and startups have used such self-built servers, now there is a trend to use cloud-based virtual machines.

How are virtual machines compared with a self-built server?

Of course, building and maintaining own server requires some skills (not so high as it could look like), so many companies want to get rid of all hardware servers (and, possibly, fire system administrator responsible for servers).

In the next article, we will consider VM in the cloud as a database server for SMB.

More articles about Firebird database performance:

1. Firebird Hardware Guide
2. Tuning 1.7Tb Firebird database
3. Firebird Performance Optimization Service
4. 45 Ways To Speed Up Firebird database
5. Collection of optimized Firebird configuration files

Contact us

Thanks

• Vlad Khorsun
• Dmitri Kuzmenko
• Pavel Zotov
• Sergey Nikitin
• Emil Mustea