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 differences. QUERY: explain select count(*) from items where name like '%a%' www3: psql (PostgreSQL) 8.1.14 www3: Linux www3 2.6.23.17-88.fc7 #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) www3: www1: psql (PostgreSQL) 8.1.17 www1: Linux www1 2.6.26.8-57.fc8 #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) www1: 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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx:/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 max_connections*16kB 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) www2: -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
