Building SQL server as replacement for dedi.

[hnbTech]

Hi!
Recently I've been asked to help with small friend's app startup, which uses SQL databases.
For now, they've been using dedicated server:

Quote

Intel Xeon E-2236
6x 3.4 GHz
RAM: 64 GB
STORAGE: 2x 2TB SSD

Pricing: ~165$/month

Now they've obtained small office and wanted to place server on their own ground.
I'm not sure what route I should go, since budget isn't quite big [~5300$ for whole server, rack, small UPS that would allow for emergency backup of database before shutdown, propably 1gbps switch]

I've been looking at Gigabyte R162-Z10 (rev. 100), 64-128GB RAM, 16/24c EPYC 2nd gen & 2 nvme u.2 ssd's from intel or western digital, although it started to be pretty tight for budget.

 

 

Database usage details:

Spoiler

I'd really appreciate any help, also sorry for grammar-related mistakes!

[Applefreak]

Why not build a system based on a Intel Xeon or AMD Ryzen motherboard. Most AMD boards will support ECC memory. You won't need that much anyways, what you need is fast or better low latency storage. What I would consider is the local ISP and if they can provide the same quality network that the rented server has. Database servers don't need a lot of power but many threads, a decent amount of ram and as I've mentioned, fast storage are important. 

[leadeater]

18 minutes ago, hnbTech said:

I'm not sure what route I should go, since budget isn't quite big [~5300$ for whole server, rack, small UPS that would allow for emergency backup of database before shutdown, propably 1gbps switch]

That's not enough for all that, the $165/m they are paying right now is a good price so I'd stick with that until a larger budget can be allocated. Honestly unless they can go for a proper HA setup with 2 servers and a storage array it's not really worth deploying anything, for startups it's better to find a good deal for hosting and leave all of that to the provider.

[hnbTech]

14 minutes ago, leadeater said:

That's not enough for all that, the $165/m they are paying right now is a good price so I'd stick with that until a larger budget can be allocated. Honestly unless they can go for a proper HA setup with 2 servers and a storage array it's not really worth deploying anything, for startups it's better to find a good deal for hosting and leave all of that to the provider.

I was saying at first that too, although they're saying that current hosting they're using started lagging whole app recently. I was actually thinking if it isn't just issue of MySQL itself, since it's plain MyISAM iirc, not InnoDB or RocksDB.

Other thing i was proposing, was normal workstation on Threadripper [3960X, since it's on sale right now; 128GB RAM, 2TB Gen4 nvme in raid 1], tho they've insisted for rackmount [and I can't really find any cooler capable of Threadripper fit into 2U chassis]

[manikyath]

if you're a small company, DONT put stuff on-site, it's absolutely the wrong way to go.

 

cloud's *fairly* affordable these days, compared to the cost of hosting something comparable from your office.

you're looking at hardware cost, aircon, power cost, uplink (you want a nice high-availability, and preferrably redundant connection), etc etc... in the end just clicking a button in azure is surprisingly cheap once you do the math.

 

in my opinion, there's only two reasons having hardware locally makes sense:

- you already had local hardware, and at least one application or workflow would be a major overhaul to migrate to cloud.

- you deal with large datasets, which would be prohibitively expensive to move to cloud storage, as opposed to local storage servers / NAS'es, or tape.

 

without delving into too much detail, here's some examples from customers i've dealt with:

- customer who has a very poor ISP connection, and rely on an old application which needs low latency connection between server and clients. ==> local server

- customer who is in the 'business of data' (not exactly right, but for the sake of example), and on a good day generates close to a TB of extra data, and EVERYTHING needs to be stored. ==> local server, archiving to tape.

- customer who only really needs a domain, and some form of central storage ==> AzureAD, and O365/onedrive/sharepoint.

- customer who has projected explosive growth, has an old application that needs decent client<=>server connectivity, but has a focus on working from home, and projected working abroad for representatives. ==> put the application in the cloud, with "windows virtual desktop" to access the application.

[manikyath]

8 minutes ago, hnbTech said:

was saying at first that too, although they're saying that current hosting they're using started lagging whole app recently. I was actually thinking if it isn't just issue of MySQL itself, since it's plain MyISAM iirc, not InnoDB or RocksDB.

tell the engineers making the application to investigate where the bottleneck is. i'd dare say without any knowledge, that it's just inefficient database calls.

 

EDIT: or that they need a faster server either way..

[leadeater]

13 minutes ago, hnbTech said:

Other thing i was proposing, was normal workstation on Threadripper [3960X, since it's on sale right now; 128GB RAM, 2TB Gen4 nvme in raid 1], tho they've insisted for rackmount [and I can't really find any cooler capable of Threadripper fit into 2U chassis]

Rackmount is a bit pointless for a single server and the pricing on racks are terrible. Combine that with wanting a UPS which for anything actually worth buying isn't particularly cheap but can be found for decent prices but either way both of those eat far too much in to the budget.

 

You don't actually need NVMe SSDs btw, good server grade SATA SSDs have more than enough performance. Don't go with anything consumer gradeish otherwise the performance will actually be rather bad once steady state performance is hit, that's where server rated SSDs are actually much faster.

 

13 minutes ago, hnbTech said:

I was saying at first that too, although they're saying that current hosting they're using started lagging whole app recently. I was actually thinking if it isn't just issue of MySQL itself, since it's plain MyISAM iirc, not InnoDB or RocksDB.

Looking at the current specs I'd be looking at database optimization and query optimization. Unless the SSDs in there aren't at least Samsung Pros then I can't see any reason why performance would be not enough due to the hardware. If I'm reading your stats you posted correctly then 1.7k QPS isn't a lot.

 

Would need to look at other performance metrics like avg CPU, memory stats and disk latency to see how those look. Most often application performance issues are not the database server and if it is it's administrative and design issues like Indexes and query structures etc.

 

Edit:

Don't fillet a fish with a hammer, you can but you shouldn't  Too often I see database servers being blamed for performance issues and massively over spec'd server purchased when it wasn't necessary, but it can solve the issue just not the most efficient way possible.

[mariushm]

Does it have to be 2U, why not 3U or 4U ? Lots of rackmount cases here : https://www.newegg.com/p/pl?N=100161262 8000 600029570

 

You could also get a Ryzen 9 3900 and you can put 128 GB of DDR4 ECC on a b550 motherboard or a x570 motherboard.  The 64 MB cache of the 3900 may help, and the extra memory will also help... 

 

A 4U case will have option for a 240mm water cooler for the cpu, if you're interested in going that route... see for example https://www.newegg.com/chenbro-rm42300-f/p/N82E16811123153?Item=N82E16811123153&quicklink=true

 

[hnbTech]

17 minutes ago, leadeater said:

Would need to look at other performance metrics like avg CPU, memory stats and disk latency to see how those look. 

Spoiler

This is for CPU usage, can't really get much more for now [I'm not their sysadmin nor engineer :v]

 

[leadeater]

1 minute ago, hnbTech said:

This is for CPU usage, can't really get much more for now [I'm not their sysadmin nor engineer :v]

Doesn't exactly scream hardware performance related, still need to check the disk performance just in case but I'd be looking at in depth analysis of the database performance stats and query history. Once you can find what is going slow you can then look at/for the why.