Re: [PERFORM] Slow response of PostgreSQL
On Tue, 17 Feb 2004, Saleem Burhani Baloch wrote: select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit; on both databases. PostgreSQL Machine ** P-III 600Mhz (Dell Precision 220) 256 MB Ram (RD Ram) 40 GB Baracuda Ext2 File System. RedHat 7.2 PostgreSQL 7.1.3-2 Besides the comments on the conf file already sent, 7.1.3 is many versions behind the current version and definately has some deficiencies either fully or partially corrected in later versions. All in all, I'd suggest going all the way to 7.4.1 since the hash aggregate stuff might help the queries you're running. ---(end of broadcast)--- TIP 3: 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] Slow response of PostgreSQL
Shridhar Daithankar [EMAIL PROTECTED] writes: Right now, it is hotly debated on HACKERS about adding a NOWAIT clause to SELECT FOR UPDATE. If you think your application deployment is away for months and can try CVS head, you can expect some action on it in coming few days. You can also try using the statement_timeout configuration variable that is already included with 7.4. It's not exactly don't wait for locks, but should approximate that behavior well enough. http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-CLIENT -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Slow response of PostgreSQL
Hi, Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( rh 9 require a lot of lib's) and set the configuration sent by Chris. Now the query results in 6.3 sec waooo. I m thinking that why the 7.1 process aggregate slowly. Anyway. I still have to go for 2 sec result and now I m thinking to go for Free BSD 5.2. The record locking by select . update for is working fine, but a situation arrises when the second user goes for locking the record, the task will wait untill the first user ends up his work. IS their is a way that we can know or get a message that the row/record is already locked by some one else. Thanks for help. Saleem ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Slow response of PostgreSQL
On Thursday 19 February 2004 14:31, Saleem Burhani Baloch wrote: Hi, Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( rh 9 require a lot of lib's) and set the configuration sent by Chris. Now the query results in 6.3 sec waooo. I m thinking that why the 7.1 process aggregate slowly. Anyway. I still have to go for 2 sec result and now I m thinking to go for Free BSD 5.2. Before that you can try something with kernel 2.6.3. I think it could make the difference you are looking at. The record locking by select . update for is working fine, but a situation arrises when the second user goes for locking the record, the task will wait untill the first user ends up his work. IS their is a way that we can know or get a message that the row/record is already locked by some one else. Right now, it is hotly debated on HACKERS about adding a NOWAIT clause to SELECT FOR UPDATE. If you think your application deployment is away for months and can try CVS head, you can expect some action on it in coming few days. As a side bonus you would be benefitted by performance and scalability additions that won't make there way in 7.4 stream. HTH Shridhar ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Slow response of PostgreSQL
Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( rh 9 require a lot of lib's) and set the configuration sent by Chris. Now the query results in 6.3 sec waooo. I m thinking that why the 7.1 process aggregate slowly. Anyway. I'm glad we could help you Saleem :) We knew PostgreSQL wasn't that slow :P Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Slow response of PostgreSQL
Saleem Burhani Baloch kirjutas N, 19.02.2004 kell 11:01: Hi, Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( rh 9 require a lot of lib's) and set the configuration sent by Chris. Now the query results in 6.3 sec waooo. I m thinking that why the 7.1 process aggregate slowly. Anyway. I still have to go for 2 sec result What is the plan now ? Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Slow response of PostgreSQL
Christopher Kings-Lynne wrote: 1- How can I lock a single record so that other users can only read it. ?? You cannot do that in PostgreSQL. How about SELECT ... FOR UPDATE? -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Slow response of PostgreSQL
Saleem Burhani Baloch [EMAIL PROTECTED] writes: I have a question why MS-SQL with 256 MB RAM gives result in 2 sec ?? If I have low memory Postgres should give result in 10 sec as compared to MS-SQL. Are you still running 7.1? regards, tom lane ---(end of broadcast)--- TIP 3: 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] Slow response of PostgreSQL
Easy two step procedure for speeding this up: 1: Upgrade to 7.4.1 2: Read this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow response of PostgreSQL
1- How can I lock a single record so that other users can only read it. ?? You cannot do that in PostgreSQL. How about SELECT ... FOR UPDATE? No, because users cannot read the locked row in that case. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Slow response of PostgreSQL
On Wed, 18 Feb 2004, Christopher Kings-Lynne wrote: 1- How can I lock a single record so that other users can only read it. ?? You cannot do that in PostgreSQL. How about SELECT ... FOR UPDATE? No, because users cannot read the locked row in that case. I just tested it (within transactions) and it appeared that I could still view the rows selected for update. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow response of PostgreSQL
scott.marlowe wrote: On Wed, 18 Feb 2004, Christopher Kings-Lynne wrote: 1- How can I lock a single record so that other users can only read it. ?? You cannot do that in PostgreSQL. How about SELECT ... FOR UPDATE? No, because users cannot read the locked row in that case. I just tested it (within transactions) and it appeared that I could still view the rows selected for update. Thank you. I was just about to test it myself. The user's guide, section 9.3.2 states that this is the case: i.e. select for update will prevent concurrent updating of the row, while allowing queries utilizing that row to succeed. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Slow response of PostgreSQL
How about SELECT ... FOR UPDATE? No, because users cannot read the locked row in that case. I just tested it (within transactions) and it appeared that I could still view the rows selected for update. Ah, true. My mistake. OK, well you can do it in postgres then... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Slow response of PostgreSQL
Saleem Burhani Baloch [EMAIL PROTECTED] writes: PostgreSQL 7.1.3-2 Aside from the config issues Chris mentioned, I'd recommend trying a somewhat less obsolete version of Postgres. I believe the poor performance with grouped aggregates should be fixed in 7.4 and later. (Red Hat 7.2 is a bit long in the tooth as well.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
