X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9B2A653724 for ; Thu, 21 Apr 2005 09:24:26 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61945-07 for ; Thu, 21 Apr 2005 12:24:21 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 20F85536E9 for ; Thu, 21 Apr 2005 09:24:18 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id D544230952; Thu, 21 Apr 2005 14:23:11 +0200 (MET DST) From: Daniel Schuchardt X-Newsgroups: pgsql.performance Subject: Re: two queries and dual cpu (perplexed) Date: Thu, 21 Apr 2005 14:19:06 +0200 Organization: Hub.Org Networking Services Lines: 89 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: de-DE, de, en-us, en In-Reply-To: To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/638 X-Sequence-Number: 12035 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