How important is drive speed for a MySql server 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.
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 &quot;few&quot; 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 &quot;buffer_pool&quot; 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!