Обсуждение: Postgres performance Linux vs FreeBSD
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 ??
Thanks for any advices :))
..and have a nice day!!
J.
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
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.
Le mercredi 21 février 2007 10:57, Jacek Zaręba a écrit : > Now the point :)) According to my tests postgres on Linux > box run much faster then on FreeBSD, here are my results: You may want to compare some specific benchmark, as in bench with you application queries. For this, you can consider Tsung and pgfouine softwares. http://tsung.erlang-projects.org/ http://pgfouine.projects.postgresql.org/tsung.html Regards, -- Dimitri Fontaine
Jacek Zarêba 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: > With respect to 'select count(*) from ...' being slower on FreeBSD, there are a number of things to try to make FreeBSD faster for this sort of query. Two I'm currently using are: - setting sysctl vfs.read_max to 16 or 32 - rebuilding the relevant filesystem with 32K blocks and 4K frags I have two (almost) identical systems - one running Gentoo, one running FreeBSD 6.2. With the indicated changes the FreeBSD system performs pretty much the same as the Gentoo one. With respect to the 'explain analyze' times, FreeBSD has a more accurate and more expensive gettimeofday call - which hammers its 'explain analyze' times compared to Linux. Cheers Mark
On 2007-02-21, Mark Kirkwood <markir@paradise.net.nz> wrote: > With respect to 'select count(*) from ...' being slower on FreeBSD, > there are a number of things to try to make FreeBSD faster for this sort > of query. Two I'm currently using are: > > - setting sysctl vfs.read_max to 16 or 32 > - rebuilding the relevant filesystem with 32K blocks and 4K frags Be aware that increasing the filesystem block size above 16k is _known_ to tickle kernel bugs - there is a workaround involving increasing BKVASIZE, but this isn't a config parameter and therefore you have to patch the sources. The symptom to look for is: largescale filesystem deadlocks with many processes (especially syncer) blocked in "nbufkv" state. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services