How mysql database performance can impact by different types of disk[hard disks] I have a ques regarding mysql server performance which how related to server's different types of disk. I mean how disk impacting Mysql Server performance and which type of disk is more convenient for good performance. Please suggest the best way to decide which type is disk is best like ssd,nvme etc
Short answer: It's 2020, the case for spinning rust is super-weak.

Long answer: It depends on how much memory you have and what your read:write ratio is.

If the workload is mostly reads, and you have enough buffer pool to cache all of the even remotely hot data, disk speed won't matter as much because once the buffer pool is warmed up, not much data will be coming off the disk.

For writes, every transaction is going to require at a minimum 2-4 disk IOPS with InnoDB.

1) Once into the transaction log

2) Once into the target tablespace

3) Once again into the transaction log (unless innodb_doublewrite=0)

4) Once into the binlog (if log_bin and sync_logbin=1)

A typical 7200rpm disk can sustain about 120 IOPS (that's how many times per second the required sector comes around to meet the heads).

So if you don't need to sustain more than about 30 transactions per second (note: transactions per second is not the same as queries per second), you may be able to scrape by with a spinning disk.

There are various things you can do to speed this up, if you can afford to lose transactions in a system crash scenario, e.g.:

innodb_doublewrite=0
innodb_flush_logs_at_trx_commit=0


or even better, if you are on ZFS, zfs set sync=disabled on the relevant volume, and enjoy the fact that on ZFS innodb_doublewrite=0 is safe and there is no need to set innodb_flush_logs_at_trx_commit=0 if you set sync=disabled.

That will make the workload a lot more sequential and thus the IOPS limitation will be less severe. But really, if you have to resort to this sort of thing to squeeze more performance out of the storage, you really should be buying an SSD instead.

NVMe is usually 4x faster on throughput and much faster on latency than SATA SSD.

So you will have to decide in the context of your workload what your performance requirements are and what you can afford, and plot a compromise through those parameters.