0

I'm trying to invest in a self managed server for MySQL. My use case will involve querying a full text index of anywhere from 5m to 20m records. About 5 thousand full text searches per hour. Usually the searches will each query multiple match against statements as I will need to search multiple columns in the table index. The only write queries will be to update a single field of a single record approximately 10 to 20 times per minute.

My question is this... How beneficial would it be to setup an 8 drive NVMe raid 0? I know indexes are usually queried from RAM as this is usually significantly faster than storage drives. But with the newest PCIe 4.0 NVMe SSDs and the crazy speeds of these drives, I could probably setup raid0 that has about twice throughout as the fastest DDR4 ram.

Or does none if this even matter? Is throughout the factor that even matters? Can you even force indexes to be queried from the storage drive?

Thanks in advance for the advice.

5
  • You should think about data security and disaster recovedry as well, a raid 0 when one drove fails is destroyed.
    – nbk
    Commented Apr 4, 2021 at 9:21
  • If you have 8 drives, might go for a 2 group RAID 10 array - but consider putting the OS, the data (or chunks thereof), the logs and the server software on different disks... much of this will depend on your usage pattern...
    – Vérace
    Commented Apr 4, 2021 at 12:01
  • 2
    Why do you suppose you make a storage array with double the speed of RAM? Have you done this? RAM is orders of magnitude faster than solid-state storage, even NVMe, even in a RAID array. Commented Apr 4, 2021 at 16:18
  • Why would you need multiple queries to match against multiple columns of the same table? Do you know that you can create a fulltext index over multiple columns, so you can search them all in one query? Commented Apr 4, 2021 at 16:21
  • 5,000 searches per hour is not a high load. I support many databases at work with a query rate between 10 and 25,000 queries per second. Admittedly, these are simpler queries, not fulltext search. Commented Apr 4, 2021 at 16:23

1 Answer 1

1

How many GB in the tables? It sounds like the RAM might be big enough to cache everything all the time? If that is the case, the disk is irrelevant.

Even if RAM is not big enough, FULLTEXT does not need to hit the disk that much. Think of a query as being controlled by how many disk hits are needed, minus how many were avoided by caching.

A Rule of Thumb is that a disk hit on HDD is 10ms; on SSD is 1ms. If the query needs only a "few" hits, the response time on even HDD will be adequate.

I'm saying that FT 5K searches per minute is probably feasible with your proposed configuration! Even if there were 20 updates per second.

Every action involving the disk actually occurs in the "buffer_pool" in RAM. So, if the disk must be hit, the time taken for the query is necessarily slower regardless of disk speed.

On the other hand, NVMe is getting cheap, so why not!

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.