Re: Sun vs a P2. Interesting results.

I spoke with my SUN admin, and this is what he had to say about what you are
seeing.

Sun gear is known to show a lower than Intel performance on light loads, rerun
your test with 100 concurrent users (queries) and see what happens. Also he
recommends installing a 64bit version of Solaris, the 32bit robs a lot of
performance as well.

On Tuesday 26 August 2003 05:34, Jeff wrote:
> Here's an interesting situation, and I think it may be just that Sun
> stinks.
>
> I was recently given the go ahead to switch from Informix to Postgres on
> one of our properties. (I had dozens of performance comparisons showing
> how slow Informix was compared to it and my boss seeing me struggle trying
> to make it run fast while Postgres, nearly out of the box, was simply
> spanking it.).
>
>
> Well, in order to facilitate things we were going to run pg on a 4 cpu
> (ultrasparc ii 400Mhz) sun with 4gb of memory (also the current informix
> box. It isn't very loaded). Now I know FreeBSD/Linux is preferred (and
> where I do a lot of development and testing). But check this out for
> interesting results.
>
> The Hardware:
> Machine A: 4 CPU Sun Ultrasparc II 400Mhz, 4GB mem, 20GB RAID5, Solaris 8
> (32 bit mode)
>
> Machine B: 2 CPU Pentium II, 450Mhz, 512MB mem, 18GB RAID0 (2 old scsi
> disks) Linux 2.4.18 (Stock redhat 8.0)
>
> The software: PG 7.3.4 compiled myself. (Reading specs from
> /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.9/2.95.3/specs gcc version 2.95.3
> 20010315 (release) (The solaris 8 box has no compilers, could this be the
> issue?) and (Reading specs from
> /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
> Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
> --infodir=/u
> sr/share/info --enable-shared --enable-threads=posix --disable-checking
> --host=i
> 386-redhat-linux --with-system-zlib --enable-__cxa_atexit
> Thread model: posix
> gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7))
>
> Ok. Maybe the compiler (I'll try installing a newer gcc for sun later
> today).
>
> The PG.conf:
> shared_buffers = 2000
> sort_mem = 8192
> effective_cache_size = 32000
> tcpip_sockets = true
>
> The Schema:
>
> userprofile:
>
> userkey | character varying(128) |
> displayname | character varying(128) |
> displayname_v | boolean | default 'f'
> name | character varying(128) |
> name_v | boolean | default 'f'
> email | character varying(128) |
> email_v | boolean | default 'f'
> gender | character varying(1) |
> gender_v | boolean | default 'f'
> country | character varying(64) |
> country_v | boolean | default 'f'
> zip | character varying(10) |
> zip_v | boolean | default 'f'
> city | character varying(128) |
> city_v | boolean | default 'f'
> state | character varying(10) |
> state_v | boolean | default 'f'
> lang | character varying(2) |
> lang_v | boolean | default 'f'
> url | character varying(255) |
> url_v | boolean | default 'f'
> phone | character varying(64) |
> phone_v | boolean | default 'f'
> phonemobile | character varying(64) |
> phonemobile_v | boolean | default 'f'
> phonefax | character varying(64) |
> phonefax_v | boolean | default 'f'
> dob | timestamp with time zone |
> dob_v | boolean | default 'f'
> interests_v | boolean | default 'f'
> description | character varying(255) |
> description2 | character varying(255) |
> description_v | boolean | default 'f'
>
> (Yes, I kknow it isn't good - a lot of it is because it is the same schema
> I had to use on informix. Convienantly you cannot do much with a textblob
> on infomrix, so I have to use big varchar's, but that is a fiffernt
> story).
>
> The magic query:
>
> select userkey, dob, email, gender, country from imuserprofile
> where gender_v and gender='m'
> and country_v and country = 'br'
> and dob_v = 't'
> and dob >= 'now'::timestamptz - '29 years'::interval
> and dob <= 'now'::timestamptz - '18 years'::interval
> order by dob asc
> limit 20
> offset 100
>
> (Page 5 of male brazillians, 18-29)
>
> Now the P2 runs this in about 0.3 seconds, and hte sun box runs it in 1
> second.
> Here's the explain analyze's on each:
>
> P2:
> Limit (cost=2484.52..2484.57 rows=20 width=67) (actual
> time=377.32..377.41 row
> s=20 loops=1)
> -> Sort (cost=2484.27..2484.74 rows=186 width=67) (actual
> time=377.02..377.
> 21 rows=121 loops=1)
> Sort Key: dob
> -> Seq Scan on userprofile (cost=0.00..2477.28 rows=186
> width=67) (
> actual time=0.15..350.93 rows=1783 loops=1)
> Filter: (gender_v AND (gender = 'm'::character varying) AND
> count
> ry_v AND (country = 'br'::character varying) AND (dob_v = true) AND (dob
>
> >= '197
>
> 4-08-26 07:13:15.903437-04'::timestamp with time zone) AND (dob <=
> '1985-08-26 0
> 7:13:15.903437-04'::timestamp with time zone))
> Total runtime: 378.21 msec
> (6 rows)
>
> Sun:
> Limit (cost=2521.19..2521.24 rows=20 width=67) (actual
> time=1041.14..1041.20 r
> ows=20 loops=1)
> -> Sort (cost=2520.94..2521.39 rows=178 width=67) (actual
> time=1040.96..104
> 1.08 rows=121 loops=1)
> Sort Key: dob
> -> Seq Scan on userprofile (cost=0.00..2514.28 rows=178
> width=67) (
> actual time=0.37..1014.50 rows=1783 loops=1)
> Filter: (gender_v AND (gender = 'm'::character varying) AND
> count
> ry_v AND (country = 'br'::character varying) AND (dob_v = true) AND (dob
>
> >= '197
>
> 4-08-26 08:21:52.158181-04'::timestamp with time zone) AND (dob <=
> '1985-08-26 0
> 8:21:52.158181-04'::timestamp with time zone))
> Total runtime: 1042.54 msec
> (6 rows)
>
> They are loaded with the exact same dataset - 53k rows, ~10MB
> Notice the estimates are roughly the same, but the execution time is
> different.
>
> I don't think it is the IO system, since 10MB will be cached by the OS and
> iostat reports no activity on the disks (when running the query many
> times over and over and in parellel). it is a simple query..
>
> Could it just be that the sun sucks? (And for the record - same schema,
> nearly same query (modified for datetime syntax) on informix runs in 3
> seconds).

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com