Re: Postgres performance Linux vs FreeBSD - Mailing list pgsql-performance

In response to "Jacek Zaręba" <asthma@polok.pl>:

> 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 ??

I can't speak to the gettimeofday() question, but I have a slew of comments
regarding other parts of this email.

The first thing that I expect most people will comment on is your testing
strategy.  You don't get a lot of details, but it seems as if you ran
1 query on each server, 1 run on each.  If you actually did more tests,
you should provide that information, otherwise, people will criticize your
testing strategy instead of looking at the problem.

The other side to this is that you haven't shown enough information about
your alleged problem to even start to investigate it.

--
Bill Moran
Collaborative Fusion Inc.