Mysql – What kind of SSD to speed up querying a lot of data

mariadbMySQLperformanceperformance-tuningquery-performancessd

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 $?

Best Answer

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.

Related Solutions

Mysql – Improve `Update` performance (rows locking issue)

First, each time you UPDATE the status column, you are having to update the index as well (source). Evaluate your indexing to see if you really need the index on the status column. My guess is no, since it has an extremely low cardinality and MySQL probably won't use it anyway.

If you ignore me and think you do need it, follow the advice in the article to drop the index before your loop and re-add it after you're done.

Here are some other things you might do if that doesn't help:

You are taking all the columns from the data but only using number. Don't do a SELECT *, but instead a SELECT number. That won't help your writes, but it is a good performance practice. Only select the columns you're using.
Your number index isn't getting used at all. This means it is not unique enough to be useful for updating. (Slight tangent: how many rows does a single UPDATE affect?) I would drop it, or at least add it to process index.
It looks like process is unique enough for MySQL to whittle the amount of rows down to 16k, instead of 1 million. In light of this, I would add AND process=x to your update statement (I'm assuming you know process from the original SELECT statement):
```
-- FAILED--
UPDATE data SET status = 2, error='$error' WHERE process=X AND number = $data['number']

-- SUCCESS --
UPDATE data SET status = 1 WHERE process=X AND number = $data['number']
```

A hint about unnecessary indexes in InnoDB. InnoDB is using a hidden 'primary key' (since you don't have one defined) and is using that when it writes the indexes. So for each Index you're using, you add the size of the index + the size of the hidden primary key to the data file. If you're not using the index (or MySQL can't use it), you are wasting space and adding overhead each time you insert a new number (same for status, as discussed earlier)

Mysql – SSD vs HDD for databases

Yes with a lot of reads and reporting an SSD will make a huge difference. From 7200 RPM drive you could expect no more than ~100 IOPS while cheapest SSD can be as minimum 5x as fast as that. With good SSD you can get to 20000 IOPS or even more.

Also random writes in SSD are a lot faster as disk does not have to move every time.

Best Answer

Related Solutions

Mysql – Improve `Update` performance (rows locking issue)

Mysql – SSD vs HDD for databases

Related Question