Re: Sun vs a P2. Interesting results. - Mailing list pgsql-performance
From | Darcy Buskermolen |
---|---|
Subject | Re: Sun vs a P2. Interesting results. |
Date | |
Msg-id | 200308261048.10817.darcy@wavefire.com Whole thread Raw |
In response to | Sun vs a P2. Interesting results. (Jeff <threshar@torgo.978.org>) |
List | pgsql-performance |
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
pgsql-performance by date: