X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 36FD1D1B8E2 for ; Tue, 26 Aug 2003 14:48:47 -0300 (ADT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 76994-03 for ; Tue, 26 Aug 2003 14:48:15 -0300 (ADT) Received: from mail2.dbitech.ca (radius.wavefire.com [64.141.13.252]) by svr1.postgresql.org (Postfix) with SMTP id EF1F4D1B8C8 for ; Tue, 26 Aug 2003 14:48:13 -0300 (ADT) Received: (qmail 15693 invoked from network); 26 Aug 2003 18:10:19 -0000 Received: from dbitech.wavefire.com (HELO dbitech) (darcy@64.141.15.253) by radius.wavefire.com with SMTP; 26 Aug 2003 18:10:19 -0000 Content-Type: text/plain; charset="iso-8859-1" From: Darcy Buskermolen Organization: Wavefire Technologies Corp. To: Jeff , pgsql-performance@postgresql.org Subject: Re: Sun vs a P2. Interesting results. Date: Tue, 26 Aug 2003 10:48:10 -0700 User-Agent: KMail/1.4.3 References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200308261048.10817.darcy@wavefire.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/338 X-Sequence-Number: 3154 I spoke with my SUN admin, and this is what he had to say about what you ar= e=20 seeing. Sun gear is known to show a lower than Intel performance on light loads, re= run=20 your test with 100 concurrent users (queries) and see what happens. Also h= e=20 recommends installing a 64bit version of Solaris, the 32bit robs a lot of= =20 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=3D/usr --mandir=3D/usr/share/man > --infodir=3D/u > sr/share/info --enable-shared --enable-threads=3Dposix --disable-checking > --host=3Di > 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 =3D 2000 > sort_mem =3D 8192 > effective_cache_size =3D 32000 > tcpip_sockets =3D 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=3D'm' > and country_v and country =3D 'br' > and dob_v =3D 't' > and dob >=3D 'now'::timestamptz - '29 years'::interval > and dob <=3D '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=3D2484.52..2484.57 rows=3D20 width=3D67) (actual > time=3D377.32..377.41 row > s=3D20 loops=3D1) > -> Sort (cost=3D2484.27..2484.74 rows=3D186 width=3D67) (actual > time=3D377.02..377. > 21 rows=3D121 loops=3D1) > Sort Key: dob > -> Seq Scan on userprofile (cost=3D0.00..2477.28 rows=3D186 > width=3D67) ( > actual time=3D0.15..350.93 rows=3D1783 loops=3D1) > Filter: (gender_v AND (gender =3D 'm'::character varying) = AND > count > ry_v AND (country =3D 'br'::character varying) AND (dob_v =3D true) AND (= dob > > >=3D '197 > > 4-08-26 07:13:15.903437-04'::timestamp with time zone) AND (dob <=3D > '1985-08-26 0 > 7:13:15.903437-04'::timestamp with time zone)) > Total runtime: 378.21 msec > (6 rows) > > Sun: > Limit (cost=3D2521.19..2521.24 rows=3D20 width=3D67) (actual > time=3D1041.14..1041.20 r > ows=3D20 loops=3D1) > -> Sort (cost=3D2520.94..2521.39 rows=3D178 width=3D67) (actual > time=3D1040.96..104 > 1.08 rows=3D121 loops=3D1) > Sort Key: dob > -> Seq Scan on userprofile (cost=3D0.00..2514.28 rows=3D178 > width=3D67) ( > actual time=3D0.37..1014.50 rows=3D1783 loops=3D1) > Filter: (gender_v AND (gender =3D 'm'::character varying) = AND > count > ry_v AND (country =3D 'br'::character varying) AND (dob_v =3D true) AND (= dob > > >=3D '197 > > 4-08-26 08:21:52.158181-04'::timestamp with time zone) AND (dob <=3D > '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). --=20 Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com