mod_perl - dbi - DBD:Pg performance test, old vs new

Discussion in 'Perl' started by Pablo S, Sep 1, 2004.

  1. Pablo S

    Pablo S Guest

    Hi there mod_perl/Pg folks,

    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, which i am demoing
    (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. (this one 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 –

    <pg stuff>
    their SYSV shared mem increased to 128mb
    shared_buffers = 15200
    sort_mem = 32168
    effective_cache_size = 4000
    </pg stuff>

    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.

    I think this has something to do with either DBI or Apache or
    mod_perl(is that all?), as it is a pretty simple loop, DBhandle is
    there the whole time, i just prepare a new sth and execute it, read
    back the rows, rinse repeat. It is the same code.

    I know pg performance tuning better than mod_perl, so heres that info:

    It is your standard join query:

    explain select ts.id, ts.start, to_hours(ts.hours) 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.02
    rows=1 width=76)
    -> Index Scan using so_id_key on so (cost=0.00..6.01 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.02
    rows=1 width=76)
    Index Cond: ((emp_id = 1) AND ("start" >= '2004-01-01
    00:00:00'::timestamp with time zone) AND ("start" <= '2004-01-04
    00:00:00'::timestamp with time zone))
    -> Index Scan using so_id_key on so (cost=0.00..6.01 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. 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.
     
    Pablo S, Sep 1, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andrew Crook

    DBD::CSV questions and is there a DBD::XML?

    Andrew Crook, Sep 2, 2003, in forum: Perl Misc
    Replies:
    2
    Views:
    187
    Andrew Crook
    Sep 11, 2003
  2. Pablo S
    Replies:
    1
    Views:
    323
    Tore Aursand
    Sep 1, 2004
  3. Replies:
    2
    Views:
    478
    Big and Blue
    Jan 25, 2006
  4. Jim Cochrane
    Replies:
    0
    Views:
    132
    Jim Cochrane
    Aug 25, 2007
  5. david
    Replies:
    4
    Views:
    113
    Ted Zlatanov
    Jan 19, 2010
Loading...

Share This Page