Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig DDR PC3200
Hello.OS: Gentoo 2006.0 with gentoo's hardened kernelVersion: I haven't checked. Im guessing 8.0.8 (latest stable on all systems) or 8.1.4 which is the latest package.I'm still gonna try to run with smart array 5i. How can i find out that my performance with that is crappy? Without ripping down my systems, and using software raid? Kjell ToreOn 7/28/06, Claus Guttesen <[EMAIL PROTECTED]> wrote: > As I have understood, there is alot of tuning using both postgres.conf and> analyzing queries to make the values of postgres.conf fit my needs, system> and hardware. This is where I need some help. I have looked into > postgres.conf , and seen the tunings. But I'm still not sure what I should> put into those variables (in postgres.conf) with my hardware.>> Any suggestions would be most appreciated!What OS is it running and what version is postgresql? regardsClaus-- "Be nice to people on your way up because you meet them on your way down."
Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig
Run bonnie++ version 1.03 and report results here. - Luke Sent from my GoodLink synchronized handheld (www.good.com) -Original Message- From: Kjell Tore Fossbakk [mailto:[EMAIL PROTECTED] Sent: Sunday, July 30, 2006 03:03 PM Eastern Standard Time To: Claus Guttesen Cc: pgsql-performance@postgresql.org Subject:Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig DDR PC3200 Hello. OS: Gentoo 2006.0 with gentoo's hardened kernel Version: I haven't checked. Im guessing 8.0.8 (latest stable on all systems) or 8.1.4 which is the latest package. I'm still gonna try to run with smart array 5i. How can i find out that my performance with that is crappy? Without ripping down my systems, and using software raid? Kjell Tore On 7/28/06, Claus Guttesen <[EMAIL PROTECTED]> wrote: > > > As I have understood, there is alot of tuning using both postgres.confand > > analyzing queries to make the values of postgres.conf fit my needs, > system > > and hardware. This is where I need some help. I have looked into > > postgres.conf , and seen the tunings. But I'm still not sure what I > should > > put into those variables (in postgres.conf) with my hardware. > > > > Any suggestions would be most appreciated! > > What OS is it running and what version is postgresql? > > regards > Claus > -- "Be nice to people on your way up because you meet them on your way down." ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig DDR PC3200
Okey!The thing is, im on vacation. So ill report in about 3 weeks time.. Sry guys.. :-)Kjell ToreOn 7/30/06, Luke Lonergan < [EMAIL PROTECTED]> wrote:Run bonnie++ version 1.03 and report results here. - LukeSent from my GoodLink synchronized handheld (www.good.com) -Original Message-From: Kjell Tore Fossbakk [mailto: [EMAIL PROTECTED]]Sent: Sunday, July 30, 2006 03:03 PM Eastern Standard TimeTo: Claus GuttesenCc: pgsql-performance@postgresql.orgSubject:Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig DDR PC3200Hello.OS: Gentoo 2006.0 with gentoo's hardened kernelVersion: I haven't checked. Im guessing 8.0.8 (latest stable on all systems)or 8.1.4 which is the latest package. I'm still gonna try to run with smart array 5i. How can i find out that myperformance with that is crappy? Without ripping down my systems, and usingsoftware raid?Kjell ToreOn 7/28/06, Claus Guttesen < [EMAIL PROTECTED]> wrote:>> > As I have understood, there is alot of tuning using both postgres.confand> > analyzing queries to make the values of postgres.conf fit my needs,> system> > and hardware. This is where I need some help. I have looked into> > postgres.conf , and seen the tunings. But I'm still not sure what I> should> > put into those variables (in postgres.conf) with my hardware.> >> > Any suggestions would be most appreciated!>> What OS is it running and what version is postgresql?>> regards> Claus> --"Be nice to people on your way up because you meet them on your way down."-- "Be nice to people on your way up because you meet them on your way down."
[PERFORM] sub select performance due to seq scans
I am testing a query what that has a sub-select. The query performance is very very poor as shown below due to the use of sequencial scans. The actual row count of both tables is also shown. It appears the row count shown by explain analyze does not match the actual count. Columns dstobj, srcobj & objectid are all indexed yet postgres insists on using seq scans. Vacuum analyze makes no difference. I am using 8.1.3 on linux. This is a very simple query with relatively small amount of data and the query is taking 101482 ms. Queries with sub-selects on both tables individually is very fast (8 ms). How do I prevent the use of seq scans? capsa=# explain analyze select name from capsa.flatomfilesysentry where objectid in ( select dstobj from capsa.flatommemberrelation where srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409');
QUERY PLAN - Nested Loop IN Join (cost=0.00..1386.45 rows=5809 width=14) (actual time=2.933..101467.463 rows=5841 loops=1) Join Filter: ("outer".objectid = "inner".dstobj) -> Seq Scan on flatomfilesysentry (cost=0.00..368.09 rows=5809 width=30) (actual time=0.007..23.451 rows=5844 loops=1) -> Seq Scan on flatommemberrelation (cost=0.00..439.05 rows=5842 width=16) (actual time=0.007..11.790 rows=2922
loops=5844) Filter: (srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409'::capsa_sys.uuid) Total runtime: 101482.256 ms (6 rows) capsa=# select count(*) from capsa.flatommemberrelation ; count --- 11932 (1 row) capsa=# select count(*) from capsa.flatomfilesysentry ; count --- 5977
[PERFORM] How to increase performance?
Hello, My name is Hristo Markov. I am software developer. I am developing software systems (with C/C++ program language) that work on Windows operation system and uses ODBC driver and ACCESS database. I want to change database with PostgreSQL. The systems working without problems with PostgreSQL and ODBC, but the performance and speed of updating and reading of data are very low. I run the test program working on one single computer under Windows XP operating system and working with equal data (I use around 10 tables at the same time). The difference is only databases and ODBC drivers. The results from speed and performance of the test program are: Around 10 seconds under Access database.Around 40 seconds under PostgreSQL database. Please help me to increase speed and performance of PostgreSQL. /I am freshman in PostgreSQL and I thing that may be must set some settings / Thank you in advance, Sincerely,Hristo Markov Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.
[PERFORM] Strange behaviour
We are using a BI tool that generates some rather ugly queries. One of the ugly queries is taking much longer to return thin I think it should. The select expression when run alone returns in 2 seconds with 35k rows (http://www.bowmansystems.com/~richard/explain_select.analyze) The "where clause" when run alone returns 5200 rows in 10 seconds (http://www.bowmansystems.com/~richard/explain_where.analyze) However when I put to two together it takes much, much longer to run. (http://www.bowmansystems.com/~richard/full.analyze) Can anyone shed any light on what is going on here? Why does the optimizer choose such a slow plan in the combined query when the only real difference between the full query and the "where only" query is the number of rows in the result set on the "outside" of the "IN" clause? A few pertinent observations/facts below 1. The query is generated by a BI tool, I know it is ugly and stupid in many cases. However, please try to see the larger issue, that if the select and where portions are run separately they are both fast but together it is insanely slow. 2. The database has vacuumdb -f -z run on it nightly. 3. Modifications to the stock postgresql.conf: shared_buffers = 15000 work_mem = 131072 default_statistics_target = 100 4. Dual Dual core Opterons, 4 gigs of ram, 6 disk Serial ATA hardware RAID 10 running Postgres 8.03 compiled from source running on Debian stable. 5. The tables being queried are only 200 megs or so combined on disk, the whole DB is ~ 4 gigs SELECT sum(relpages*8/1024) AS size_M FROM pg_class; size_m 4178 Thanks! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Query 200x slower on server [PART 2]
See Query 200x slower on server [PART 1] before reading any
further
QUERY PLAN ON MY HOME SERVER
Sort (cost=1516.55..1516.59 rows=15 width=640) (actual
time=123.008..123.435 rows=1103 loops=1) Sort Key:
aanmaakdatum -> Subquery Scan producttabel
(cost=1515.39..1516.26 rows=15 width=640) (actual time=112.890..119.067
rows=1103 loops=1) ->
Unique (cost=1515.39..1516.11 rows=15 width=834) (actual
time=112.886..117.950 rows=1103
loops=1)
InitPlan
-> Index Scan using geg_winkel_pkey on geg_winkel
(cost=0.00..5.44 rows=1 width=4) (actual time=0.022..0.023 rows=1
loops=1)
Index Cond: (winkelid =
0)
-> Index Scan using geg_winkel_pkey on geg_winkel
(cost=0.00..5.44 rows=1 width=4) (actual time=0.004..0.005 rows=1
loops=1)
Index Cond: (winkelid =
0)
-> Group (cost=1504.51..1505.18 rows=15 width=834) (actual
time=112.880..115.682 rows=1136
loops=1)
-> Sort (cost=1504.51..1504.55 rows=15 width=834) (actual
time=112.874..113.255 rows=1137
loops=1)
Sort Key: p.productid, p.serienummer, p.artikelnaam, p.inkoopprijs,
p.vasteverkoopprijs, gegw.winkelid, gegw.winkelnaam, gegw.winkelnaamnl,
gegw.winkelnaamenkelvoud, gegw.winkelnaamenkelvoudnl, defg.genrenaam,
defg.genrenaamnl, p.
(..)
-> Hash Join (cost=925.74..1504.22 rows=15 width=834) (actual
time=34.143..107.937 rows=1137
loops=1)
Hash Cond: ("outer".leverancierid =
"inner".leverancierid)
-> Nested Loop (cost=924.29..1502.54 rows=15 width=829) (actual
time=34.041..105.706 rows=1137
loops=1)
-> Hash Join (cost=924.29..1399.67 rows=20 width=829) (actual
time=32.698..71.780 rows=3852
loops=1)
Hash Cond: ("outer".winkelid =
"inner".winkelid)
-> Hash Left Join (cost=918.33..1373.61 rows=3981 width=249)
(actual time=31.997..64.938 rows=3852
loops=1)
Hash Cond: ("outer".genreid =
"inner".genreid)
-> Hash Left Join (cost=917.14..1312.71 rows=3981 width=117)
(actual time=31.946..60.961 rows=3852
loops=1)
Hash Cond: ("outer"..onderwerpid)
-> Hash Left Join (cost=904.72..1240.57 rows=3981 width=117)
(actual time=31.104..56.264 rows=3852
loops=1)
Hash Cond: ("outer"..onderwerpid)
-> Merge Right Join (cost=890.28..1166.42 rows=3981 width=101)
(actual time=29.938..50.406 rows=3852
loops=1)
Merge Cond: ("outer".productid =
"inner".productid)
-> Index Scan using koppel_product_onderwerp_pkey on
koppel_product_onderwerp kpo (cost=0.00..216.34 rows=5983 width=8) (actual
time=0.011..8.537 rows=5965
loops=1)
-> Sort (cost=890.28..900.23 rows=3981 width=97) (actual
time=29.918..31.509 rows=3852
loops=1)
Sort Key:
p.productid
-> Seq Scan on product p (cost=0.00..652.24 rows=3981 width=97)
(actual time=0.012..18.012 rows=3819
loops=1)
Filter: (afdelingid =
1)
-> Hash (cost=12.75..12.75 rows=675 width=20) (actual
time=1.119..1.119 rows=675
loops=1)
-> Seq Scan on geg_onderwerp gego (cost=0.00..12.75 rows=675
width=20) (actual time=0.010..0.598 rows=675
loops=1)
-> Hash (cost=10.74..10.74 rows=674 width=8) (actual
time=0.822..0.822 rows=674
loops=1)
-> Seq Scan on koppel_onderwerp_genre kog (cost=0.00..10.74
rows=674 width=8) (actual time=0.010..0.423 rows=674
loops=1)
-> Hash (cost=1.15..1.15 rows=15 width=140) (actual
time=0.033..0.033 rows=15
loops=1)
-> Seq Scan on geg_genre defg (cost=0.00..1.15 rows=15 width=140)
(actual time=0.004..0.017 rows=15
loops=1)
-> Hash (cost=5.96..5.96 rows=1 width=584) (actual
time=0.682..0.682 rows=197
loops=1)
