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

[Sebastian Hilbert]

Am Freitag 26 Juni 2009 16:21:26 schrieb Dave Cramer:

Hi Dave,

> Postgresql is really not setup for this.
>
I hope I did not leave the impression that I blame postgresql for anything. I 
simply reported my findings.

There is a simple use case. People are interested in having the database 
travel along with them.

Since as far as I know PostgreSQL is there to stay for GNUmed I was interested 
in if it is possible to do it.

What is the difference that affects GNUmed?

Sebastian

> Remember it uses wal logs to write transaction logs before writing
> anything.
>
> Berkley DB is designed for flash.
>
> DAve
>
> On Fri, Jun 26, 2009 at 10:14 AM, Sebastian Hilbert <
>
> address@hidden> wrote:
> > 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.
> >
> >
> > _______________________________________________
> > Gnumed-devel mailing list
> > address@hidden
> > http://lists.gnu.org/mailman/listinfo/gnumed-devel