1

We are upgrading hardware for a MySQL database server (DB is around 200GB). We're getting 4x Samsung 850 Pro SSDs and are planning to run them in a RAID10 configuration. Also, 128GB of RAM.

The quote from our supplier includes dual 6 core Xeons (E5-2620V2 2.1G) with an option to upgrade to E5-2630V2 2.6G instead.

The price for the faster CPUs would be about $622 extra (about $311 each), which is around 6.5% of the price of the server.

Since we don't have the hardware, of course, I can't benchmark it and see whether the faster CPUs would make a difference for our database and the way we query it. But is there some way to get an idea of whether the faster CPUs will help? i.e. assuming the current bottleneck is I/O and we replace the disks with SSDs, is it possible/likely for the E5-2620V2 CPUs to become the bottleneck?

Thanks.

2

I'm a proud owner of a Samsung 850 Pro for my desktop machine. It is a great disk, but it is not server-grade, and it is far from the PCI Flashcards sold by Virident and FusionIO (to put examples of some known brands). On the specialized hardware, a recent version of MySQL is almost a must, and some configuration tuning is needed to get most of them (change buffer, innodb checksums, transaction logs, doublewrite area, etc.).

Check some of the vendor benchmarks (Oracle MySQL, MariaDB, Percona), as they usually boast its capabilities on those kind of devices.

However, a generalization cannot be reached- most of the problems are load-dependent. To give you an extreme example: if your queries all look like this:

SELECT AES_DECRYPT(*, key) FROM my_table;

Your CPU is probably going to be your bottleneck, while if you only insert new rows, then most of the delay will probably due to the disk latency. You should check if with the current hardware, the CPU load is high, that will probably increase with the new storage- but you may still hit other problems, like concurrency issues due to several threads trying to update the same row at the same time.

assuming the current bottleneck is I/O and we replace the disks with SSDs, is it possible/likely for the E5-2620V2 CPUs to become the bottleneck

It is possible, but just with a bad configuration (what I mentioned before), assuming recent versions of MySQL (5.6, 5.7). Remember that SSDs are still way slower than the 1st cache level. You should probably ask again when the hardware is installed and meanwhile read sources like:

  • Thanks for the answer. The Samsung 850 Pro is surely going to perform much better than e.g. SAS drives, right? In what way is it "not server-grade"? If I understand your answer correctly, you think that it's unlikely for the faster CPU to make a difference unless the config is bad, (but of course it depends on the queries.) – Wodin Oct 21 '14 at 16:22
  • @Wodin Yes, way better in most scenarios. The first link I have shared with you has way more specifics on differences between server-grade and consumer drives than I can tell you on 500 characters. You can also watch the corresponding video: youtube.com/watch?v=sQk1COoyagM PZ knows what he is talking about better than I do. It is mostly about reliability, cache and cost. – jynus Oct 21 '14 at 19:05
  • Thanks. I've skimmed the links you sent, but will go through them more thoroughly as soon as I am able. – Wodin Oct 22 '14 at 6:36
2

Since we don't have the hardware, of course, I can't benchmark it

While you can't simulate the exact hardware you might be able to get some estimates by comparing simpler local hardware with similar relative differences. If you are upgrading from spinning rust in a similar RAID config then you could benchmark a single traditional drive against a single SSD in a dev/test box with a similar amount of RAM.

It wouldn't be a truely accurate measure of course but you'll see what sort of order of throughput difference you might see under normal workloads for you application so be able to better guesstimate the worth of the CPU upgrade. It would likely be more useful a gauge than general advice that isn't specific to your application.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.