Performance on 7.4 vs 7.2?

[Pablo S]

Hi there Pg admins,

I have 2 systems, one OLD,
(linux 2.4 running postgresql-7.2.1-5 with a perl web db on
Apache/1.3.23 mod_perl 1.26 and dbi 1.37 with peristent db connections
via apache:dbi. )

The other system is NEW
(running the same web app on linux 2.6 postgresql-7.4.2-1 w/
Apache/2.0.49 & mod_perl/1.99_12 & apache:dbi. fedora core 2)

Both systems have almost identical hardware, and have had the same
tweaks made to pg - at least all I can rack out of my brain –

their SYSV shared mem increased to 128mb
shared_buffers = 15200
sort_mem = 32168
effective_cache _size = 4000

I have one page where I run the same query 155 times. (Whew! I know
it sucks, but it is what raises the question.) On the old server, it
loads in 1.5 seconds. On the new server, with the same data, it takes
6. I am trying to find out why.

It is your standard join query:

explain select ts.id, ts.start, to_hours(ts.hou rs) as hours,
ts.emp_id, ts.finish, ts.so_id, ts.lowdown, so.customer, so.summary,
so.status, ts.status as ts_status from ts inner join so on
so.id=ts.so_id where emp_id=? and start between ? and ?

My table is ~10MB, so I am expecting pg should be able to get it all
buffered in shared mem. If I EXPLAIN it they are both doing the same
thing – using an index on each table.

7.2 –
=#
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..12. 04 rows=1 width=176)
-> Index Scan using ts_empid_start_ idx on ts (cost=0.00..6.0 2
rows=1 width=76)
-> Index Scan using so_id_key on so (cost=0.00..6.0 1 rows=1
width=100)
7.4 –
Nested Loop (cost=0.00..12. 09 rows=2 width=172)
-> Index Scan using ts_empid_start_ idx on ts (cost=0.00..6.0 2
rows=1 width=76)
Index Cond: ((emp_id = 1) AND ("start" >= '2004-01-01
00:00:00'::time stamp with time zone) AND ("start" <= '2004-01-04
00:00:00'::time stamp with time zone))
-> Index Scan using so_id_key on so (cost=0.00..6.0 1 rows=1
width=100)
Index Cond: (so.id = "outer".so_ id)

Watching processes, pg is doing a lot of work on the new box apache
also seems to be logging more cycles but it is 70% Pg. So I am
looking for experts to hopefully point me in the right direction to
why this may be happening/things to check/debugging/ways to split up
the task. One thing I did notice, my old (faster) server has pg
processes using 37mb of RAM, each. On the new server, they are only
using 8. The table is larger than 8MB but I thought I read that table
caching is done in IPC cache mem, not process mem.

Thank you for your interest!! Any comments welcome.

[Gaetano Mendola]

Pablo S wrote:

Hi there Pg admins,

I have 2 systems, one OLD,
(linux 2.4 running postgresql-7.2.1-5 with a perl web db on
Apache/1.3.23 mod_perl 1.26 and dbi 1.37 with peristent db connections
via apache:dbi. )

The other system is NEW
(running the same web app on linux 2.6 postgresql-7.4.2-1 w/
Apache/2.0.49 & mod_perl/1.99_12 & apache:dbi. fedora core 2)

Both systems have almost identical hardware, and have had the same
tweaks made to pg - at least all I can rack out of my brain –

their SYSV shared mem increased to 128mb
shared_buffers = 15200
sort_mem = 32168
effective_cache _size = 4000

I don't know what changes from 7.2 -> 7.4 but effective_cache _size
is too little IMO, or at least incongruous with sort_mem.
Try to rise that parameter to something more realistic.

Regards
Gaetano Mendola

[jake johnson]

pa*********@yah oo.com (Pablo S) wrote in message news:<76******* *************** ****@posting.go ogle.com>...

Hi there Pg admins,

I have 2 systems, one OLD,
(linux 2.4 running postgresql-7.2.1-5 with a perl web db on
Apache/1.3.23 mod_perl 1.26 and dbi 1.37 with peristent db connections
via apache:dbi. )

The other system is NEW
(running the same web app on linux 2.6 postgresql-7.4.2-1 w/
Apache/2.0.49 & mod_perl/1.99_12 & apache:dbi. fedora core 2)

[...snip...]
I don't think the (general) performance difference between 7.2 & 7.4
is major at all and if anything is improved in 7.4. Some hardware
information would be interesting to know because kernel 2.6 is more
geared towards mulitiprocessor systems than 2.4. You do know about
mod_perl's beta status for use with Apache 2, right? I'd be looking
there (unless you're really able to narrow the issue down to PG's
query speed). Provide more PG diagnostics info and perhaps this can
be considered.