Sqlite3 写入速度测试：30万/秒

Question

SSD 盘写入速度可以高达30万/秒

[2020-09-03 10:44:43.174] sqlite3perf generate -r 500000 -b 100 --db 'a.db?_journal=wal&sync=0' --prepared
2020/09/03 10:44:43 Generating records by config &{NumRecs:500000 BatchSize:100 Vacuum:false Prepared:true LogSeconds:2}
2020/09/03 10:44:43 Opening database
2020/09/03 10:44:43 Dropping table 'bench' if already present
2020/09/03 10:44:43 (Re-)creating table 'bench'
2020/09/03 10:44:43 Setting up the environment
2020/09/03 10:44:43 Starting progress logging
2020/09/03 10:44:43 Starting inserts
2020/09/03 10:44:44 500000/500000 (100.00%) written in 1.564473342s, avg: 3.128µs/record, 319596.37 records/s

sqlite3perf on master [?] via v1.15 via v2.7.16
[2020-09-03 10:44:44.980] ls -lh a.db
-rw-r--r--  1 bingoobjca  staff    52M  9  3 10:44 a.db

sqlite3perf on master [?] via v1.15 via v2.7.16
[2020-09-03 10:45:02.768] sqlite3 a.db "select count(*) from bench"
500000

sqlite3perf on master [?] via v1.15 via v2.7.16
[2020-09-03 10:45:18.680] sqlite3 a.db "select * from bench order by ID desc limit 3"
499999|b4360f0166243704|92774f4d4de17688998d2adb9f4de955714a5adb5e2d2d82fbe891196b58140c
499998|c00c55a85e1d97f2|2309d1f02a1c433c23f16e6e7e7a8f39f39f47bf4ea3790160149a702eadc801
499997|c8d46b21b5d9be88|5e539cb2d84838acc67e1eaaf0cbe416e98c6130564475946dda85d7a516de01

HHD 盘写入速度可以达到10万/秒

[root@localhost ~]# ./sqlite3perf generate -r 500000 -b 100 --db 'a.db?_journal=wal&sync=0' --prepared
2020/09/03 11:17:37 Generating records by config &{NumRecs:500000 BatchSize:100 Vacuum:false Prepared:true LogSeconds:2}
2020/09/03 11:17:37 Opening database
2020/09/03 11:17:37 Dropping table 'bench' if already present
2020/09/03 11:17:37 (Re-)creating table 'bench'
2020/09/03 11:17:37 Setting up the environment
2020/09/03 11:17:37 Starting progress logging
2020/09/03 11:17:37 Starting inserts
2020/09/03 11:17:39 206999/500000 ( 41.40%) written in 2.004364054s, avg: 9.682µs/record, 103274.15 records/s
2020/09/03 11:17:41 414099/500000 ( 82.82%) written in 4.002220203s, avg: 9.664µs/record, 103467.32 records/s
2020/09/03 11:17:42 500000/500000 (100.00%) written in 4.787455631s, avg: 9.574µs/record, 104439.61 records/s
[root@localhost ~]# ls -lh a.db
-rw-r--r--. 1 root root 53M 9月   3 11:17 a.db
[root@localhost ~]# sqlite3 a.db "select count(*) from bench"
500000
[root@localhost ~]# sqlite3 a.db "select * from bench order by ID desc limit 3"
499999|4c04552584deba8a|07c91406c05197539d07fc41429ab904e7793fcce531ad4f17d233ca204d8d2f
499998|58b339ee1aad5823|b03e7d6b7181051dbdd7994535e5562bede446bd83090811ee5a64a767e0e2ad
499997|3d9f37009a1e2778|75ddbab14b04b1559360be0654e191758f3821789e3f410517f27098383671d3
[root@localhost ~]# cat /sys/block/sda/queue/rotational
1

怎么判断磁盘是机械硬盘还是固态硬盘

Check if the Disk is rotational

As you may already know, the traditional Hard Disk Drive (HDD) stores the data on a circular disc known as platter. When the disc spins, the moving read/write head will access the data. The faster the disk spins (rotates), the faster the hard disk works.

On the other hand, Solid State Drive (SDD) is modern storage technology and faster type of disk drive that stores the data on instantly-accessible flash memory chips. Unlike the traditional HDDs, the SSDs doesn't have any moving parts and SSDs don't rotate.

So, to find whether the installed disk is SSD or normal HDD, just check if the disk is rotational using the following command:

$ cat /sys/block/sda/queue/rotational

If the output is 1, the disk is HDD. If the output is 0 (zero), the disk is SDD. Because, SSDs won't rotate. So the output should be zero if you have SSD in your system.

Each drive has a directory in /sys/class/block/ location. So, you can check other drives details as well.

• $ cat /sys/block/sdb/queue/rotational
• $ cat /sys/block/sdc/queue/rotational

[root@localhost ~]# df -h
文件系统                 容量  已用  可用 已用% 挂载点
/dev/mapper/centos-root   50G  5.4G   45G   11% /
devtmpfs                  63G     0   63G    0% /dev
tmpfs                     63G     0   63G    0% /dev/shm
tmpfs                     63G  117M   63G    1% /run
tmpfs                     63G     0   63G    0% /sys/fs/cgroup
/dev/sda2               1014M  141M  874M   14% /boot
/dev/sda1                200M  9.8M  190M    5% /boot/efi
/dev/mapper/centos-home  2.6T   34M  2.6T    1% /home
tmpfs                     13G     0   13G    0% /run/user/0
[root@localhost ~]# pwd
/root
[root@localhost ~]# lsblk
NAME            MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda               8:0    0   2.6T  0 disk
├─sda1            8:1    0 199.5M  0 part /boot/efi
├─sda2            8:2    0     1G  0 part /boot
└─sda3            8:3    0   2.6T  0 part
  ├─centos-root 253:0    0    50G  0 lvm  /
  ├─centos-swap 253:1    0     4G  0 lvm  [SWAP]
  └─centos-home 253:2    0   2.6T  0 lvm  /home
[root@localhost ~]# cat /sys/block/sda/queue/rotational
1

测试代码

https://github.com/bingoohuang/sqlite3perf