0

For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an external hard drive. This was sufficient for simple queries. But for more complex tasks the queries take several days. Most of the tasks will be SELECT queries (with multiple joins).

This is why I want to invest into a new internal hard drive (1-2 TB), which will be used in a server setup (128 GB RAM). Due to performance reasons, I think a SSD would be the right choice. The mainboard has both free PCIe x8 and SATA 6.0GB/s slots.

Is there a difference between PCIe SSDs and M.2 SSDs?

What kind of SSD would be the best value for money according to my requirements? I do not want to spend more than 300 $?

Improve this question

2 Answers 2

Reset to default
0

would be the best value for money according to my requirements

Shopping/recommendations are generally considered off-topic here, so I'll stick with technical differences and not discus value judgements.

Is there a difference between PCIe SSDs and M.2 SSDs?

M.2 is just a form factor: it can hold drives with either SATA or nVME interfaces. A new PCIe based card will almost certainly be nVME but may be a SATA interface and SATA drive soldered to a board.

What kind of SSD to speed up querying a lot of data?

The chief difference between SATA and NVMe drives is the interface speed which imposes the throughput bottleneck. As latency, which affects random access rates, is likely to be similar between the various SSD types you want NVMe (either surface mounted on a PCIe card or in an M.2 slot) for speed.

Of course compared to your current solution with a drive connected via USB, both should be significantly faster.

Improve this answer
2
  • Thanks a lot for your explanation. Will there be performance losses if a M.2 to PCIe adapter will be used instead of mounting it directly in a M.2 slot?
    – jwarz
    Commented Feb 20, 2019 at 14:19
  • Probably not but it depends on the adaptor, you'd have to research specific products.
    – David Spillett
    Commented Feb 20, 2019 at 16:13
2

You can't fix a performance problem by throwing hardware at it.

Even if you can find a fast enough SSD to handle the several-day query today, what will happen tomorrow when the data is bigger and/or you need another JOIN.

In many cases, a "several-day query" can be turned into a "several-hour" query by reformulating the query and/or adding a 'composite' index. Or even "re-thinking" the problem.

Let's discuss the query and see if it can be sped up for $0.00.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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