Does a mechanical hard drive significantly impact the query performance of a TiDB database?

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 机械硬盘对tidb数据库的查询性能影响大吗?

| username: TiDBer_djgos04V

[Test Environment for TiDB]
Deployed TiDB on the server, and found that the query speed is much slower compared to the virtual machine, even though the server’s configuration is much higher than the virtual machine. Therefore, I am considering whether it is a hard disk issue, as the server uses a mechanical hard drive while the virtual machine uses an SSD.

| username: WalterWj | Original post link

Running PD and TiKV on mechanical drives will have a significant impact. Check the server hardware configuration requirements: TiDB 软件和硬件环境建议配置 | PingCAP 文档中心

| username: 像风一样的男子 | Original post link

You can test disk performance in two environments with the following commands:

IOPS test reference command

fio -group_reporting -thread -name=iops_test -rw=randwrite -direct=1 -size=8G -numjobs=8 -ioengine=psync -bs=4k -ramp_time=10 -randseed=0 -runtime=60 -time_based

Read/write bandwidth test reference commands

fio -group_reporting -thread -name=iops_test -rw=randwrite -direct=1 -size=8G -numjobs=8 -ioengine=psync -bs=1m -ramp_time=10 -randseed=0 -runtime=60 -time_based
fio -group_reporting -thread -name=iops_test -rw=randread -direct=1 -size=8G -numjobs=8 -ioengine=psync -bs=1m -ramp_time=10 -randseed=0 -runtime=60 -time_based

| username: Kongdom | Original post link

Slowness is not necessarily a disk issue. Test with fio on both physical and virtual machines and then take another look.

| username: 啦啦啦啦啦 | Original post link

The gap is significant; the bottleneck for mechanical drives is usually the disk itself.

| username: tidb菜鸟一只 | Original post link

Databases definitely have high demands on disks… Any database does.

| username: TiDBer_vfJBUcxl | Original post link

Sure, here is the translation:

“Take a look at this

| username: 大飞哥online | Original post link

Databases have high demands on disks, mechanical drives are running hot.

| username: TiDBer_djgos04V | Original post link

Command Reference for Running IOPS Test

This is the server:

This is the virtual machine:

Command Reference for Running Read/Write Bandwidth Test

This is the server:

This is the virtual machine:

The IOPS test difference doesn’t seem significant, but the read/write difference appears quite large.

| username: 像风一样的男子 | Original post link

I didn’t see the first IOPS result in the screenshot.
The IOPS result is in the place of my screenshot.

| username: TiDBer_djgos04V | Original post link

Oh, I see.
This is the server’s:

This is the virtual machine’s:

| username: 像风一样的男子 | Original post link

IOPS are only a few thousand, which makes it very difficult for this type of disk to run a database. NVMe SSDs are not expensive nowadays, so I recommend switching to SSDs.

| username: TiDBer_djgos04V | Original post link

Is it that high? Why does my virtual machine only have a few thousand IOPS? My computer uses an NVMe SSD.

| username: Fly-bird | Original post link

The impact is significant.

| username: zhanggame1 | Original post link

There is no performance to speak of; the IOPS of a mechanical disk is only a few dozen, while even a poor SSD has a few thousand. Use a mechanical disk to install Oracle.

| username: 昵称想不起来了 | Original post link

Very large, go for NVMe SSD.