I have a question about PostgreSQL performance differences.
I am developing a web application on a MAC OS X system and the web application has to be deployed on a FreeBSD server. On a page of the system there is a ajax controlled data entry field. In this field you can enter a city name and when you enter two characters or more the system starts to look for the cities in the database and presents a drop-down of cities conforming these 2 (or more) characters at the beginning of the string.
All this seems to work well until I deployed it on FreeBSD servers. The first deployment was alright but the second deployment shows a huge performance difference.
This is a list of test results:
system1 proc: Intel Core 2 Duo 3.06 GHz, mem: 8GB. : OS: OS X 10.6.8, 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:33:36 PDT 2011; root:xnu-1504.15.3~1/RELEASE_I386 i386 DB: PostgreSQL 8.4.5 on i386-apple-darwin10.5.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit Tests system1: query parameter time in ms 01 'de%' 909 02 'de%' 886 03 'den%' 132 04 'den %' 115 05 'den h%' 115 06 'den ha%' 117 07 'den haa%' 95 08 'den haag%' 100 host: system1 guest: parallels virtual machine, proc: 2 cpu, mem: 1 GB. system2 OS: 8.2-RELEASE FreeBSD 8.2-RELEASE #0: Thu Feb 17 02:41:51 UTC 2011 firstname.lastname@example.org:/usr/obj/usr/src/sys/GENERIC amd64 DB: PostgreSQL 8.4.7 on amd64-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit Tests system2: query parameter time in ms 01 'de%' 1178 02 'de%' 857 03 'den%' 298 04 'den %' 233 05 'den h%' 134 06 'den ha%' 132 07 'den haa%' 132 08 'den haag%' 136 host: system1 guest: parallels virtual machine, proc: 2 cpu, mem: 1 GB. system3 OS: 8.3-RELEASE FreeBSD 8.3-RELEASE #0: Mon Apr 9 21:23:18 UTC 2012 email@example.com:/usr/obj/usr/src/sys/GENERIC amd64 DB: PostgreSQL 8.4.11 on amd64-portbld-freebsd8.3, compiled by GCC cc (GCC) 4.2.2 20070831 prerelease [FreeBSD], 64-bit Tests system3: query parameter time in ms 01 'de%' 7096 02 'de%' 7012 03 'den%' 6228 04 'den %' 6237 05 'den h%' 6145 06 'den ha%' 5640 07 'den haa%' 5512 08 'den haag%' 5561
The parameters mimic the way data is entered in the ajax application. These results are from queries directly to the database through pgAdmin3, so through the application. I have put the query here because I don't think that should be relevant. The databases are identical and the same query is used on all three database instances.
Now I can understand the performance difference between system1: OS X system, bare hardware and system2: virtual machine running FreeBSD.
What I don't understand is the huge performance difference between system2 and system3 which are both vm's running under the same host. The test were done with each vm running individually.
Does anyone have a clue why this could be happening?