pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Daniel Schuchardt <daniel_schuchardt@web.de>
To: pgsql-performance@postgresql.org
Subject: Re: two queries and dual cpu (perplexed)
Date: Thu, 21 Apr 2005 14:19:06 +0200
Message-ID: <d485nj$1sh6$1@news.hub.org> (raw)
In-Reply-To: <Pine.LNX.4.44.0504212109050.28107-100000@hp.vpac.org>
References: <Pine.LNX.4.44.0504212109050.28107-100000@hp.vpac.org>

Shoaib Burq (VPAC) schrieb:

>Hi everybody,
>
>One of our clients was using SQL-Server and decided to switch to
>PostgreSQL 8.0.1.
>
>Hardware: Dual processor  Intel(R) Xeon(TM) CPU 3.40GHz
>OS: Enterprise Linux with 2.6.9-5 SMP kernel
>Filesystem: ext3
>SHMMAX: $ cat  /proc/sys/kernel/shmmax
>6442450944 <--- beleive that's ~6.5 GB, total ram is 8GB
>Database: 15GB in size with a few tables with over 80 million rows.
>
>Here is a snippit from the output of 
>SELECT oid , relname, relpages, reltuples 
>        FROM pg_class ORDER BY relpages DESC;
>    oid    |             relname             | relpages |  reltuples  
>-----------+---------------------------------+----------+-------------
>     16996 | CurrentAusClimate               |   474551 | 8.06736e+07
>     16983 | ClimateChangeModel40            |   338252 | 5.31055e+07
> 157821816 | PK_CurrentAusClimate            |   265628 | 8.06736e+07
> 157835995 | idx_climateid                   |   176645 | 8.06736e+07
> 157835996 | idx_ausposnum                   |   176645 | 8.06736e+07
> 157835997 | idx_climatevalue 		     |   176645 | 8.06736e+07
> 157821808 | PK_ClimateModelChange_40        |   174858 | 5.31055e+07
> 157821788 | IX_iMonth001                    |   116280 | 5.31055e+07
> 157821787 | IX_ClimateId                    |   116280 | 5.31055e+07
> 157821786 | IX_AusPosNumber                 |   116280 | 5.31055e+07
>     17034 | NeighbourhoodTable              |    54312 | 1.00476e+07
> 157821854 | PK_NeighbourhoodTable           |    27552 | 1.00476e+07
> 157821801 | IX_NeighbourhoodId              |    22002 | 1.00476e+07
> 157821800 | IX_NAusPosNumber                |    22002 | 1.00476e+07
> 157821799 | IX_AusPosNumber006              |    22002 | 1.00476e+07
>[...]
>
>To test the performance of the database we ran one of the most demanding
>queries that exist with the following embarrassing results:
>
>Query Execution time on:
>SQL-Server (dual processor xeon)  3min 11sec
>PostgreSQL (SMP IBM Linux server) 5min 30sec
>
>Now I have not touch the $PGDATA/postgresql.conf (As I know very little 
>about memory tuning) Have run VACCUM & ANALYZE.
>
>The client understands that they may not match the performance for a
>single query as there is no multithreading. So they asked me to
>demonstrate the benefits of Postgresql's multiprocessing capabilities.
>
>To do that I modified the most demanding query to create a second query
>and ran them in parallel:
>
>$ time ./run_test1.sh
>$ cat ./run_test1.sh
>/usr/bin/time -p psql -f ./q1.sql ausclimate > q1.out 2>q1.time &
>/usr/bin/time -p psql -f ./q2.sql ausclimate > q2.out 2>q2.time
>
>and the time taken is *twice* that for the original. The modification was 
>minor. The queries do make use of both CPUs:
>
> 2388 postgres 16 0 79640 15m 11m R 80.9 0.2 5:05.81 postmaster
> 2389 postgres 16 0 79640 15m 11m R 66.2 0.2 5:04.25 postmaster
>
>But I can't understand why there's no performance improvement and infact
>there seems to be no benefit of multiprocessing.  Any ideas? I don't know
>enough about the locking procedures employed by postgres but one would
>think this shouldn't be and issue with read-only queries.
>
>Please don't hesitate to ask me for more info like, the query or the
>output of explain, or stats on memory usage. I just wanted to keep this 
>short and provide more info as the cogs start turning :-)
>
>Thanks & Regards
>Shoaib
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>  
>
I think you should post the SQL-Statement and EXPLAIN ANALYSE - Output 
here to get a usefull awnser.
(EXPLAIN ANALYSE SELECT * FROM x WHERE ---)

Daniel



view thread (18+ messages)  latest in thread

Message-ID: <d485nj$1sh6$1@news.hub.org>
Permalink:  ../d485nj$1sh6$1@news.hub.org/
Also on:    postgresql.org/message-id/d485nj$1sh6$1@news.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: daniel_schuchardt@web.de
  Subject: Re: two queries and dual cpu (perplexed)
  In-Reply-To: <d485nj$1sh6$1@news.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox