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
[PERFORM] Postgresql on SAN
Title: Message Hello, Has anyone designed/implemented postgresql server on storage networks? Are there any design considerations? Are there any benchmarks for storage products (HBAs, Switches, Storage Arrays)? Any recommendation on the design, resources, references, keeping PG in mind? Thanks,Anjan ** This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.
Re: [PERFORM] Postgresql on SAN
Anjan, > Has anyone designed/implemented postgresql server on storage networks? Yes, Zapatec.com runs their stuff this way. Probably others as well. > Are there any design considerations? I don't know. Probably. > Are there any benchmarks for storage products (HBAs, Switches, Storage > Arrays)? Not specific to PostgreSQL.I'm sure there are generic benchmarks. Keep in mind that PostgreSQL needs lots of 2-way I/O, batch writes, and random reads. > Any recommendation on the design, resources, references, keeping PG in > mind? See above. Also keep in mind that PostgreSQL's use of I/O should improve 100% in version 7.5. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Forcing filter/join order?
Tom, > Uh, why do you need the child table? Because there's linked information which needs to be kept by day for multi-day events. Also, it makes calendar reports easier, where one wants each day of a multi-day event to appear on each day of the calendar. >Seems like the correct incantation > given an assumption about maximum duration is > > event_date <= 'end-date' AND (event_date + duration) >= 'start-date' > AND event_date >= 'start-date' - 'max-duration' H ... so the same as what I have, only with the extra condition for event_date+duration and without the IN clause. I'll try it, thanks! -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Postgresql on SAN
Josh Berkus wrote: Anjan, Has anyone designed/implemented postgresql server on storage networks? Yes, Zapatec.com runs their stuff this way. Probably others as well. Are there any design considerations? I don't know. Probably. Are there any benchmarks for storage products (HBAs, Switches, Storage Arrays)? Not specific to PostgreSQL.I'm sure there are generic benchmarks. Keep in mind that PostgreSQL needs lots of 2-way I/O, batch writes, and random reads. Any recommendation on the design, resources, references, keeping PG in mind? See above. Also keep in mind that PostgreSQL's use of I/O should improve 100% in version 7.5. We run PG on a SAN array. We currently have it setup so a single PG instance runs off of a single LUN, this includes the WAL logs. Apart from that we have made no other special considerations; we just treat it as a fast RAID array. We haven't got to the stage where the speed of the SAN is a problem as load hasn't increased as expected. This will change, when it does I am sure the performance list will be hearing from us ;-). Out current limitations, as I see it, are amount of memory and then processing power. The only problem we have had was a dodgy set of kernel modules (drivers) for the fibre cards, this was because they were beta drivers and obviously still had a few bugs. This was solved by reverting to an older version. Everything has run smoothly since then (uptime is 153 days :-)). Nick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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] Optimizer difference using function index between 7.3 and 7.4
>Jeff Boes writes > # explain select link_id from links l join clm_tmp_links t on > (fn_urlrev(l.path_base) = t.rev_path_base); > executes in 59.8 seconds! > Now the odd part: if I change the query to this: > > # explain analyze select link_id from links l join clm_tmp_links t on > (fn_urlrev(l.path_base) = fn_urlrev(t.rev_path_base)); > Total runtime: 18.125 ms > > (i.e., apply the function to the data in the temp table), it runs a > whole lot faster! Is this a bug in the optimizer? Or did something > change about the way functional indexes are used? Erm..I may have misunderstood your example, but surely the second formulation of your query returns the wrong answer? It looks to me as if you are comparing a reversed URL with a twice-reversed URL; if that's true that would explain why it runs faster: They don't ever match. Is that right? Thanks for the idea of reversing the URLs, nice touch. I'd been thinking about reverse key indexes as a way of relieving the hotspot down the rightmost edge of an index during heavy insert traffic. I hadn't thought this would also speed up the access also. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Forcing filter/join order?
Tom, > event_date <= 'end-date' AND (event_date + duration) >= 'start-date' > AND event_date >= 'start-date' - 'max-duration' Great suggestion! We're down to 160ms, from about 370ms with my subselect workaround. Thanks! -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
