Thread: PostgreSQL on VMWare vs Windows vs CoLinux
Hello pgsql-performance, I was using the native windows PostgreSQL 7.5Dev and was adviced by several people to use an emulated PostgreSQL instead, as it is just a beta. Well, I give it a whirl and tried both commercial VMWare and the freeweare open-source CoLinux, both work under Windows and both emulate Linux, that's a quick review of my experience with them, may someone in need learn from it. This might be not the best place for such a post, but since the subject was brought up here, I'll post it here as well. If someone thinks it should be posted somewhere else, let me know. Installation & Configuration ---------------------------- VMWare: On the bright side, the installation went quite smoothly, VMWare configured all the network stuff by itself and I had no trouble using the net right away. On the grim side, the installation itself took ages, compared to the plug & play feel of CoLinux. Installing PostgreSQL on VMWare was quite straightforward, just as the the PostgreSQL documention goes. CoLinux: As I said, with CoLinux the installation itself goes very quickly. To get Linux running you need to download practically less than 20mb which include the distribution (Debian in my case) and the CoLinux setup. Configuring CoLinux took a bit longer than VMWare, yet, not long as I thought it would take. In fact, it can be very easy if you just follow the documention of CoLinux Wiki stuff, there are some very easy to follow tutorials there. Installing PostgreSQL on CoLinux proved a little more difficult (again, Debian), but I posted a quick tutorial that should smooth the process: http://www.colinux.org/wiki/index.php/PostgreSQL. Performance ----------- This was a totally subjective test (especially since one of the participants is in a beta stage), yet, that's what I tested and that's what I needed to know. To make the test as fair as possible, I did an exact dump of the same database. I ran the SQLs (around 10) in the same order on all of them and repeated the test several times. I also did an EXPLAIN on the queries to make sure all the databases work on the query the same way. It wasn't a full test though, I didn't test mass select load, nor inserts, nor work under heavy load, nor I tried different types of joins. All I did was to run some heavy (in execution time) queries. So you should take these "tests" just for what they are. That's what I got: The native window port performed poorly lagging 30%-50% behind the VMWare/CoLinux solutions in execution times, rather sad, but not unexpected, I guess. CoLinux and VMWare give AROUND the same results, yet CoLinux did give slightly better performance (I'd say 5%-10%) but with such slight improvement and inconsistency I wouldn't count it as much. Conclusion ---------- With all that said, VMWare is badly suited for running a database, while CoLinux can be run as a service (didn't try it yet though), VMWare always sits there, it is slow to go up, slow to go down and generally feels like a system hog. I'll go on with CoLinux for now and hope it will act as good as it looks. http://www.vmware.com/ http://www.colinux.org/ Thanks to Bryan and Matthew for their advices regarding the emulations. Regards, Vitaly Belman ICQ: 1912453 AIM: VitalyB1984 MSN: tmdagent@hotmail.com Yahoo!: VitalyBe
Vitaly Belman <vitalib@012.net.il> writes: > With all that said, VMWare is badly suited for running a database, > while CoLinux can be run as a service (didn't try it yet though), > VMWare always sits there, it is slow to go up, slow to go down and > generally feels like a system hog. Uhm, it sounds like you're using VMWare Workstation? VMWare has a range of different versions including some that are specifically targeted towards server situations. I think they had the idea that hosting companies would run hundreds of virtual machines on a server and provide their hosting clients with a virtual machine to play with. That said, I'm curious why the emulated servers performed better than the Native Windows port. My first thought is that they probably aren't syncing every write to disk so effectively they're defeating the fsyncs, allowing the host OS to buffer disk writes. I would be curious to see better stats on things like a pgbench run which would give some idea of the context switch efficiency, and a large select or update, which would give some idea of the i/o throughput. Really there's no excuse for the Windows port to be slower than an emulator. Barring effects like the disk caching I mentioned, it should far outpace the emulators. -- greg
Using VMware myself quite extensively, I wonder what the disk configuration was that you created for the VM. Where the disks preallocated and did you make sure that they are contiguous on the NTFS filesystem? Did you install the VMware tools in the guest operating system? What did you use to measure the "performance"? Jan On 6/1/2004 6:56 PM, Vitaly Belman wrote: > Hello pgsql-performance, > > I was using the native windows PostgreSQL 7.5Dev and was adviced by > several people to use an emulated PostgreSQL instead, as it is just > a beta. > > Well, I give it a whirl and tried both commercial VMWare and the > freeweare open-source CoLinux, both work under Windows and both > emulate Linux, that's a quick review of my experience with them, may > someone in need learn from it. > > This might be not the best place for such a post, but since the > subject was brought up here, I'll post it here as well. If someone > thinks it should be posted somewhere else, let me know. > > Installation & Configuration > ---------------------------- > > VMWare: > > On the bright side, the installation went quite smoothly, VMWare > configured all the network stuff by itself and I had no trouble > using the net right away. On the grim side, the installation itself > took ages, compared to the plug & play feel of CoLinux. > > Installing PostgreSQL on VMWare was quite straightforward, just as > the the PostgreSQL documention goes. > > CoLinux: > > As I said, with CoLinux the installation itself goes very quickly. > To get Linux running you need to download practically less than 20mb > which include the distribution (Debian in my case) and the CoLinux > setup. Configuring CoLinux took a bit longer than VMWare, yet, not > long as I thought it would take. In fact, it can be very easy if you > just follow the documention of CoLinux Wiki stuff, there are some > very easy to follow tutorials there. > > Installing PostgreSQL on CoLinux proved a little more difficult > (again, Debian), but I posted a quick tutorial that should smooth > the process: http://www.colinux.org/wiki/index.php/PostgreSQL. > > Performance > ----------- > > This was a totally subjective test (especially since one of the > participants is in a beta stage), yet, that's what I tested and that's > what I needed to know. > > To make the test as fair as possible, I did an exact dump of the > same database. I ran the SQLs (around 10) in the same order on all > of them and repeated the test several times. I also did an EXPLAIN > on the queries to make sure all the databases work on the query the > same way. It wasn't a full test though, I didn't test mass select > load, nor inserts, nor work under heavy load, nor I tried different > types of joins. All I did was to run some heavy (in execution time) > queries. So you should take these "tests" just for what they are. > > That's what I got: > > The native window port performed poorly lagging > 30%-50% behind the VMWare/CoLinux solutions in execution times, > rather sad, but not unexpected, I guess. > > CoLinux and VMWare give AROUND the same results, yet CoLinux did > give slightly better performance (I'd say 5%-10%) but with such > slight improvement and inconsistency I wouldn't count it as much. > > Conclusion > ---------- > > With all that said, VMWare is badly suited for running a database, > while CoLinux can be run as a service (didn't try it yet though), > VMWare always sits there, it is slow to go up, slow to go down and > generally feels like a system hog. > > I'll go on with CoLinux for now and hope it will act as good as it > looks. > > http://www.vmware.com/ > http://www.colinux.org/ > > Thanks to Bryan and Matthew for their advices regarding the emulations. > > Regards, > Vitaly Belman > > ICQ: 1912453 > AIM: VitalyB1984 > MSN: tmdagent@hotmail.com > Yahoo!: VitalyBe > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Greg Stark <gsstark@mit.edu> writes:
> That said, I'm curious why the emulated servers performed better than the
> Native Windows port. My first thought is that they probably aren't syncing
> every write to disk so effectively they're defeating the fsyncs, allowing the
> host OS to buffer disk writes.
It would be fairly easy to check this by repeating the comparisons with
fsync = off in postgresql.conf. A performance number that doesn't
change much would be a smoking gun ;-).
The native port hasn't had any performance testing done on it yet, and
I wouldn't be surprised to hear of a gotcha or two. Perhaps with the
recent schedule change there will be some time for performance tuning
before we go beta.
regards, tom lane
I have colinux running on a Fedora Core 1 image. I have the rhdb 3 (or PostgreSQL RedHat Edition 3) on it running. Here are tests with fsync on and off: FSYNC OFF FSYNC ON RUN 136.9 142.0 124.5 149.1 1 122.1 126.7 140.1 169.7 2 125.7 148.7 147.4 180.4 3 103.3 136.7 136.8 166.3 4 126.5 146.1 152.3 187.9 5 114.4 133.3 144.8 176.7 6 124.0 146.5 143.3 175.0 7 121.7 166.8 147.8 180.5 8 127.3 151.8 146.7 180.0 9 124.6 143.0 137.2 167.5 10 -------------------------------------- 122.7 144.2 142.1 173.3 AVG I hope those numbers' formatting come through all right. This computer is an AMD Athlon 900MHz with 448MB Ram running XP Pro SP1 This is using Colinux 0.60 (not the recently released 0.61) and 96MB of RAM allocated to linux. The computer was idle but it was running Putty, Excel and Task Manager during the process. (I prefer to use Putty to SSH into the virtual computer than to run the fltk console) It occurs to me that the fsync may be performed to the linux filesystem, but this filesystem is merely a file on the windows drive. Would Windows cache this file? It's 2GB in size, so if it did, it would only be able to cache part of it. I'd like to run a more difficult test personally. It seems like this test goes too fast to be very useful. If someone would like me to try something more specific, e-mail me right away and I'll do it. I must leave my office at 4:15 EDT and will not return until Friday, although I can do another test on my home computer Thursday. Matthew Nuzum | Makers of "Elite Content Management System" www.followers.net | View samples of Elite CMS in action matt@followers.net | http://www.followers.net/portfolio/ > -----Original Message----- > From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] On Behalf Of Tom Lane > Sent: Wednesday, June 02, 2004 11:25 AM > To: Greg Stark > Cc: Vitaly Belman; pgsql-performance@postgresql.org; Bryan Encina; Matthew > Nuzum > Subject: Re: [PERFORM] PostgreSQL on VMWare vs Windows vs CoLinux > > Greg Stark <gsstark@mit.edu> writes: > > That said, I'm curious why the emulated servers performed better than > the > > Native Windows port. My first thought is that they probably aren't > syncing > > every write to disk so effectively they're defeating the fsyncs, > allowing the > > host OS to buffer disk writes. > > It would be fairly easy to check this by repeating the comparisons with > fsync = off in postgresql.conf. A performance number that doesn't > change much would be a smoking gun ;-). > > The native port hasn't had any performance testing done on it yet, and > I wouldn't be surprised to hear of a gotcha or two. Perhaps with the > recent schedule change there will be some time for performance tuning > before we go beta. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Hi All, I think it would actually be interesting to see the performance of the Cygwin version for these same benchmarks, then we'vecovered all ways to run PostgreSQL on Windows systems. (I expect though that performance of Cygwin-PostgreSQL will improveconsiderably when an updated version is released that uses Cygwin native IPC instead of the ipc-daemon.) regards, --Tim
Greg Stark wrote:
>That said, I'm curious why the emulated servers performed better than the
>Native Windows port. My first thought is that they probably aren't syncing
>every write to disk so effectively they're defeating the fsyncs, allowing the
>host OS to buffer disk writes.
>
>
I havn't tested it, and it's certanly possible. However, please bear in
mind that it is also possible that it just gives better performance.
The reason this may be possible is that the emulation layer gets the CPU
(and other resources) from the OS in bulk, and decides on it's own how
to allocate it to the various processes running within the emulation.
Inparticular, this "on it's own" is done using the stock Linux kernel.
As Postgresql works sufficiently better on Linux than on Windows, this
yields better performance.
Again - speculation only. Someone should defenitely make sure that no
caching takes place where it shouldn't.
As a side note, I have had a chance to talk to Dan Aloni (coLinux
maintainer) about running PostgreSQL on coLinux. He said that he knows
that this particular use is high on people's priority list, but he feels
it is totally unsafe to run a production database on alpha grade
software. Then again, free software projects being what they are, this
is usually what a maintainer would say.
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/
Shachar Shemesh wrote: > Greg Stark wrote: > > >That said, I'm curious why the emulated servers performed better than the > >Native Windows port. My first thought is that they probably aren't > syncing > >every write to disk so effectively they're defeating the fsyncs, allowing > the > >host OS to buffer disk writes. > > > > > I havn't tested it, and it's certanly possible. However, please bear in > mind that it is also possible that it just gives better performance. > > The reason this may be possible is that the emulation layer gets the CPU > (and other resources) from the OS in bulk, and decides on it's own how > to allocate it to the various processes running within the emulation. > Inparticular, this "on it's own" is done using the stock Linux kernel. > As Postgresql works sufficiently better on Linux than on Windows, this > yields better performance. 'better' does not mean 'faster'. Win32 has a pretty decent journaling filesytem (ntfs) and a good I/O subsystem which includes IPC. Process management is poor compared to newer linux kernels but this is unimportant except in extreme cases. Right now the win32 native does not sync() (but does fsync()). So, the performance is somewhere between fsync = off and fsync = on (probably much closer to fsync = on). It is reasonable to assume that the win32 port will outperform the unix versions at many tasks (at the expense of safety) until the new sync() code is put in. If tested on the same source base, 40-60% differences can only be coming from the I/O subsystem. There are other factors which aren't clear from this exchange like what version of gcc, etc. Merlin
On 2 Jun 2004 at 16:45, Merlin Moncure wrote: > > 'better' does not mean 'faster'. Win32 has a pretty decent journaling > filesytem (ntfs) and a good I/O subsystem which includes IPC. Process > management is poor compared to newer linux kernels but this is > unimportant except in extreme cases. Right now the win32 native does > not sync() (but does fsync()). So, the performance is somewhere between > fsync = off and fsync = on (probably much closer to fsync = on). It is > reasonable to assume that the win32 port will outperform the unix > versions at many tasks (at the expense of safety) until the new sync() > code is put in. > > If tested on the same source base, 40-60% differences can only be coming > from the I/O subsystem. There are other factors which aren't clear from > this exchange like what version of gcc, etc. > Hmm, interesting. I've been running the Win32 port for a couple of weeks now. Using the same database as a Linux 2.6 system. Same processor and memory but different disks. Linux system has 10K rpm SCSI disks Windows has 7200 rpm serial ATA disks. When a lot of IO is involved the performance differences are very mixed as I would expect. Sometimes Windows wins, sometimes Linux. BUT, very consistently, when NO IO is involved then the Win32 port is always around 20% slower than Linux. In cases where the EXPLAIN ANALYZE results are different I have disregarded. In all the cases that the EXPLAIN ANALYZE results are the same and no IO is involved the Win32 port is slower. Currently I am putting this down to the build/gcc differences. I can't see why there should be this difference otherwise. (memory management??) Regards, Gary.
"Matthew Nuzum" <matt@followers.net> writes: > I have colinux running on a Fedora Core 1 image. I have the rhdb 3 (or > PostgreSQL RedHat Edition 3) on it running. Here are tests with fsync on > and off: > FSYNC OFF FSYNC ON RUN > 136.9 142.0 124.5 149.1 1 > 122.1 126.7 140.1 169.7 2 > 125.7 148.7 147.4 180.4 3 > 103.3 136.7 136.8 166.3 4 > 126.5 146.1 152.3 187.9 5 > 114.4 133.3 144.8 176.7 6 > 124.0 146.5 143.3 175.0 7 > 121.7 166.8 147.8 180.5 8 > 127.3 151.8 146.7 180.0 9 > 124.6 143.0 137.2 167.5 10 > -------------------------------------- > 122.7 144.2 142.1 173.3 AVG > > I hope those numbers' formatting come through all right. No, they didn't. You used tabs? Are they four space tabs or 8 space tabs? I assume 4 space tabs, but then what is the meaning of the four columns? You have two columns for each fsync setting? One's under Windows and one's under Vmware? Which is which? > It occurs to me that the fsync may be performed to the linux filesystem, but > this filesystem is merely a file on the windows drive. Would Windows cache > this file? It's 2GB in size, so if it did, it would only be able to cache > part of it. Well VMWare certainly doesn't know that the linux process called fsync. For all it knows the Linux kernel just schedule the i/o because it felt it was time. So the question is how does VMWare alway handle i/o normally. Does it always handle i/o from the Guest OS synchronously or does it buffer it via the Host OS's i/o system. I'm actually not sure which it does, it could be doing something strange. But does seem most likely that it lets Windows buffer the writes, or does so itself. It might also depend on whether you're using raw disks or a virtual disk file. Undoable disks would throw another wrench in the works entirely. Note that "caching" isn't really the question. It doesn't have to cache the entire 2GB file or even very much of it. It just has to store the block that linux wants to write and report success to linux without waiting for the disk to report success. Linux will then think the file is sync'd to disk and allow postgres to continue with the next transaction without actually waiting for the physical disk to spin around to the right place and the head to seek and perform the write. -- greg
On Wed, 2004-06-02 at 17:39, Greg Stark wrote: > "Matthew Nuzum" <matt@followers.net> writes: > > > I have colinux running on a Fedora Core 1 image. I have the rhdb 3 (or > > PostgreSQL RedHat Edition 3) on it running. Here are tests with fsync on > > and off: > > FSYNC OFF FSYNC ON RUN > > 136.9 142.0 124.5 149.1 1 > > 122.1 126.7 140.1 169.7 2 > > 125.7 148.7 147.4 180.4 3 > > 103.3 136.7 136.8 166.3 4 > > 126.5 146.1 152.3 187.9 5 > > 114.4 133.3 144.8 176.7 6 > > 124.0 146.5 143.3 175.0 7 > > 121.7 166.8 147.8 180.5 8 > > 127.3 151.8 146.7 180.0 9 > > 124.6 143.0 137.2 167.5 10 > > -------------------------------------- > > 122.7 144.2 142.1 173.3 AVG > > > > I hope those numbers' formatting come through all right. > > No, they didn't. You used tabs? Are they four space tabs or 8 space tabs? > I assume 4 space tabs, but then what is the meaning of the four columns? > You have two columns for each fsync setting? One's under Windows and one's > under Vmware? Which is which? > Sorry that wasn't clear. The pgbench program puts out two numbers, can't remember what they are, I think one number included the time to make the connection. Therefore, the first two columns represent the two values presented from pgbench with FSYNC off. The second two columns are those same to figures but with FSYNC ON. The 5th column is the run. I did 10 runs and included the output of all runs so that incase anything significant could be gleaned from the details, the data would be there. The executive summary is this: Tom was curious if colinux might be deceiving the applications that expect the fsync to occur. He suspected that pgbench run with and without fsync enabled might reveal something. Therefore: FSYNC ON: 142.1 FSYNC OFF: 122.7 Having FSYNC off seems to yield faster results. I'd like some input on a more demanding test though, because these tests run so quickly I can't help but be suspicious of their accuracy. When there are two OSs involved, it seems like the momentary activity of a background process could skew these results. > > It occurs to me that the fsync may be performed to the linux filesystem, but > > this filesystem is merely a file on the windows drive. Would Windows cache > > this file? It's 2GB in size, so if it did, it would only be able to cache > > part of it. > > Well VMWare certainly doesn't know that the linux process called fsync. For > all it knows the Linux kernel just schedule the i/o because it felt it was > time. > > So the question is how does VMWare alway handle i/o normally. Does it always > handle i/o from the Guest OS synchronously or does it buffer it via the > Host OS's i/o system. We probably will never know what the internal workings of VMWare are like because it is a closed source program. I'm not slighting them, I have purchased a license of VMWare and use it for my software testing. However, colinux is an open source project and we can easily find out how they handle this. I have little interest in this as I use this merely as a tool to speed up my application development and do not run any critical services what-so-ever. > > I'm actually not sure which it does, it could be doing something strange. But > does seem most likely that it lets Windows buffer the writes, or does so > itself. It might also depend on whether you're using raw disks or a virtual > disk file. Undoable disks would throw another wrench in the works entirely. In these tests I'm using a virtual disk file. This is a 2GB file on the hard drive that linux sees as a disk partition. Colinux does not support undoable disks in the way that vmware does. Their wiky site does not mention anything tricky being done to force disk writes to actually be written; the implication therefore is that it leaves the i/o completely at the discretion of XP. Also note that XP Pro and 2000 Pro both offer different caching options for the user to choose so unless it does something to actually force a write the answer is probably "who knows." > > Note that "caching" isn't really the question. It doesn't have to cache the > entire 2GB file or even very much of it. It just has to store the block that > linux wants to write and report success to linux without waiting for the disk > to report success. Linux will then think the file is sync'd to disk and allow > postgres to continue with the next transaction without actually waiting for > the physical disk to spin around to the right place and the head to seek and > perform the write. That's interesting to know. I wondered about that. So, my summary is this: If you develop applications in windows that run in linux and you need a testing platform you may like colinux a lot because of the following: * It's purchase price is 0 * It's seems to be capable of running any (or at least many) distribution based on 2.4 kernel * It appears to run much faster than VMWare (maybe because it has far fewer features, including the ability to run X apps unless you use VNC or a separate X server) * When idle it causes no apparent load on the CPU. This is the best part in my opinion. VMWare causes a drag on the system even when idle and especially when active. With colinux you can simply minimize the console or run it as a service and forget about it until you need it. You may prefer VMWare if you need: * GUI setup tools * Full PC hardware support (sound, fb, etc) * Undoable disk support * Painless install (colinux isn't hard but it's not "shrink wrap" quality yet) * Need special kernel features. Colinux uses its own kernel which has been ported to windows. OK, this horse is dead... anyone else want to kick it? Just kidding... If you want me to test something for you, I'd be happy to. Be specific and give me the details and I'll run it through the wringer on Friday. Oh, and don't ask me to download anything big please. 25 of us share a 56k modem there so bandwidth is limited. Hope this is helpful to someone, have a nice day. -- Matthew Nuzum <matt@followers.net> Followers.net, Inc.
Matthew Nuzum <matt@followers.net> writes:
> I'd like some input on a more demanding test though, because these tests
> run so quickly I can't help but be suspicious of their accuracy.
So increase the number of transactions tested (-t switch to pgbench).
Be aware also that you really want -s (database size scale factor) to
exceed -c (number of concurrent clients) for meaningful results.
regards, tom lane
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> ... Right now the win32 native does
> not sync() (but does fsync()). So, the performance is somewhere between
> fsync = off and fsync = on (probably much closer to fsync = on). It is
> reasonable to assume that the win32 port will outperform the unix
> versions at many tasks (at the expense of safety) until the new sync()
> code is put in.
... which was three days ago. Why are we still speculating?
regards, tom lane