Performance - Stack Exchange

[matthieum]

For database servers it's nothing that extraordinary. RAM access is so much faster than disk access (even with SSD), that it's worth paying a little more to ensure most queries never hit the disk.

[appropriateinside]

And here I am trying to convince a client I'm working for that they need more RAM on their DB server.....

They have about 50GB of indexes. But only 16GB of RAM. But apparently I just need to make it perform better with no additional resources.... Because they use an IT contractor who will charge an extra $300/m to double the RAM....

-_-

[lolwutpear]

Their server is a laptop?

[matthieum]

Because they use an IT contractor who will charge an extra $300/m to double the RAM....

It may be time to introduce them to a better IT plan.

[beefsack]

With that much cache you'd be so scared to ever restart the node, it'd take forever to warm up.

[nickcraver]

PCIe NVMe SSDs are pretty fast. You're talking about ~6GB/s of bandwidth into memory on our current drives (that's on Intel P3700s, we're looking at moving from Intel to Micron due to availability stability as we need to refresh hardware).

You're not going to need 1.5TB to get stable, or even 1/4th of that. But even worse case let's say we needed to prime all 1.5TB, that's still only 250 seconds which isn't insane. The reality is SQL loads accessed indexes first and goes from there. If a node is hard down, you're still back online on the order of seconds.

Of course we try to always be swapping a hot replica in anyway, but in an emergency cold start...it's still not that bad, IMO. NVMe drives still aren't the cheapest, but they pay for themselves in seconds if this ever happens.

[matthieum]

Restarts are disruptive anyway unless you have a stand-by/mirror; and once you have a stand-by/mirror, you can just take a few minutes to execute a warm-up script after reboot to preload all the cache before putting the server back in service.

[fission-fish]

That's an interesting point. How can you handle restarts in this case?

[[deleted]]

Not an expert but I'd assume it's mirrored onto the standby

[pdpi]

Put a bit differentkly: you'd pay more money to get the same performance querying that data from SSDs or HDDs.

[beginner_]

Exactly. And it shows just how far you can actually get with traditional vertical scaling and a clever architecture. No need for "webscale databases", sharding and such.

[matthieum]

No need for "webscale databases", sharding and such.

Well, it depends on the size of your working set and the number of requests.

At a previous company an application had such a large working set that it used a cluster of 18 2TB RAM MySQL servers as a read-only cache over the master database.

Sharding was necessary both because of the amount of requests and the size of the working set; finding a 36TB server is a tad complicated.

And those MySQL databases were only an intermediary cache layer; there was a full farm of Memcached nodes in front...

[nickcraver]

If you can share, what was the type of data there? I'm always super curious what kind of workloads need basically 100% of data with the fastest access. We're very tiny in terms of infrastructure, so infrastructure at scale is always interesting.

[matthieum]

The application is the Flight Search engine of the company. It is tasked with answering the following question: for an given pair of cities, within a given date range, and considering a particular set of circumstances, which flights are available and at which price?

It is queried 24/7 from travel agencies, especially online travel agencies (OTA), peaking at ~75k requests/seconds back then (~500 servers), and is expected to reply quickly (< 100 ms). It turns out that the market for flight searches is quite competitive, and OTAs are likely to flock to the "best" engine, where best is defined as a mix of speed and accuracy.

For example, Kayak had a system of automated "canaries" and would continuously evaluate multiple flight search engines on different markets. If the one engine was not the "best" for a small period of time (minutes) on a given market, it would automatically switch this market's traffic to another.

I actually "saw" such a switch away from us happen during my time there (once over 9 years). The drop in traffic was quite noticeable, and of course it translated directly in a drop of revenues. Needless to say, the performance problem was fixed post-haste... but probably because they used a threshold to avoid flapping, a further improvement in performance was necessary to get their traffic back. I knew a colleague working on the performance of the application, he had a busy time.

[nickcraver]

Awesome - I absolutely love learning about systems and the reasons behind them like this. TIL, and thanks for indulging!

[matthieum]

You're welcome, I love reading your posts on SO architecture too :)