[Gnumed-devel] GNUmed (PostgreSQL) on USB thumbdrive benchmark

[Sebastian Hilbert]

The idea of running PostgreSQL off a removable drive such as USB thumbdrive or 
flash drive is appealing. However performance can sluggish. Who is to blame ?
Is it PostgreSQL that is too slow or poorly configured ? Is it GNUmed that 
stresses the database too much ? Or is it the storage media that slows things 
down ?

This clearly calls for some sort of benchmark. Google has little to offer on 
how to benchmark perfomance. Representative or not we are going to benchmark a 
few use cases.

1.) The storage media performance
2.) Real world installation performance
3.) Real world GNUmed client startup performance

- Storage media performance:
I am not aware of any GNU/Linux tool that can test read/write speed of both 
harddisk and usb-thumbdrives. Sure there is hdparm but it only works for 
harddisks. I reverted to HdTach and hdbench form CT magazine. One more word on 
theoretical performance. I use USB 2.0 so performance should be in the area of 
60MB/s. I have seen reports that USB-connected removable drives that have S-
ATA II storage media can reach throughput of up to 55MB/s. Effizienzgurus.de 
have tested USB-thumbdrive perfomance with HD-Tach and the list top read 
performance from 7,9 to 51,2 MB/s.

Let's see how the USB thumbdrives I bought for GNUmed compare:

Harddrive Seagate IDE 60GB native: avg. sequential read 22,9MB/s | random 
access 15ms |
Thumbdrive Intenso 2GB native: avg. sequential read 23,7 MB/s | random access 
0,4 ms | sequential write 6,4 MB/s
Thumbdrive Buffalo 2GB native: avg. sequential read 8,7 MB/s | random access 
8,7ms | sequential write 2,4 MB/s
Thumbdrive Samsung 8GB native: avg sequential read 18MB/s | random access 
1,7ms | sequential write 5,5 MB/s
Generic Business Card 1GB native: avg sequential read 16MB/s | sequential 
write 2,9 MB/s

The bigger the file size the faster the drive.

Same on VMware:
Harddrive (S-ATA native) in VMware : avg. sequential read 317MB/s  | random 
access 23ms
Thumbdrive Intenso 2GB on Vmware: avg. sequential read 8MB/s
Thumbdrive Buffalo 2GB on Vmware: avg. sequential read 4,5MB/s
Thumbdrive Samsung 8GB on Vmware: avg sequential read 5,7MB/s | random access 
7,8ms

Conclusion: a) Performance in VMWare is let's say not what we need here. Good 
to know since I guess a few people might try running it in a virtual machine. 
Don't. If you must copy the stuff from the thumbdrive to the virtual harddrive 
of the virtual machine. b) Native performance to the 2GB USB drive is nowhere 
close to the theoretical maximum for USB 2.0 but comes close to the IDE 
harddrive in this fairly old PC (1,6GHz AMD 2600+, 1,5GB RAM) c) The Buffalo 
2GB thumbdrives is cheap but unusable performance wise.

- PortableApps Suite installation (355MB as many files)
Harddrive Seagate IDE 60GB native : avg write 1,6 MB/s = 1667KB/s (3 minutes 
38 seconds)
Thumbdrive Intenso 2GB native : avg write 0,307 MB/s = 314KB/s (19 minutes 15 
seconds)
Thumbdrive Bufallo 2GB native :avg write 0,091 MB/s = 93KB/s (125 minutes)
Thumbdrive Samsung 8GB native:

Conclusion: a) The slower Buffalo stick has half the read speed compared to 
the faster Intenso stick. The write speed for the 355MB written to the faster 
drive is devastating at 0,3 MB/s compared to the harddisk installation at 
1,6MB/s. The installation to the faster USB thumbdrive is 7x faster then to 
the slower one.

- PortablePostgresql installation (171 MB as many files)
This involves unzipping 40,7MB to the USB drive. This is potentially limited 
by the unzip operation and host-PC speed but relative speeds still apply.

Harddrive Seagate IDE 60GB native: avg write 2,85 MB/s = 2918KB/s (1minute)
Thumbdrive Intenso 2GB native: avg write 0,63 MB/s = 650 KB/s (4 minutes 25 
seconds)
Thumbdrive Bufallo 2GB native: avg write 0,05 MB/s = 59KB/s (49 minutes 15 
seconds)
Thumbdrive Samsung 8GB native: avg write  0,21 MB/s = 214KB/s (13 minutes 35 
seconds)

Unzipping the Postgresql Server to this the slower USB (Buffalo) drive took 
12x longer than to the faster one (Intenso). The slower USB drive is 49x 
slower then the harddrive. The 8GB Samsung drive doesn't come close to the 
harddrive. This implies that bigger drives don't neccessarly have better 
performance.

- PortableGNUmed client time to login window (first start uncached, second 
start cached)
Harddrive Seagate IDE 60GB native: (4 sec , 3 sec
Thumbdrive Intenso 2GB native: (5 sec, 5 sec )
Thumbdrive Bufallo 2GB native: (8 sec, 6 sec )
Thumbdrive Samsung 8GB native: (2min 29 sec, 1min 30 sec)

- PortableGNUmed client time from login window to first window (first login, 
second login)
Note that both client and server are running off the same drive. For the 
second login times are shorter since much data is cached.

Harddrive Seagate IDE 60GB native: (17 sec , 10 sec )
Thumbdrive Intenso 2GB native: (54 sec ,  21 sec )
Thumbdrive Bufallo 2GB native: ( 2 min 41 ,  59 sec )
Thumbdrive Samsung 8GB native: (2min 22 sec, 1min 38 sec )

conclusion: The Intenso drive is the clear winner. The Buffalo and Samsung 
drives have disappointing performance. The startup performance is interesting 
but the real performance of the database can only be measured during heavy sql 
operations.

- Pushing a 12 MB document into the database
Harddrive Seagate IDE 60GB native: 30 sec
Thumbdrive Intenso 2GB native: 5 minutes 1 sec
Thumbdrive Bufallo 2GB native: 15 minutes 30 sec
Thumbdrive Samsung 8GB native: 32 minutes sec

The results are somewhat helpful to get a feeling for the database speed. Note 
that even for the harddrive it takes 7x its drive speed to complete the task.  
Be aware that I cannot say if these results are reproducable.

- Retrieving a 12 MB document from the database
Harddrive Seagate IDE 60GB native: 5 sec
Thumbdrive Intenso 2GB native: 12 sec
Thumbdrive Bufallo 2GB native: 12 sec
Thumbdrive Samsung 8GB native: 1 min 11 sec

Conclusions: In theory the user oriented performance should be identical 
between the USB thumbdrive and the harddisk. Unfortunately it is not always 
the case. If you can make sure you get a decent USB thumbdrive. The 32GB OCZ 
Rally2 is fairly usable at 30MB/s sequential read and 22MB/s sequential write 
performance. But be aware that the Corsair Flash Voyager while not bad at all 
comes in second at 22MB/s read and 10MB/sec write performance. There is a 
catch. A huge difference exists between the sequential read/write values and 
the random read/write vaues. According to codinghorror.com the random read 
performance is 6MB/s and the write performance is 3MB/s. Those are the values 
you are looking for.

Only the best USB thumbdrives come close to the el-cheapo IDE harddisk I used 
for testing. This is really disappointing since the first batch of USB drives 
I ordered for GNUmed is really only useful as storage media. I had to order a 
second batch. If you intend to use one of these in production for GNUmed you 
should really be ready to spend the $79 for an adequate 32GB USB thumbdrive. 
If you wanna go cheap make you can go with something like the 2GB Intenso 
since it gives you acceptable performance.

The road ahead:
None of the storage media I had available really come close to a real 
harrddrive. Options to pursue in the future are flash media, solid state 
drives and external harddrives. External legacy harddrives are error prone due 
to many moving parts. Flash or SSD might be a way out.