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

Performance issue - 2 linux machines, identical configs, different performance

From: "Mark Steben" <msteben(at)autorevenue(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Cc: "'Rich Garabedian'" <richg(at)autorevenue(dot)com>,"'Mark Steben'" <msteben(at)autorevenue(dot)com>
Subject: Performance issue - 2 linux machines, identical configs, different performance
Date: 2009-06-17 17:12:51
Message-ID: 20090617171307.4464D634C88@mail.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-performance
Hi, sorry about the blank post yesterday - let's try again

 

We have two machines.  Both running Linux Redhat, both running postgres
8.2.5.

Both have nearly identical 125 GB databases.  In fact we use PITR Recovery
to 

Replicate from one to the other.  The machine we replicate to runs a query
with

About 10 inner and left joins about 5 times slower than the original machine

I run an explain on both.  Machine1 (original) planner favors hash joins
about 3 to 1

Over nested loop joins.  Machine2 (replicated) uses only nested loop joins -
no hash at all.

 

A few details - I can always provide more

 

 MACHINE1 - original:

    TOTAL RAW MEMORY - 30 GB

    TOTAL SHARED MEMORY (shmmax value) - 4 GB

     

     Database configs

          SHARED_BUFFERS ------------- 1525 MB

          MAX_PREPARED_TRANSACTIONS - 5

          WORK_MEM - --------------------    300 MB

          MAINTENANCE_WORK_MEM - 512 MB 

          MAX_FSM_PAGES -------------- 3,000,000

          CHECKPOINT_SEGMENTS -----         64

          WAL_BUFFERS ---------------------        768

           EFFECTIVE_CACHE_SIZE ----       2 GB

          Planner method configs all turned on by default, including
enable_hashjoin

  

   MACHINE2 - we run 2 postgres instances.  Port 5433 runs continuous PITR
recoveries

       Port 5432 receives the 'latest and greatest' database when port 5433
finishes a recovery

          TOTAL RAW MEMORY - 16 GB (this is a VMWARE setup on a netapp)

          TOTAL SHARED MEMORY (shmmax value) - 4 GB

 

         Database configs - port 5432 instance

           SHARED_BUFFERS ------------ 1500 MB

           MAX_PREPARED_TRANSACTIONS - 1 (we don't run prepared transactions
here)

          WORK_MEM - --------------------    300 MB

          MAINTENANCE_WORK_MEM - 100 MB  (don't think this comes into play
in this conversation)

          MAX_FSM_PAGES -------------- 1,000,000

          CHECKPOINT_SEGMENTS -----         32

          WAL_BUFFERS ---------------------        768

          EFFECTIVE_CACHE_SIZE ----       2 GB

          Planner method configs all turned on by default, including
enable_hashjoin

 

            Database configs - port 5433 instance

           SHARED_BUFFERS ------------ 1500 MB

           MAX_PREPARED_TRANSACTIONS - 1 (we don't run prepared transactions
here)

          WORK_MEM - --------------------    250 MB

          MAINTENANCE_WORK_MEM - 100 MB  (don't think this comes into play
in this conversation)

          MAX_FSM_PAGES -------------- 1,000,000

          CHECKPOINT_SEGMENTS -----         32

          WAL_BUFFERS ---------------------        768

          EFFECTIVE_CACHE_SIZE ----       2 GB

          Planner method configs all turned on by default, including
enable_hashjoin

 

   Now some size details about the 11 tables involved in the join

         All join fields are indexed unless otherwise noted and are of type
integer unless otherwise noted

 

        TABLE1  -------------398 pages

        TABLE2  --------  5,014 pages INNER JOIN on TABLE1

        TABLE3  ------- 34,729 pages INNER JOIN on TABLE2 

        TABLE4 ----1,828,000 pages INNER JOIN on TABLE2

        TABLE5 ----1,838,000 pages INNER JOIN on TABLE4

        TABLE6 ------ 122,500 pages INNER JOIN on TABLE4         

        TABLE7 -----------  621 pages INNER JOIN on TABLE6

        TABLE8  ----------     4 pages INNER JOIN on TABLE7 (TABLE7 column
not indexed)

        TABLE9 -----------     2 pages INNER JOIN on TABLE8 (TABLE8 column
not indexed)

        TABLE10 ---------   13 pages LEFT JOIN on TABLE6  (columns on both
tables text, neither column indexed)

        TABLE11 -1,976,430 pages LEFT JOIN on TABLE5. AND explicit join on
TABLE6

           The WHERE clause filters out primary key values from TABLE1 to 1
value and a 1 month range of 

               Indexed dates from TABLE4.

 

 So, my guess is the disparity of performance (40 seconds vs 180 seconds)
has to do with MACHINE2 not

 Availing itself of hash joins which by my understanding is much faster.

 

Any help / insight appreciated.  Thank you

 

  

               

 

 

 

Mark Steben│Database Administrator│ 

@utoRevenue-R- "Join the Revenue-tion"
95 Ashley Ave. West Springfield, MA., 01089 
413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)

@utoRevenue is a registered trademark and a division of Dominion Enterprises

 

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2009-06-17 17:31:56
Subject: Re: Performance issue - 2 linux machines, identical configs, different performance
Previous:From: Kevin GrittnerDate: 2009-06-17 16:42:54
Subject: Re: performance with query

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