Skip site navigation (1) Skip section navigation (2)

degenerate performance on one server of 3

From: Erik Aronesty <erik(at)q32(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: degenerate performance on one server of 3
Date: 2009-05-31 09:59:35
Message-ID: (view raw or flat)
Lists: pgsql-performance
I have 3 servers, all with identical databases, and each performing
very differently for the same queries.

www3 is my fastest, www2 is the worst, and www1 is in the middle...
even though www2 has more ram, faster CPU and faster drives (by far),
and is running a newer version of postgres. I have been reluctant to
post because I know it's something that I'm doing wrong in the
settings or something that I should be able to figure out.

Last year at this time www2 was the fastest... in fact, I bought the
machine to be my "primary" server, and it performed as such.... with
the striped volumes and higher RAM, it outpaced the other 2 in every
query.  It has, over time, "degenerated" to being so slow it
frequently has to be taken out of the load-balance set.   The only
major changes to the sever have been "yum update (or ports upgrade)"
to the newer releases ... over time.

The query planner "knows" about the problem, but I'm not sure *why*
there's a difference... since the tables all have the same data ...
loaded from a dump nightly.

The planner shows a different number of "rows" even though the items
table has 22680 rows in all 3 instances.  I ran a vacuum analyze just
before these runs hoping to get them all into a similar "clean" state.

The difference is outlined below, with the query planner output from a
table-scan query that greatly exaggerates the differences in
performance, along with some info about the configuration and platform

QUERY: explain select count(*) from items where name like '%a%'

www3: psql (PostgreSQL) 8.1.14
www3: Linux www3 #1 SMP Thu May 15 00:02:29 EDT 2008
x86_64 x86_64 x86_64 GNU/Linux

www3: Mem:   1996288k total,  1537576k used,   458712k free,    23124k buffers
www3: Swap:        0k total,        0k used,        0k free,  1383208k cached

www3: shared_buffers = 10000                    # min 16 or
max_connections*2, 8KB each

www3:                             QUERY PLAN
www3: ------------------------------------------------------------------
www3:  Aggregate  (cost=3910.07..3910.08 rows=1 width=0)
www3:    ->  Seq Scan on items  (cost=0.00..3853.39 rows=22671 width=0)
www3:          Filter: (name ~~ '%a%'::text)
www3: (3 rows)

www1: psql (PostgreSQL) 8.1.17
www1: Linux www1 #1 SMP Thu Dec 18 18:59:49 EST 2008
x86_64 x86_64 x86_64 GNU/Linux

www1: Mem:   1019376k total,   973064k used,    46312k free,    27084k buffers
www1: Swap:  1959888k total,    17656k used,  1942232k free,   769776k cached

www1: shared_buffers = 6000                      # min 16 or
max_connections*2, 8KB each

www1:                             QUERY PLAN
www1: ------------------------------------------------------------------
www1:  Aggregate  (cost=5206.20..5206.21 rows=1 width=0)
www1:    ->  Seq Scan on items  (cost=0.00..5149.50 rows=22680 width=0)
www1:          Filter: (name ~~ '%a%'::text)
www1: (3 rows)

www2: psql (PostgreSQL) 8.2.13
www2: FreeBSD www2 6.3-RELEASE-p7 FreeBSD 6.3-RELEASE-p7 #0: Sun Dec
21 03:24:04 UTC 2008
root(at)amd64-builder(dot)daemonology(dot)net:/usr/obj/usr/src/sys/SMP  amd64

www2: Mem: 57M Active, 1078M Inact, 284M Wired, 88M Cache, 213M Buf, 10M Free
www2: Swap: 4065M Total, 144K Used, 4065M Free

www2: shared_buffers = 360MB                   # min 128kB or

www2:                             QUERY PLAN
www2: ------------------------------------------------------------------
www2:  Aggregate  (cost=17659.45..17659.46 rows=1 width=0)
www2:    ->  Seq Scan on items  (cost=0.00..17652.24 rows=2886 width=0)
www2:          Filter: (name ~~ '%a%'::text)
www2: (3 rows)


pgsql-performance by date

Next:From: Tom LaneDate: 2009-05-31 14:52:43
Subject: Re: degenerate performance on one server of 3
Previous:From: Scott MarloweDate: 2009-05-31 05:20:42
Subject: Re: Scalability in postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group