Friday, June 26, 2009

PostgreSQL on USB flash drive performance

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
Thumbdrive OCZ 16GB native: : avg sequential read 27MB/s | sequential write 8,7 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:
Thumbdrive OCZ 16GB native: avg write 0,8MB/s = 865KB/s (06 minutes 59 seconds)

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 (Intenso) 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 (1min)
Thumbdrive Intenso 2GB native: avg write 0,63 MB/s = 650 KB/s (4 min 25 sec)
Thumbdrive Bufallo 2GB native:
avg write 0,05 MB/s = 59KB/s (49 min 15 sec)
Thumbdrive Samsung 8GB native: avg write 0,21 MB/s = 214KB/s (13 min 35 sec)
Thumbdrive OCZ 16GB native: avg write 1,2 MB/s = 1306KB/s (2 min 14 sec)

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)
Thumbdrive OCZ 16GB native: (4 sec, 3 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 )
Thumbdrive OCZ 16GB native: (20 sec, 17 sec )

conclusion: The OCZ drive is the clear winner. The cheaper Intenso is second. 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 min 1 sec
Thumbdrive Bufallo 2GB native:
15 min 30 sec
Thumbdrive Samsung 8GB native: 32 min
Thumbdrive OCZ 16GB native: 3 min 4 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
Thumbdrive OCZ 16GB native: 9 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 16GB OCZ is fairly usable at 30MB/s sequential read and 8 MB/s sequential 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.

Wednesday, June 17, 2009

Umstieg von Windows auf Ubuntu

Was tut man wenn man ein Notebook mit 500MB RAM und Windows XP im Einsatz hat und das immer langsamer wird ?

Die Kurzversion: Man installiert LInux.

Windows wurde immer langsamer. Zugegebenermassen war es noch die allererste Installation von Windows. Das allein ist viele Wert und hat bisher gut funktioniert. Die ersten versuche das alte Notebook sicherer zu machen war Firefox zu installieren. Aber mit zunehmender Internetnutzung kam man einen Virenscanner nicht drum herum. Und der braucht viele Ressourcen. Nach und nach erfolgte die Umstellung auf OpenOffice. StarMoney hielt uns noch bei Windows. Als dann nach etlichen Jahren die Startzeiten unerträglich wurden war die Zeit für den Umstieg reif.

Es wurde eine Live-CD von Ubuntu 9.04 beschafft und ausprobiert. Alle Komponenten des Notebook inklusive WLAN wurden sofort erkannt. Also erfolgte eine Festisntallation. Das wurde bisher nicht bereut. Das Notebook bootet schneller. Firefox, OpenOffice und Starmoney sind weiter im Einsatz. Der Drucker Brother MFC-465 liess sich mit original Brother-Treibern problemlos einrichten. Sogar Suspend to RAM funktioniert auf Anhieb.

Vorteil: Kein Virenscanner mehr notwendig. Regelmässige Updates. Alle Programme weiter nutzbar. Schnellere Startzeit. Und GNUmed lässt sich nun auch Problemlos installieren.