Re: [PERFORM] Process Time X200
On Fri, Mar 10, 2006 at 08:11:44AM +0100, NbForYou wrote: As you can see the query isn't useful anymore because of the processtime. Please Also notice that both systems use a different query plan. Also on the webhost we have a loop of 162409 (403 rows * 403 rows). Both systems also use a different postgresql version. But I cannot believe that the performance difference between 1 version could be this big regarding self outer join queries! What versions are both servers? I'd guess that the webhost is using 7.3 or earlier and you're using 7.4 or later. I created a table like yours, populated it with test data, and ran your query on several versions of PostgreSQL. I saw the same horrible plan on 7.3 and the same good plan on later versions. The 7.4 Release Notes do mention improvements in query planning; apparently one of those improvements is making the difference. -- Michael Fuhr ---(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] Process Time X200
Hey Michael, you sure know your stuff! Versions: PostgreSQL 7.3.9-RH running on the webhost. PostgreSQL 8.0.3 running on my homeserver. So the only solution is to ask my webhost to upgrade its postgresql? The question is will he do that? After all a license fee is required for commercial use. And running a webhosting service is a commercial use. thanks for replying and going through the effort of creating the database and populating it. Nick - Original Message - From: Michael Fuhr [EMAIL PROTECTED] To: NbForYou [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Friday, March 10, 2006 9:59 AM Subject: Re: [PERFORM] Process Time X200 On Fri, Mar 10, 2006 at 08:11:44AM +0100, NbForYou wrote: As you can see the query isn't useful anymore because of the processtime. Please Also notice that both systems use a different query plan. Also on the webhost we have a loop of 162409 (403 rows * 403 rows). Both systems also use a different postgresql version. But I cannot believe that the performance difference between 1 version could be this big regarding self outer join queries! What versions are both servers? I'd guess that the webhost is using 7.3 or earlier and you're using 7.4 or later. I created a table like yours, populated it with test data, and ran your query on several versions of PostgreSQL. I saw the same horrible plan on 7.3 and the same good plan on later versions. The 7.4 Release Notes do mention improvements in query planning; apparently one of those improvements is making the difference. -- Michael Fuhr ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Hanging queries on dual CPU windows
Is it possible to get a stack trace from the stuck process? I dunno if you've got anything gdb-equivalent under Windows, but that's the first thing I'd be interested in ... Here ya go: http://www.devisser-siderius.com/stack1.jpg http://www.devisser-siderius.com/stack2.jpg http://www.devisser-siderius.com/stack3.jpg There are three threads in the process. I guess thread 1 (stack1.jpg) is the most interesting. I also noted that cranking up concurrency in my app reproduces the problem in about 4 minutes ;-) Actually, stack2 looks very interesting. Does it stay stuck in pg_queue_signal? That's really not supposed to happen. Also, can you confirm that stack1 actually *stops* in pgwin32_waitforsinglesocket? Or does it go out and come back? ;-) (A good signal of this is to check the cswitch delta. If it stays at zero, then it's stuck. If it shows any values, that means it's actuall going out and coming back) And finally, is this 8.0 or 8.1? There have been some significant changes in the handling of the signals between the two... //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Process Time X200
On 10.03.2006, at 10:11 Uhr, NbForYou wrote: So the only solution is to ask my webhost to upgrade its postgresql? Seems to be. The question is will he do that? You are the customer. If they don't, go to another provider. After all a license fee is required for commercial use. And running a webhosting service is a commercial use. No license fee is required for any use of PostgreSQL. Read the license: Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. A commercial license is needed for MySQL, not for PostgreSQL. cug -- PharmaLine, Essen, GERMANY Software and Database Development smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] Process Time X200
On fös, 2006-03-10 at 10:11 +0100, NbForYou wrote: Hey Michael, you sure know your stuff! Versions: PostgreSQL 7.3.9-RH running on the webhost. PostgreSQL 8.0.3 running on my homeserver. So the only solution is to ask my webhost to upgrade its postgresql? The question is will he do that? After all a license fee is required for commercial use. And running a webhosting service is a commercial use. A licence fee for what? Certainly not for postgresql. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] x206-x225
Hello list. We have compared 2 IBM x servers: IBM X206 IBM X226 -- --- processor Pentium 4 3.2 Ghz Xeon 3.0 Ghz main memory 1.25 GB 4 GB discs 2 x SCSI RAID11RPM 1 x ATA 7200 RPM LINUX 2.6 (SUSE 9) same PGSQL 7.4same postgresql.conf attached same We have bij means of an informix-4GL program done the following test: create table : name char(18) adres char(20) key integer create index on (key) Time at X206 Time at X226 -- insert record (key goes from 1 to 1) 6 sec. 41 sec. select record (key goes from 1 to 1) 4 4 delete record (key goes from 1 to 1) 6 41 This is ofcourse a totally unexpected results (you should think off the opposite). Funny is that the select time is the same for both machines. Does anybody has any any idea what can cause this strange results or where we can start our investigations? Regards Henk Sanders postgresql.conf-74 Description: Binary data ---(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
[PERFORM] Query time
Hi, I think im specting problems with a 7.4.8 postgres database. Sometimes some big query takes between 5 to 15 seconds. It happens sometimes all the day it does not depend if database is busy. I have measured that sentence in 15 - 70 ms in normal circunstances. Why sometimes its takes too much time? How can I fix it? Is a postgres version problem, database problem or query problem? Any ideas will be apreciatted. Ruben Rubio ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Process Time X200
NbForYou wrote: Hey Michael, you sure know your stuff! Versions: PostgreSQL 7.3.9-RH running on the webhost. PostgreSQL 8.0.3 running on my homeserver. So the only solution is to ask my webhost to upgrade its postgresql? The question is will he do that? After all a license fee is required for commercial use. And running a webhosting service is a commercial use. No, you're thinking of MySQL - PostgreSQL is free for anyone, for any purpose. You can even distribute your own changes without giving them back to the community if you want to complicate your life. -- Richard Huxton Archonet Ltd ---(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
Re: [PERFORM] Query time
There is not possibility to use another database. It's the best option I have seen. We have been working in postgres in last 3 years, and this is the first problem I have seen. (The database is working in a large website, 6.000 visits per day in a dedicated server) Any other idea? Chethana, Rao (IE10) wrote: USUALLY POSTGRES DATABASE TAKES MORE TIME, COMPARED TO OTHER DATABASES. HOWEVER U CAN FINETUNE THE PERFORMANCE OF POSTGRESQL. IF U HAVE AN OPTION GO FOR SQLITE, MYSQL OR FIREBIRD. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ruben Rubio Rey Sent: Friday, March 10, 2006 2:06 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Query time Hi, I think im specting problems with a 7.4.8 postgres database. Sometimes some big query takes between 5 to 15 seconds. It happens sometimes all the day it does not depend if database is busy. I have measured that sentence in 15 - 70 ms in normal circunstances. Why sometimes its takes too much time? How can I fix it? Is a postgres version problem, database problem or query problem? Any ideas will be apreciatted. Ruben Rubio ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Process Time X200
Ok, Everybody keeps saying that Postgresql is free... So I contacted my webhost and their respons was they have to pay a license fee. But because they use PLESK as a service I think they are refering to a fee PLESK charges them for the use combination PLESK - POSTGRESQL I do not know however that this information is accurate... I thank everybody who have responded so far. Great feedback! - Original Message - From: Richard Huxton dev@archonet.com To: NbForYou [EMAIL PROTECTED] Cc: Michael Fuhr [EMAIL PROTECTED]; pgsql-performance@postgresql.org Sent: Friday, March 10, 2006 10:40 AM Subject: Re: [PERFORM] Process Time X200 NbForYou wrote: Hey Michael, you sure know your stuff! Versions: PostgreSQL 7.3.9-RH running on the webhost. PostgreSQL 8.0.3 running on my homeserver. So the only solution is to ask my webhost to upgrade its postgresql? The question is will he do that? After all a license fee is required for commercial use. And running a webhosting service is a commercial use. No, you're thinking of MySQL - PostgreSQL is free for anyone, for any purpose. You can even distribute your own changes without giving them back to the community if you want to complicate your life. -- Richard Huxton Archonet Ltd ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Query time
On Fri, Mar 10, 2006 at 11:05:57AM +0100, Ruben Rubio Rey wrote: Sometimes some big query takes between 5 to 15 seconds. It happens sometimes all the day it does not depend if database is busy. I have measured that sentence in 15 - 70 ms in normal circunstances. Is it the *exact* same query, including the values you're querying for? The same query with different values can run with different plans depending on row count estimates. It might be useful to see the query string and the EXPLAIN ANALYZE output for a fast query and a slow one. How many tables are you querying? Might you be hitting geqo_threshold (default 12)? If so then the following thread might be helpful: http://archives.postgresql.org/pgsql-performance/2006-01/msg00132.php -- Michael Fuhr ---(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
Re: [PERFORM] Process Time X200
Ok, Everybody keeps saying that Postgresql is free... So I contacted my webhost and their respons was they have to pay a license fee. But because they use PLESK as a service I think they are refering to a fee PLESK charges them for the use combination PLESK - POSTGRESQL Probably. Although in my humble opinion, proposing postgres 7.3 in 2006 is a bit disrespectful to the considerable work that has been done by the postgres team since that release. If you don't find a host to your liking, and you have a large website, as you say, consider a dedicated server. Prices are quite accessible now, you can install the latest version of Postgres. Going from 7.3 to 8.1, and having your own server with all its resources dedicated to running your site, will probably enhance your performance. Consider lighttpd which is a speed demon and uses very little resources. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Query time
Michael Fuhr wrote: On Fri, Mar 10, 2006 at 11:05:57AM +0100, Ruben Rubio Rey wrote: Sometimes some big query takes between 5 to 15 seconds. It happens sometimes all the day it does not depend if database is busy. I have measured that sentence in 15 - 70 ms in normal circunstances. Is it the *exact* same query, including the values you're querying for? The same query with different values can run with different plans depending on row count estimates. It might be useful to see the query string and the EXPLAIN ANALYZE output for a fast query and a slow one. How many tables are you querying? Might you be hitting geqo_threshold (default 12)? If so then the following thread might be helpful: http://archives.postgresql.org/pgsql-performance/2006-01/msg00132.php The querys that are failing are very similar. But, when I see the warning in logs files, I take that query and try it, and takes a few miliseconds (as spected). That query one table on FROM and 2 or 3 on WHERE clause. Rigth now Im using the default server configuration for geqo_threshold, but i'll ckeck the link that u provide me. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hanging queries on dual CPU windows
On Friday 10 March 2006 04:20, Magnus Hagander wrote: Is it possible to get a stack trace from the stuck process? I dunno if you've got anything gdb-equivalent under Windows, but that's the first thing I'd be interested in ... Here ya go: http://www.devisser-siderius.com/stack1.jpg http://www.devisser-siderius.com/stack2.jpg http://www.devisser-siderius.com/stack3.jpg There are three threads in the process. I guess thread 1 (stack1.jpg) is the most interesting. I also noted that cranking up concurrency in my app reproduces the problem in about 4 minutes ;-) Just reproduced again. Actually, stack2 looks very interesting. Does it stay stuck in pg_queue_signal? That's really not supposed to happen. Yes it does. Also, can you confirm that stack1 actually *stops* in pgwin32_waitforsinglesocket? Or does it go out and come back? ;-) (A good signal of this is to check the cswitch delta. If it stays at zero, then it's stuck. If it shows any values, that means it's actuall going out and coming back) I only see CSwitch change once I click OK on the thread window. Once I do that, it goes up to 3 and back to blank again. The 'context switches' counter does not increase like it does for other processes (like e.g. process explorer itself). Another thing which may or may not be of interest: Nothing is listed in the 'TCP/IP' tab for the stuck process. I would have expected to see at least the socket of the client connection there?? And finally, is this 8.0 or 8.1? There have been some significant changes in the handling of the signals between the two... This is 8.1.3 on Windows 2003 Server. Also reproduced on 8.1.0 and 8.1.1 (also on 2K3). //Magnus jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] x206-x225
H.J. Sanders wrote: X206 IBM X226 ----- processorPentium 4 3.2 GhzXeon 3.0 Ghz main memory1.25 GB4 GB discs 2 x SCSI RAID1 1RPM 1 x ATA 7200 RPM Noting that the SCSI discs are on the *slower* machine. Time at X206Time at X226 -- insert record (1 to 1) 6 sec.41 sec. select record (1 to 1) 4 4 delete record (1 to 1) 6 41 This is ofcourse a totally unexpected results (you should think off the opposite). Your ATA disk is lying about disk caching being turned off. Assuming each insert is in a separate transaction, then it's not going to do 10,000 / 6 = 1667 transactions/sec - that's faster than it's rotational speed. Funny is that the select time is the same for both machines. Because you're limited by the speed to read from RAM. By the way - these sort of tests are pretty much meaningless in any practical terms. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Query time
Ruben Rubio Rey wrote: Hi, I think im specting problems with a 7.4.8 postgres database. Sometimes some big query takes between 5 to 15 seconds. It happens sometimes all the day it does not depend if database is busy. I have measured that sentence in 15 - 70 ms in normal circunstances. Why sometimes its takes too much time? How can I fix it? Is a postgres version problem, database problem or query problem? Information, Ruben - we can't do anything without information. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] x206-x225
The primary slow down is probably between your system bus from main memory to your disk storage. If you notice from your statistics that the select statements are very close. This is because all the data you need is already in system memory. The primary bottle neck is probably disk I/O. Scsi will always be faster than ATA. Scsi devices have dedicated hardware for getting data to and from the disc to the main system bus without requiring a trip through the CPU. You may be able to speed up the ata disc by enabling DMA by using hdparm. hdparm -d1 /dev/hda (or whatever your device is) -Daniel -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) iD8DBQBEEYzX9SJ2nhowvKERAoiFAKCLR+7a7ReZ2mjjPjpONHLGIQD1SgCeNNON V1kbyATIFVPWuf1W6Ji0IFg= =5Msr -END PGP SIGNATURE- On 3/10/06, Richard Huxton dev@archonet.com wrote: H.J. Sanders wrote: X206 IBM X226 - processorPentium 4 3.2 GhzXeon 3.0 Ghz main memory1.25 GB4 GB discs2 x SCSI RAID11RPM 1 x ATA 7200 RPMNoting that the SCSI discs are on the *slower* machine. Time at X206Time at X226 -- insert record (1 to 1)6 sec.41 sec. select record (1 to 1)44 delete record (1 to 1)6 41 This is ofcourse a totally unexpected results (you should think off the opposite).Your ATA disk is lying about disk caching being turned off. Assumingeach insert is in a separate transaction, then it's not going to do 10,000 / 6 = 1667 transactions/sec - that's faster than it's rotationalspeed. Funny is that the select time is the same for both machines.Because you're limited by the speed to read from RAM. By the way - these sort of tests are pretty much meaningless in anypractical terms.-- Richard Huxton Archonet Ltd---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hanging queries on dual CPU windows
Hi, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, March 09, 2006 9:11 PM To: Jan de Visser Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Hanging queries on dual CPU windows Jan de Visser [EMAIL PROTECTED] writes: Furtermore, it does not happen on Linux machines, both single CPU and dual CPU, nor on single CPU windows machines. We can only reproduce on a dual CPU windows machine, and if we take one CPU out, it does not happen. ... Which showed me that several transactions where waiting for a particular row which was locked by another transaction. This transaction had no pending locks (so no deadlock), but just does not complete and hence never relinquishes the lock. Is the stuck transaction still consuming CPU time, or just stopped? Is it possible to get a stack trace from the stuck process? I dunno if you've got anything gdb-equivalent under Windows, but that's the first thing I'd be interested in ... Debugging Tools for Windows from Microsoft http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx Additinonally you need a symbol-file or you use SRV*c:\debug\symbols*http://msdl.microsoft.com/download/symbols; to load the symbol-file dynamically from the net. Best regards regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Hakan Kocaman Software-Development digame.de GmbH Richard-Byrd-Str. 4-8 50829 Köln Tel.: +49 (0) 221 59 68 88 31 Fax: +49 (0) 221 59 68 88 98 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hanging queries on dual CPU windows
On Friday 10 March 2006 09:03, Jan de Visser wrote: On Friday 10 March 2006 04:20, Magnus Hagander wrote: Is it possible to get a stack trace from the stuck process? I dunno if you've got anything gdb-equivalent under Windows, but that's the first thing I'd be interested in ... Here ya go: http://www.devisser-siderius.com/stack1.jpg http://www.devisser-siderius.com/stack2.jpg http://www.devisser-siderius.com/stack3.jpg There are three threads in the process. I guess thread 1 (stack1.jpg) is the most interesting. I also noted that cranking up concurrency in my app reproduces the problem in about 4 minutes ;-) Just reproduced again. Actually, stack2 looks very interesting. Does it stay stuck in pg_queue_signal? That's really not supposed to happen. Yes it does. An update on that: There is actually *two* processes in this state, both hanging in pg_queue_signal. I've looked at the source of that, and the obvious candidate for hanging is EnterCriticalSection. I also found this: http://blogs.msdn.com/larryosterman/archive/2005/03/02/383685.aspx where they say: In addition, for Windows 2003, SP1, the EnterCriticalSection API has a subtle change that's intended tor resolve many of the lock convoy issues. Before Win2003 SP1, if 10 threads were blocked on EnterCriticalSection and all 10 threads had the same priority, then EnterCriticalSection would service those threads in a FIFO (first -in, first-out) basis. Starting in Windows 2003 SP1, the EnterCriticalSection will wake up a random thread from the waiting threads. If all the threads are doing the same thing (like a thread pool) this won't make much of a difference, but if the different threads are doing different work (like the critical section protecting a widely accessed object), this will go a long way towards removing lock convoy semantics. Could it be they broke it when they did that Also, can you confirm that stack1 actually *stops* in pgwin32_waitforsinglesocket? Or does it go out and come back? ;-) (A good signal of this is to check the cswitch delta. If it stays at zero, then it's stuck. If it shows any values, that means it's actuall going out and coming back) I only see CSwitch change once I click OK on the thread window. Once I do that, it goes up to 3 and back to blank again. The 'context switches' counter does not increase like it does for other processes (like e.g. process explorer itself). Another thing which may or may not be of interest: Nothing is listed in the 'TCP/IP' tab for the stuck process. I would have expected to see at least the socket of the client connection there?? And finally, is this 8.0 or 8.1? There have been some significant changes in the handling of the signals between the two... This is 8.1.3 on Windows 2003 Server. Also reproduced on 8.1.0 and 8.1.1 (also on 2K3). //Magnus jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(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] Hanging queries on dual CPU windows
On Friday 10 March 2006 09:32, Jan de Visser wrote: Actually, stack2 looks very interesting. Does it stay stuck in pg_queue_signal? That's really not supposed to happen. Yes it does. An update on that: There is actually *two* processes in this state, both hanging in pg_queue_signal. I've looked at the source of that, and the obvious candidate for hanging is EnterCriticalSection. I also found this: http://blogs.msdn.com/larryosterman/archive/2005/03/02/383685.aspx where they say: In addition, for Windows 2003, SP1, the EnterCriticalSection API has a subtle change that's intended tor resolve many of the lock convoy issues. Before Win2003 SP1, if 10 threads were blocked on EnterCriticalSection and all 10 threads had the same priority, then EnterCriticalSection would service those threads in a FIFO (first -in, first-out) basis. Starting in Windows 2003 SP1, the EnterCriticalSection will wake up a random thread from the waiting threads. If all the threads are doing the same thing (like a thread pool) this won't make much of a difference, but if the different threads are doing different work (like the critical section protecting a widely accessed object), this will go a long way towards removing lock convoy semantics. Could it be they broke it when they did that See also this: http://bugs.mysql.com/bug.php?id=12071 It appears the mysql people ran into this and concluded it is a Windows bug they needed to work around. jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Hanging queries on dual CPU windows
I dunno if you've got anything gdb-equivalent under Windows, but that's the first thing I'd be interested in ... Here ya go: http://www.devisser-siderius.com/stack1.jpg http://www.devisser-siderius.com/stack2.jpg http://www.devisser-siderius.com/stack3.jpg There are three threads in the process. I guess thread 1 (stack1.jpg) is the most interesting. I also noted that cranking up concurrency in my app reproduces the problem in about 4 minutes ;-) Just reproduced again. Actually, stack2 looks very interesting. Does it stay stuck in pg_queue_signal? That's really not supposed to happen. Yes it does. An update on that: There is actually *two* processes in this state, both hanging in pg_queue_signal. I've looked at the source of that, and the obvious candidate for hanging is EnterCriticalSection. I also found this: http://blogs.msdn.com/larryosterman/archive/2005/03/02/383685.aspx where they say: In addition, for Windows 2003, SP1, the EnterCriticalSection API has a subtle change that's intended tor resolve many of the lock convoy issues. Before Win2003 SP1, if 10 threads were blocked on EnterCriticalSection and all 10 threads had the same priority, then EnterCriticalSection would service those threads in a FIFO (first -in, first-out) basis. Starting in Windows 2003 SP1, the EnterCriticalSection will wake up a random thread from the waiting threads. If all the threads are doing the same thing (like a thread pool) this won't make much of a difference, but if the different threads are doing different work (like the critical section protecting a widely accessed object), this will go a long way towards removing lock convoy semantics. Could it be they broke it when they did that In theory, yes, but it still seems a bit far fetched :-( If you have the env to rebuild, can you try changing the order of the lines: ResetEvent(pgwin32_signal_event); LeaveCriticalSection(pg_signal_crit_sec); in backend/port/win32/signal.c And if not, can you also try disabling the stats collector and see if that makes a difference. (Could be a workaround..) //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_reset_stats + cache I/O %
On Thu, Mar 09, 2006 at 08:13:30AM -0500, mcelroy, tim wrote: charts showing system and in this case DB performance. I'm basically just using the out-of-the-box defaults in my postgresql.conf file and that seems Ugh... the default config won't get you far. Take a look here: http://www.powerpostgresql.com/Downloads/annotated_conf_80.html Or, I've been planning on posting a website with some better canned postgresql.conf config files for different configurations; if you send me specs on the machine you're running on I'll come up with something that's at least more reasonable. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Using materialized views for commonly-queried subsets
See also http://www.jonathangardner.net/PostgreSQL/materialized_views/matviews.html On Fri, Mar 10, 2006 at 02:25:08AM +, Casey Allen Shobe wrote: I typed up a description of a situation where the only viable option to improve performance was to use a materialized view, which, when implemented, was found to improve performance twenty-sevenfold, even with a fairly small amount of excess data (which is antipated to grow). I thought this might be of use to anybody else in a similar situation, so I thought I'd post it here. http://community.seattleserver.com/viewtopic.php?t=11 Feel free to reproduce as you see fit. Cheers, -- Casey Allen Shobe | [EMAIL PROTECTED] | 206-381-2800 SeattleServer.com, Inc. | http://www.seattleserver.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Query time
On Fri, Mar 10, 2006 at 11:29:53AM +0100, Ruben Rubio Rey wrote: There is not possibility to use another database. It's the best option I have seen. We have been working in postgres in last 3 years, and this is the first problem I have seen. (The database is working in a large website, 6.000 visits per day in a dedicated server) Any other idea? Chethana, Rao (IE10) wrote: USUALLY POSTGRES DATABASE TAKES MORE TIME, COMPARED TO OTHER DATABASES. HOWEVER U CAN FINETUNE THE PERFORMANCE OF POSTGRESQL. IF U HAVE AN OPTION GO FOR SQLITE, MYSQL OR FIREBIRD. If I were you I wouldn't believe any performance recommendations from someone who can't find their caps-lock key or spell you. The fact is, on any meaningful benchmark current versions of PostgreSQL are on par with other databases. Any benchmark that shows PostgreSQL to be 'slow' is almost certain to be very old and/or does a very poor job of reflecting how client-server databases are normally used. The one caveat is that PostgreSQL is often overkill for single user embedded database type apps. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hanging queries on dual CPU windows
On Friday 10 March 2006 10:11, Magnus Hagander wrote: Could it be they broke it when they did that In theory, yes, but it still seems a bit far fetched :-( Well, I rolled back SP1 and am running my test again. Looking much better, hasn't locked up in 45mins now, whereas before it would lock up within 5mins. So I think they broke something. jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(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] one-field index vs. multi-field index planner estimates
Hello. Recently I've discovered an interesting thing (Postgres version 8.1.3): example table: CREATE TABLE test ( id INT, name TEXT, comment TEXT, phone TEXT, visible BOOLEAN ); then, CREATE INDEX i1 ON test(phone); CREATE INDEX i2 ON test(phone, visible); CREATE INDEX i3 ON test(phone, visible) WHERE visible; then insert lot's of data and try to execute query like: SELECT * FROM test WHERE phone='12345' AND visible; uses index i1, and filters all visible fields. When I drop index i1, postgres starts to use index i2 and the query began to work much more faster. When I drop index i2, postgres uses index i3 which is faster than i2 ofcourse. I've noticed that planner estimated all queries for all three cases with the same cost. So, is it a planner bad estimate or what? -- Evgeny Gridasov Software Engineer I-Free, Russia ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Process Time X200
On Fri, 2006-03-10 at 04:45, NbForYou wrote: Ok, Everybody keeps saying that Postgresql is free... So I contacted my webhost and their respons was they have to pay a license fee. But because they use PLESK as a service I think they are refering to a fee PLESK charges them for the use combination PLESK - POSTGRESQL I do not know however that this information is accurate... I thank everybody who have responded so far. Great feedback! I think it's time to get a new hosting provider. If they're still running PostgreSQL 7.3.9 (the latest 7.3 is 7.3.14, and 8.1.3 is amazingly faster than 7.3.anything...) then they're likely not updating other vital components either, and therefore it's only a matter of time before your machine gets hacked. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] one-field index vs. multi-field index planner estimates
Evgeny Gridasov [EMAIL PROTECTED] writes: Recently I've discovered an interesting thing (Postgres version 8.1.3): Have you ANALYZEd the table since loading it? What fraction of the rows have visible = true? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] one-field index vs. multi-field index planner
Tom, ofcourse I've analyzed it. visible is true for about 0.3% of all rows. testing table contains about 300,000-500,000 rows. On Fri, 10 Mar 2006 12:09:19 -0500 Tom Lane [EMAIL PROTECTED] wrote: Evgeny Gridasov [EMAIL PROTECTED] writes: Recently I've discovered an interesting thing (Postgres version 8.1.3): Have you ANALYZEd the table since loading it? What fraction of the rows have visible = true? -- Evgeny Gridasov Software Engineer I-Free, Russia ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Trouble managing planner for timestamptz columns
We have large tables that hold statistics based on time. They are of the form. CREATE TABLE stats ( id serial primary key, logtime timestamptz, d1 int, s1 bigint ); CREATE INDEX idx on stats(logtime); Some of these tables have new data inserted at a rate of 500,000+ rows / hour. The entire table will grow to being 10's to 100's of millions of rows in size. (Yes, we are also paritioning these, it's the size of an individual partition that we're talking about). We tend to analyze these tables every day or so and this doesn't always prove to be sufficient Our application is a reporting application and the end users typically like to query the newest data the most. As such, the queries of the form... select * from stats inner join dimension_d1 using (d1) where logtime between X and Y and d1.something = value; This usually results in a hash join (good thing) where the dimension table is loaded into the hash table and it index scans stats using idx index. The trouble starts when both X and Y are times after the last analyze. This restriction clause is outside the range of values in the historgram created by the last analyze. Postgres's estimate on the number of rows returned here is usually very low and incorrect, as you'd expect... Trouble can occur when the planner will flip its decision and decide to hash join by loading the results of the index scan on idx into the hash table instead of the dimension table Since the table is so large and the system is busy (disk not idle at all), doing an analyze on this table in the production system can take 1/2 hour! (statistics collector set to 100). We can't afford to analyze more often... It certainly would be nice if postgres could understand somehow that some columns are dynamic and that it's histogram could be stretched to the maximal values or some other technique for estimating rows to the right of the range of values in the histogram... Or have some concept of error bars on it's planner decisions Suggestions? Comments? Marc ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hanging queries on dual CPU windows
Could it be they broke it when they did that In theory, yes, but it still seems a bit far fetched :-( Well, I rolled back SP1 and am running my test again. Looking much better, hasn't locked up in 45mins now, whereas before it would lock up within 5mins. So I think they broke something. Wow. I guess I was lucky that I didn't say it was impossible :-) But what really is happening. What other thread is actually holding the critical section at this point, causing us to block? The only places it gets held is while looping the signal queue, but it is released while calling the signal function itself... But they obviously *have* been messing with critical sections, so maybe they accidentally changed something else as well... What bothers me is that nobody else has reported this. It could be that this was exposed by the changes to the signal handling done for 8.1, and the ppl with this level of concurrency are either still on 8.0 or just not on SP1 for their windows boxes yet... Do you have any other software installed on the machine? That might possibly interfere in some way? But let's have it run for a bit longer to confirm this does help. If so, we could perhaps recode that part using a Mutex instead of a critical section - since it's not a performance critical path, the difference shouldn't be large. If I code up a patch for that, can you re-apply SP1 and test it? Or is this a production system you can't really touch? //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] one-field index vs. multi-field index planner
Evgeny Gridasov [EMAIL PROTECTED] writes: ofcourse I've analyzed it. visible is true for about 0.3% of all rows. Well, I get an indexscan on i3 ... there isn't going to be any strong reason for the planner to prefer i2 over i1, given that the phone column is probably near-unique and the i2 index will be bigger than i1. I don't see why it wouldn't like i3 though. Could we see the EXPLAIN ANALYZE results with and without i3? regression=# CREATE TABLE test (phone TEXT, visible BOOLEAN); CREATE TABLE regression=# insert into test select (z/2)::text,(z%1000)=3 from generate_series(1,30) z; INSERT 0 30 regression=# CREATE INDEX i1 ON test(phone); CREATE INDEX regression=# CREATE INDEX i2 ON test(phone, visible); CREATE INDEX regression=# CREATE INDEX i3 ON test(phone, visible) WHERE visible; CREATE INDEX regression=# analyze test; ANALYZE regression=# explain SELECT * FROM test WHERE phone='12345' AND visible; QUERY PLAN Index Scan using i3 on test (cost=0.00..5.82 rows=1 width=10) Index Cond: ((phone = '12345'::text) AND (visible = true)) (2 rows) regression=# drop index i3; DROP INDEX regression=# explain SELECT * FROM test WHERE phone='12345' AND visible; QUERY PLAN Index Scan using i2 on test (cost=0.00..5.82 rows=1 width=10) Index Cond: ((phone = '12345'::text) AND (visible = true)) Filter: visible (3 rows) regression=# regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hanging queries on dual CPU windows
On Friday 10 March 2006 13:25, Magnus Hagander wrote: Could it be they broke it when they did that In theory, yes, but it still seems a bit far fetched :-( Well, I rolled back SP1 and am running my test again. Looking much better, hasn't locked up in 45mins now, whereas before it would lock up within 5mins. So I think they broke something. Wow. I guess I was lucky that I didn't say it was impossible :-) But what really is happening. What other thread is actually holding the critical section at this point, causing us to block? The only places it gets held is while looping the signal queue, but it is released while calling the signal function itself... But they obviously *have* been messing with critical sections, so maybe they accidentally changed something else as well... What bothers me is that nobody else has reported this. It could be that this was exposed by the changes to the signal handling done for 8.1, and the ppl with this level of concurrency are either still on 8.0 or just not on SP1 for their windows boxes yet... Do you have any other software installed on the machine? That might possibly interfere in some way? Just a JDK, JBoss, cygwin (running sshd), and a VNC Server. I don't think that interferes. But let's have it run for a bit longer to confirm this does help. I turned it off after 2.5hr. The longest I had to wait before, with less load, was 1.45hr. If so, we could perhaps recode that part using a Mutex instead of a critical section - since it's not a performance critical path, the difference shouldn't be large. If I code up a patch for that, can you re-apply SP1 and test it? Or is this a production system you can't really touch? I can do whatever the hell I want with it, so if you could cook up a patch that would be great. As a BTW: I reinstalled SP1 and turned stats collection off. That also seems to work, but is not really a solution since we want to use autovacuuming. //Magnus jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(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] Hanging queries on dual CPU windows
On Friday 10 March 2006 14:27, Jan de Visser wrote: As a BTW: I reinstalled SP1 and turned stats collection off. That also seems to work, but is not really a solution since we want to use autovacuuming. I lied. I hangs now. Just takes a lot longer... jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(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] Trouble managing planner for timestamptz columns
Marc Morin [EMAIL PROTECTED] writes: We tend to analyze these tables every day or so and this doesn't always prove to be sufficient Seems to me you just stated your problem. Instead of having the planner make wild extrapolations, why not set up a cron job to analyze these tables more often? Or use autovacuum which will do it for you. Since the table is so large and the system is busy (disk not idle at all), doing an analyze on this table in the production system can take 1/2 hour! (statistics collector set to 100). I'd believe that for vacuum analyze, but analyze alone should be cheap. Have you perhaps got some weird datatypes in the table? Maybe you should back off the stats target a bit? We do support analyzing selected columns, so you might try something like a cron job analyzing only the timestamp column, with a suitably low stats target for that column. This would yield numbers far more reliable than any extrapolation the planner could do. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Trouble managing planner for timestamptz columns
Well this analyze just took 12 minutes... Stats target of 100. # time psql xxx xxx -c analyze elem_trafficstats_1 ANALYZE real12m1.070s user0m0.001s sys 0m0.015s A large table, but by far, not the largest... Have about 1 dozen or so tables like this, so analyzing them will take 3-4 hours of time... No weird datatypes, just bigints for facts, timestamptz and ints for dimensions. My problem is not the analyze itself, it's the fact that our db is really busy doing stuff Analyze I/O is competing... I am random I/O bound like crazy. If I set the stats target to 10, I get # time psql xxx -c set session default_statistics_target to 10;analyze elem_trafficstats_1 ANALYZE real2m15.733s user0m0.009s sys 0m2.255s Better, but not sure what side affect this would have. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, March 10, 2006 1:31 PM To: Marc Morin Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Trouble managing planner for timestamptz columns Marc Morin [EMAIL PROTECTED] writes: We tend to analyze these tables every day or so and this doesn't always prove to be sufficient Seems to me you just stated your problem. Instead of having the planner make wild extrapolations, why not set up a cron job to analyze these tables more often? Or use autovacuum which will do it for you. Since the table is so large and the system is busy (disk not idle at all), doing an analyze on this table in the production system can take 1/2 hour! (statistics collector set to 100). I'd believe that for vacuum analyze, but analyze alone should be cheap. Have you perhaps got some weird datatypes in the table? Maybe you should back off the stats target a bit? We do support analyzing selected columns, so you might try something like a cron job analyzing only the timestamp column, with a suitably low stats target for that column. This would yield numbers far more reliable than any extrapolation the planner could do. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] firebird X postgresql 8.1.2 windows, performance comparison
Hello, I got good results on tuning postgresql performance for my friend. One of the queries took almost 10 minutes. Now it completes on 26 miliseconds! (at the second run) A combination of query otimization, indexes choosing (with some droping and clustering), server parameters reconfigurations. Firebird still execute it on almost 2 minutes, much slower. Firebird is much slower than Postgresql at queries without joins. Postgresql is lightning faster than Firebird when manually tunned and without using joins and aggregates functions. The example query and its explain analyze results are attached, with the show all output of each config iteration, and indexes created. (UPDATE: i am sending msg from home and does not have the correct log file here. Will send the file at monday) BUT there are some issues still unknown. The example query executes consistently at 56 seconds, and even at 39 seconds. Firebird executes the same query at 54 seconds the first time and at 20 seconds at next times. Today I went to the machine (was previously executing pg commands remotely) to observe the windows behaviour. Postgresql uses around 30% cpu and hard disk heavily (not so as vacuum) at all executions. Firebird uses around 40% cpu and hard disk heavily at the first execution. The second execution uses around 60% cpu and **NO** disk activity. The previously cited query running at 26 miliseconds down from 10 minutes, can achieve this performance at the second run, with **NO** disk activity. At the first run it uses 1,7 seconds, down from 10 minutes. The hard disk is clearly a bottleneck. 1,7 seconds against 26 miliseconds. So, How convince postgresql to use windows disk cache or to read all indexes to ram? It seems that effective_cache_size does not tell postgresql to actually use windows disk cache. What parameter must be configured? Do you have some suggestions? Regards. Andre Felipe Machado www.techforce.com.br ---(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
Re: [PERFORM] x206-x225
On Fri, 2006-03-10 at 13:40 +, Richard Huxton wrote: Your ATA disk is lying about disk caching being turned off. Assuming each insert is in a separate transaction, then it's not going to do 10,000 / 6 = 1667 transactions/sec - that's faster than it's rotational speed. Could you explain the calculation? Why should the number of transactions be related to the rotational speed of the disk, without saying anything about the number of bytes per rotation? -- 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] x206-x225
On Sat, 11 Mar 2006, Joost Kraaijeveld wrote: On Fri, 2006-03-10 at 13:40 +, Richard Huxton wrote: Your ATA disk is lying about disk caching being turned off. Assuming each insert is in a separate transaction, then it's not going to do 10,000 / 6 = 1667 transactions/sec - that's faster than it's rotational speed. Could you explain the calculation? Why should the number of transactions be related to the rotational speed of the disk, without saying anything about the number of bytes per rotation? each transaction requires a sync to the disk, a sync requires a real write (which you then wait for), so you can only do one transaction per rotation. David Lang ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
