前幾天在淘寶上買了個 SSK 的USB 3.1 Gen2 (type-c) NVME SSD 外接盒
手邊也剛好有一條多的intel 600p nvme ssd 就順手來做個比較
目標是看看有沒有可能直接用外接的SSD來跑postgresql
把600p 裝進去外接盒之後，就先來看一些簡單的資訊
不過沒想到用了幾個指令，都沒辦法辨別出正確的型號
fdisk
沒有看到廠牌、型號1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
182019-09-10 13:20:55 [minion@hqdc075 ~]$ sudo fdisk /dev/sdb
Welcome to fdisk (util-linux 2.31.1).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Command (m for help): p
Disk /dev/sdb: 238.5 GiB, 256060514304 bytes, 500118192 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disklabel type: dos
Disk identifier: 0x59511d8e
Device Boot Start End Sectors Size Id Type
/dev/sdb1 2048 500118191 500116144 238.5G 83 Linux
Command (m for help): q
inxi
內接的intel ssd 有看到，不過外接的這顆SSD沒有model 可是可以看到序號
我在想這個序號應該是外接盒的序號，而不是SSD的？
1 | 2019-09-10 13:21:51 [minion075 ~]$ sudo inxi -Dxx |
dmesg
一樣，也是認不出 SSD ，但是有抓到外接盒
1 | [] hub 4-0:1.0: USB hub found |
直接測試吧
不看型號了，直接測試吧！先切好分割、格式化，然後掛載到 /mnt，再用 dd 測試寫入
結果如下
1 | 2019-09-10 13:24:03 [minion@hqdc075 ~]$ for i in {4,8,16,32,64,128,256,512,1024,2048,4096,8192,16384};do sudo dd if=/dev/zero of=/mnt/"$i"k bs="$i"k count=1k;done |
可以發現，當寫入8G檔案的時候，速度開始急遽下降
翻了一下google 看到這張表格
https://www.anandtech.com/show/10850/the-intel-ssd-600p-512gb-review
intel 600p 256G 的SLC Cache 只有8.5GB
合理解釋了為什麼當寫入8G的檔案時，速度會掉那麼慘，跟SATA硬碟差不多了
用iobench 測試看看
得到一樣的結論，8G左右，就會把cache塞暴，然後掉速1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
322019-09-10 13:44:59 [changch@hqdc075 iobench]$ sudo ./iobench.sh --sync --dir /mnt --megabytes 8192
Target directory: /mnt
Testfile size: 8192 x 1 Megabyte
1. Write benchmark without cache
8589934592 bytes (8.6 GB, 8.0 GiB) copied, 36.8695 s, 233 MB/s
2. Write benchmark with cache
8589934592 bytes (8.6 GB, 8.0 GiB) copied, 64.1573 s, 134 MB/s
3. Read benchmark with dropped cache
8589934592 bytes (8.6 GB, 8.0 GiB) copied, 9.21821 s, 932 MB/s
4. Read benchmark without cache drop
Start 1 of 5...
8589934592 bytes (8.6 GB, 8.0 GiB) copied, 1.02253 s, 8.4 GB/s
Start 2 of 5...
8589934592 bytes (8.6 GB, 8.0 GiB) copied, 0.832002 s, 10.3 GB/s
Start 3 of 5...
8589934592 bytes (8.6 GB, 8.0 GiB) copied, 0.81194 s, 10.6 GB/s
Start 4 of 5...
8589934592 bytes (8.6 GB, 8.0 GiB) copied, 0.821035 s, 10.5 GB/s
Start 5 of 5...
8589934592 bytes (8.6 GB, 8.0 GiB) copied, 0.808803 s, 10.6 GB/s
Done.
2019-09-10 13:47:40 [changch@hqdc075 iobench]$
那如果不用8G的檔案大小來測試呢？看起來是比較正常一點1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
322019-09-10 13:47:40 [changch@hqdc075 iobench]$ sudo ./iobench.sh --sync --dir /mnt --megabytes 4096
Target directory: /mnt
Testfile size: 4096 x 1 Megabyte
1. Write benchmark without cache
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 7.55296 s, 569 MB/s
2. Write benchmark with cache
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 20.6237 s, 208 MB/s
3. Read benchmark with dropped cache
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 4.63622 s, 926 MB/s
4. Read benchmark without cache drop
Start 1 of 5...
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 0.518597 s, 8.3 GB/s
Start 2 of 5...
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 0.436877 s, 9.8 GB/s
Start 3 of 5...
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 0.437854 s, 9.8 GB/s
Start 4 of 5...
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 0.420224 s, 10.2 GB/s
Start 5 of 5...
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 0.431023 s, 10.0 GB/s
Done.
2019-09-10 13:50:09 [changch@hqdc075 iobench]$
跑看看pgbench ??
試試看這次的目標，用 external nvme ssd 跑資料庫，會不會跟放在本機的PCI-E SSD 有所差距？
data_directory in internal pcie-ssd
initialize pgbench database1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
212019-09-10 13:52:35 [minion@hqdc075 ~]$ sudo su - postgres
postgres@hqdc075:~$ createdb pgbench
postgres@hqdc075:~$ pgbench -i -U postgres -s 10 pgbench
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.08 s, remaining 0.70 s)
200000 of 1000000 tuples (20%) done (elapsed 0.19 s, remaining 0.75 s)
300000 of 1000000 tuples (30%) done (elapsed 0.36 s, remaining 0.83 s)
400000 of 1000000 tuples (40%) done (elapsed 0.49 s, remaining 0.73 s)
500000 of 1000000 tuples (50%) done (elapsed 0.58 s, remaining 0.58 s)
600000 of 1000000 tuples (60%) done (elapsed 0.75 s, remaining 0.50 s)
700000 of 1000000 tuples (70%) done (elapsed 0.89 s, remaining 0.38 s)
800000 of 1000000 tuples (80%) done (elapsed 0.99 s, remaining 0.25 s)
900000 of 1000000 tuples (90%) done (elapsed 1.11 s, remaining 0.12 s)
1000000 of 1000000 tuples (100%) done (elapsed 1.27 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
run pgbench1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24postgres@hqdc075:~$ pgbench -t 10 -c 100 -S -U postgres pgbench
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 10
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 1000/1000
latency average = 32.118 ms
tps = 3113.559459 (including connections establishing)
tps = 3135.056341 (excluding connections establishing)
postgres@hqdc075:~$ pgbench -t 1000 -c 100 -S -U postgres pgbench
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 10
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 100000/100000
latency average = 3.753 ms
tps = 26643.223990 (including connections establishing)
tps = 26659.061459 (excluding connections establishing)
data_directory in external NVME SSD
initialize pgbench database1
2
3
4
5
6
7
8
9
10
11
12
13
14
15postgres@hqdc075:~$ pgbench -i -U postgres -s 10 pgbench
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.08 s, remaining 0.70 s)
200000 of 1000000 tuples (20%) done (elapsed 0.19 s, remaining 0.76 s)
300000 of 1000000 tuples (30%) done (elapsed 0.35 s, remaining 0.81 s)
400000 of 1000000 tuples (40%) done (elapsed 0.49 s, remaining 0.74 s)
500000 of 1000000 tuples (50%) done (elapsed 0.60 s, remaining 0.60 s)
600000 of 1000000 tuples (60%) done (elapsed 0.76 s, remaining 0.51 s)
700000 of 1000000 tuples (70%) done (elapsed 0.89 s, remaining 0.38 s)
800000 of 1000000 tuples (80%) done (elapsed 1.01 s, remaining 0.25 s)
900000 of 1000000 tuples (90%) done (elapsed 1.15 s, remaining 0.13 s)
1000000 of 1000000 tuples (100%) done (elapsed 1.32 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
run pgbench
可以看到兩邊的結果其實是差不多的，作為資料庫備份是一定沒有問題
至於能不能直接作為資料庫空間使用？我想也許可以嘗試看看..
1 | postgres@hqdc075:~$ pgbench -t 10 -c 100 -S -U postgres pgbench |
update:
剛剛看了一下，發現這樣只有測試到 read (select only)
或許要找找看其他的測試方法
接下來直接用 restore 測試
1 | #### restore 5G database with external nvme ssd |
restore 5G database with internal pci-e nvme ssd
1 | postgres@hqdc075:~$ dropdb demo |
看起來是沒有什麼區別，那如果是外接的 SATA SSD呢？
dmesg 看得到型號耶..
1 | [23995.478928] usb 2-4: new SuperSpeed Gen 1 USB device number 3 using xhci_hcd |
restore 5G database with external sata SSD
很意外的，速度居然還是差不多？？
1 | postgres@hqdc075:~$ createdb demo |
run pgebnech
這個差很多了，tps 從前面的 26000 掉到剩下 2800 ，差十倍左右！
1 | postgres@hqdc075:~$ pgbench -t 10 -c 100 -S -U postgres pgbench |
疑問：
- 什麼原因會讓外接/內建 nvme ssd 和外接SATA SSD 在 restore db時，花了差不多時間，但是 tps 差異那麼大呢？
- 這樣測試，似乎沒有真正測出外接nvme ssd 跑資料庫時候的效能？