Re: Postgres performance Linux vs FreeBSD

On 2/21/07, Jacek Zaręba <asthma@polok.pl> wrote:
> Hello, I've set up 2 identical machines, hp server 1ghz p3,
> 768mb ram, 18gb scsi3 drive. On the first one I've installed
> Debian/GNU 4.0 Linux, on the second FreeBSD 6.2. On both
> machines I've installed Postgresql 8.2.3 from sources.
> Now the point :)) According to my tests postgres on Linux
> box run much faster then on FreeBSD, here are my results:
>
> *** setting up **************************
> creeate table foo as select x from generate_series(1,2500000) x;
> vacuum foo;
> checkpoint;
> \timing
>
> *****************************************
>
> *** BSD *********************************
> actual=# select count(*) from foo;
>    count
> ---------
>   2500000
> (1 row)
>
> Time: 1756.455 ms
> actual=# explain analyze select count(*) from foo;
>                                                        QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------
>   Aggregate  (cost=34554.20..34554.21 rows=1 width=0) (actual
> time=12116.841..12116.843 rows=1 loops=1)
>     ->  Seq Scan on foo  (cost=0.00..28304.20 rows=2500000 width=0)
> (actual time=9.276..6435.890 rows=2500000 loops=1)
>   Total runtime: 12116.989 ms
> (3 rows)
>
> Time: 12117.803 ms
>
> ******************************************
>
>
> *** LIN **********************************
> actual=# select count(*) from foo;
>    count
> ---------
>   2500000
> (1 row)
>
> Time: 1362,193 ms
> actual=# EXPLAIN ANALYZE
> actual-# select count(*) from foo;
>                                                        QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------
>   Aggregate  (cost=34554.20..34554.21 rows=1 width=0) (actual
> time=4737.243..4737.244 rows=1 loops=1)
>     ->  Seq Scan on foo  (cost=0.00..28304.20 rows=2500000 width=0)
> (actual time=0.058..2585.170 rows=2500000 loops=1)
>   Total runtime: 4737.363 ms
> (3 rows)
>
> Time: 4738,367 ms
> actual=#
> ******************************************
>
> Just a word about FS i've used:
> BSD:
> /dev/da0s1g on /usr/local/pgsql (ufs, local, noatime, soft-updates)
>
> LIN:
> /dev/sda7 on /usr/local/pgsql type xfs (rw,noatime)
>
>
> My question is simple :) what's wrong with the FreeBSD BOX??
> What's the rule for computing gettimeofday() time ??

'explain analyze' can't be reliably used to compare results from
different operating systems...1756ms v. 1362ms is a win for linux but
not a blowout and there might be other things going on...

merlin