Shoaib Burq (VPAC) schrieb:
I think you should post the SQL-Statement and EXPLAIN ANALYSE - Output here to get a usefull awnser.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 [EMAIL PROTECTED])
(EXPLAIN ANALYSE SELECT * FROM x WHERE ---)
Daniel
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
