Cost based SELECT/UPDATE

> >>>Is there any way to do SELECTs with different priorities?
> >>
> >>>The issue is that response time for
> >>>these 50 processes is very important unlike for report generation, and
> >>>time spent by these processes while report running is unacceptable for
> >>>my production environment (response time grows from 1-3 seconds up to
> >>>1-2 minutes).
> >>
> >>The most important question is why response time drops so much? Does it
> >>look like it's disk I/O that's the problem?
> >>
> >
> > Yes, I/O grows as much as 300 - 700 tps (100% load) according to systat -vmstat.
> > I'm having 2 x 160Gb HDDs, data on one of them, pg_xlog on another
>
> Hmm - with your pg_xlog on a separate disk, updates should be relatively
> unaffected by a large SELECT going through. With these 50 other
> processes are most going through fairly quickly (less than 10 seconds),
> with some taking longer and a few taking 2 minutes or do they all take
> 1-2 minutes?
Results differ, but in range 30-120 secs...
Now looking whether some kind of RAID can improve my situation...
(workaround i'm currently using is COPY to another server, then SELECT
from it, but this does not work if report period includes, e.g.
current day)

--
Leonid Safronie
DIAS-RIPE