Re: [HACKERS] [PERFORM] insert performance for win32
On Fri, 2005-11-04 at 13:21 -0500, Bruce Momjian wrote: David Fetter wrote: On Fri, Nov 04, 2005 at 01:01:20PM -0500, Tom Lane wrote: I'm inclined to treat this as an outright bug, not just a minor performance issue, because it implies that a sufficiently long psql script would probably crash a Windows machine. Ouch. In light of this, are we *sure* what we've got a is a candidate for release? Good point. It is something we would fix in a minor release, so it doesn't seem worth doing another RC just for that. Will this be documented in the release notes? If we put unimplemented features in TODO, where do we list things we regard as bugs? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] 8.1 iss
SELECT v_barcode, count(v_barcode) FROM lead GROUP BY v_barcode HAVING count(*) 1; This is a pretty good example of the place where 8.1 seems to be quite broken. I understand that this query will want to do a full table scan (even through v_barcode is indexed). And the table is largish, at 34 million rows. In the 8.0 world, this took around 4 minutes. With 8.1beta3, this has run for 30 minutes (as I began to write this) and is still going strong. And it behaves differently than I'd expect. Top shows the postmaster process running the query as using up 99.9 percent of one CPU, while the i/o wait time never gets above 3%. vmstat shows the block out (bo) number quite high, 15 to 20 thousand, which also surprises me. block in is from 0 to about 2500. iostat shows 15,000 to 20,000 blocks written every 5 seconds, while it shows 0 blocks read. There is no other significant process running on the box. (Apache is running but is not being used here a 3:00a.m. on Sunday). This is a dual Opteron box with 16 Gb memory and a 3ware SATA raid runing 64bit SUSE. Something seems badly wrong. As I post this, the query is approaching an hour of run time. I've listed an explain of the query and my non-default conf parameters below. Please advise on anything I should change or try, or on any information I can provide that could help diagnose this. GroupAggregate (cost=9899282.83..10285434.26 rows=223858 width=15) Filter: (count(*) 1) - Sort (cost=9899282.83..9994841.31 rows=38223392 width=15) Sort Key: v_barcode - Seq Scan on lead (cost=0.00..1950947.92 rows=38223392 width=15) shared_buffers = 5 work_mem = 16384 maintenance_work_mem = 16384 max_fsm_pages = 10 max_fsm_relations = 5000 wal_buffers = 32 checkpoint_segments = 32 effective_cache_size = 5 default_statistics_target = 50 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
Hi, I am experiencing very long update queries and I want to know if it reasonable to expect them to perform better. The query below is running for more than 1.5 hours (5500 seconds) now, while the rest of the system does nothing (I don't even type or move a mouse...). - Is that to be expected? - Is 180-200 tps with ~ 9000 KB (see output iostat below) not low, given the fact that fsync is off? (Note: with bonnie++ I get write performance 50 MB/sec and read performace 70 MB/sec with 2000 read/write ops /sec? - Does anyone else have any experience with the 3Ware RAID controller (which is my suspect)? - Any good idea how to determine the real botleneck if this is not the performance I can expect? My hard- and software: - PostgreSQL 8.0.3 - Debian 3.1 (Sarge) AMD64 - Dual Opteron - 4GB RAM - 3ware Raid5 with 5 disks Pieces of my postgresql.conf (All other is default): shared_buffers = 7500 work_mem = 260096 fsync=false effective_cache_size = 32768 The query with explain (amount and orderbedrag_valuta are float8, ordernummer and ordernumber int4): explain update prototype.orders set amount = odbc.orders.orderbedrag_valuta from odbc.orders where ordernumber = odbc.orders.ordernummer; QUERY PLAN - Hash Join (cost=50994.74..230038.17 rows=1104379 width=466) Hash Cond: (outer.ordernumber = inner.ordernummer) - Seq Scan on orders (cost=0.00..105360.68 rows=3991868 width=455) - Hash (cost=48233.79..48233.79 rows=1104379 width=15) - Seq Scan on orders (cost=0.00..48233.79 rows=1104379 width=15) Sample output from iostat during query (about avarage): Device:tpskB_read/skB_wrtn/skB_readkB_wrtn hdc 0.00 0.00 0.00 0 0 sda 0.00 0.00 0.00 0 0 sdb 187.1323.76 8764.36 24 8852 -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 8.1 iss
PostgreSQL [EMAIL PROTECTED] writes: This is a pretty good example of the place where 8.1 seems to be quite broken. That's a bit of a large claim on the basis of one data point. Did you remember to re-ANALYZE after loading the table into the new database? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
Joost Kraaijeveld [EMAIL PROTECTED] writes: I am experiencing very long update queries and I want to know if it reasonable to expect them to perform better. Does that table have any triggers that would fire on the update? regards, tom lane ---(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] Performance problem with pg8.0
Hello,
I have some strange performance problems with quering a table.It has
5282864, rows and contains the following columns : id
,no,id_words,position,senpos and sentence all are integer non null.
Index on :
* no
* no,id_words
* id_words
* senpos, sentence, no)
* d=primary key
select count(1) from words_in_text takes 9 seconds to compleet.
The query 'select * from words_in_text' takes a verry long time to
return the first record (more that 2 minutes) why?
Also the following query behaves strange.
select * from words_in_text where no 100 order by no;
explain shows that pg is using sequence scan. When i turn of sequence
scan, index scan is used and is faster. I have a 'Explain verbose
analyze' of this query is at the end of the mail.
The number of estimated rows is wrong, so I did 'set statistics 1000' on
column no. After this the estimated number of rows was ok, but pg still
was using seq scan.
Can anyone explain why pg is using sequence and not index scan?
The computer is a dell desktop with 768Mb ram. Database on the same
machine. I have analyze and vacuum all tables.
Database is 8.0.
Thanks
Jeroen
With enable_seqscan=true
{SORT
:startup_cost 138632.19
:total_cost 139441.07
:plan_rows 323552
:plan_width 24
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname id
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname no
:ressortgroupref 1
:resorigtbl 1677903
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 23
:restypmod -1
:resname id_words
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 23
:restypmod -1
:resname position
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname senpos
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 23
:restypmod -1
:resname sentence
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
)
:qual
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 104880.80
:plan_rows 323552
:plan_width 24
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname id
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname no
:ressortgroupref 1
:resorigtbl 1677903
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
Re: [PERFORM] 8.1 iss
PostgreSQL [EMAIL PROTECTED] writes: ... As I post this, the query is approaching an hour of run time. I've listed an explain of the query and my non-default conf parameters below. Please advise on anything I should change or try, or on any information I can provide that could help diagnose this. GroupAggregate (cost=9899282.83..10285434.26 rows=223858 width=15) Filter: (count(*) 1) - Sort (cost=9899282.83..9994841.31 rows=38223392 width=15) Sort Key: v_barcode - Seq Scan on lead (cost=0.00..1950947.92 rows=38223392 width=15) shared_buffers = 5 work_mem = 16384 ... It sounds to me like it's doing a large on-disk sort. Increasing work_mem should improve the efficiency. If you increase it enough it might even be able to do it in memory, but probably not. The shared_buffers is excessive but if you're using the default 8kB block sizes then it 400MB of shared pages on a 16GB machine ought not cause problems. It might still be worth trying lowering this to 10,000 or so. Is this a custom build from postgresql.org sources? RPM build? Or is it a BSD ports or Gentoo build with unusual options? Perhaps posting actual vmstat and iostat output might help if someone catches something you didn't see? -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
On Sun, 2005-11-06 at 12:17 -0500, Tom Lane wrote: Does that table have any triggers that would fire on the update? Alas, no trigger, constrainst, foreign keys, indixes (have I forgotten something?) All queries are slow. E.g (after vacuum): select objectid from prototype.orders Explain analyse (with PgAdmin): Seq Scan on orders (cost=0.00..58211.79 rows=1104379 width=40) (actual time=441.971..3252.698 rows=1104379 loops=1) Total runtime: 5049.467 ms Actual execution time: 82163 MS (without getting the data) Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 8.1 iss
Greg, Increasing memory actually slows down the current sort performance. We're working on a fix for this now in bizgres. Luke -- Sent from my BlackBerry Wireless Device -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: PostgreSQL [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org pgsql-performance@postgresql.org Sent: Sun Nov 06 14:24:00 2005 Subject: Re: [PERFORM] 8.1 iss PostgreSQL [EMAIL PROTECTED] writes: ... As I post this, the query is approaching an hour of run time. I've listed an explain of the query and my non-default conf parameters below. Please advise on anything I should change or try, or on any information I can provide that could help diagnose this. GroupAggregate (cost=9899282.83..10285434.26 rows=223858 width=15) Filter: (count(*) 1) - Sort (cost=9899282.83..9994841.31 rows=38223392 width=15) Sort Key: v_barcode - Seq Scan on lead (cost=0.00..1950947.92 rows=38223392 width=15) shared_buffers = 5 work_mem = 16384 ... It sounds to me like it's doing a large on-disk sort. Increasing work_mem should improve the efficiency. If you increase it enough it might even be able to do it in memory, but probably not. The shared_buffers is excessive but if you're using the default 8kB block sizes then it 400MB of shared pages on a 16GB machine ought not cause problems. It might still be worth trying lowering this to 10,000 or so. Is this a custom build from postgresql.org sources? RPM build? Or is it a BSD ports or Gentoo build with unusual options? Perhaps posting actual vmstat and iostat output might help if someone catches something you didn't see? -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
Hi Tom, On Sun, 2005-11-06 at 15:26 -0500, Tom Lane wrote: I'm confused --- where's the 82sec figure coming from, exactly? From actually executing the query. From PgAdmin: -- Executing query: select objectid from prototype.orders Total query runtime: 78918 ms. Data retrieval runtime: 188822 ms. 1104379 rows retrieved. We've heard reports of performance issues in PgAdmin with large result sets ... if you do the same query in psql, what happens? [EMAIL PROTECTED]:~/postgresql$ time psql muntdev -c select objectid from prototype.orders output.txt real0m5.554s user0m1.121s sys 0m0.470s Now *I* am confused. What does PgAdmin do more than giving the query to the database? (BTW: I have repeated both measurements and the numbers above were all from the last measurement I did and are about average) -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
Now *I* am confused. What does PgAdmin do more than giving the query to the database? It builds it into the data grid GUI object. Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
On Mon, 2005-11-07 at 12:37 +0800, Christopher Kings-Lynne wrote: Now *I* am confused. What does PgAdmin do more than giving the query to the database? It builds it into the data grid GUI object. Is that not the difference between the total query runtime and the data retrieval runtime (see below)? -- Executing query: select objectid from prototype.orders Total query runtime: 78918 ms. Data retrieval runtime: 188822 ms. 1104379 rows retrieved. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
Hi Christopher, On Mon, 2005-11-07 at 12:37 +0800, Christopher Kings-Lynne wrote: Now *I* am confused. What does PgAdmin do more than giving the query to the database? It builds it into the data grid GUI object. But my initial question was about a query that does not produce data at all (well, a response from the server saying it is finished). I broke that query off after several hours. I am now running the query from my initial question with psql (now for 1 hour, in a transaction, fsyn off). Some statistics : uptime: 06:35:55 up 9:47, 6 users, load average: 7.08, 7.21, 6.08 iostat -x -k 1 (this output appears to be representative): avg-cpu: %user %nice%sys %iowait %idle 1.000.000.50 98.510.00 Device: sda sdb rrqm/s 0.000.00 wrqm/s 14.00 611.00 r/s 0.001.00 w/s 3.00201.00 rsec/s 0.0032.00 wsec/s 136.00 6680.00 rkB/s 0.0016.00 wkB/s 68.00 3340.00 avgrq-sz45.33 33.23 avgqu-sz0.00145.67 await 0.67767.19 svctm 0.674.97 %util 0.20100.30 -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Used Memory
It affect my application since the database server starts to slow down. Hence a very slow in return of functions. Any more ideas about this everyone? Please. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alex Turner Sent: Friday, October 21, 2005 3:42 PM To: Jon Brisbin Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory [snip] to the second processor in my dual Xeon eServer) has got me to the point that the perpetually high memory usage doesn't affect my application server. I'm curious - how does the high memory usage affect your application server? Alex I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html
Re: [PERFORM] 8.1 iss
On Sun, 6 Nov 2005, PostgreSQL wrote: SELECT v_barcode, count(v_barcode) FROM lead GROUP BY v_barcode HAVING count(*) 1; This is a dual Opteron box with 16 Gb memory and a 3ware SATA raid runing 64bit SUSE. Something seems badly wrong. GroupAggregate (cost=9899282.83..10285434.26 rows=223858 width=15) Filter: (count(*) 1) - Sort (cost=9899282.83..9994841.31 rows=38223392 width=15) Sort Key: v_barcode - Seq Scan on lead (cost=0.00..1950947.92 rows=38223392 width=15) What do the plan look like in 8.0? Since it's so much faster I assume you get a different plan. shared_buffers = 5 work_mem = 16384 maintenance_work_mem = 16384 max_fsm_pages = 10 max_fsm_relations = 5000 wal_buffers = 32 checkpoint_segments = 32 effective_cache_size = 5 default_statistics_target = 50 The effective_cache_size is way too low, only 390M and you have a machine with 16G. Try bumping it to 100 (which means almost 8G, how nice it would be to be able to write 8G instead...). It could be set even higher but it's hard for me to know what else your memory is used for. I don't know if this setting will affect this very query, but it should have a positive effect on a lot of queries. work_mem also seems low, but it's hard to suggest a good value on it without knowing more about how your database is used. -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
FW: [PERFORM] Used Memory
Here are the configuration of our database server: port = 5432 max_connections = 300 superuser_reserved_connections = 10 authentication_timeout = 60 shared_buffers = 48000 sort_mem = 32168 sync = false Do you think this is enough? Or can you recommend a better configuration for my server? The server is also running PHP and Apache but wer'e not using it extensively. For development purpose only. The database slow down is occurring most of the time (when the memory free is low) I don't think it has something to do with vacuum. We only have a full server vacuum once a day. -Original Message- From: Mark Kirkwood [mailto:[EMAIL PROTECTED] Sent: Monday, October 24, 2005 3:14 AM To: Christian Paul B. Cosinas Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory I just noticed that as long as the free memory in the first row (which is 55036 as of now) became low, the slower is the response of the database server. Also, how about posting your postgresql.conf (or just the non-default parameters) to this list? Some other stuff that could be relevant: - Is the machine just a database server, or does it run (say) Apache + Php? - When the slowdown is noticed, does this coincide with certain activities - e.g, backup , daily maintenance, data load(!) etc. regards Mark I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html Nope, not me either. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Temporary Table
Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html
