On Thu, Nov 23, 2006 at 03:40:15PM -0500, Brad Nicholson wrote:
> On Tue, 2006-11-21 at 21:43 -0200, Carlos H. Reimer wrote:
> > Hi,
> >
> > We have an application that is mission critical, normally very fast,
> > but when an I/O or CPU bound transaction appears, the mission critical
> > application suffers. Is there a way go give some kind of priority to
> > this kind of application?
> > Reimer
>
>
> Not that I'm aware of. Depending on what the problems transactions are,
> setting up a replica on a separate machine and running those
> transactions against the replica might be the solution.

The BizGres project has been working on resource quotas, which might
eventually evolve to what you're looking for.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

* Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
> Hi,
>
> There is an article about "Lowering the priority of a PostgreSQL query"
> (http://weblog.bignerdranch.com/?p=11) that explains how to use the
> setpriority() to lower PostgreSQL processes.
>
> I?m wondering how much effective it would be for i/o bound systems.
>
> Will the setpriority() system call affect i/o queue too?

Nope, and in fact the article shows the way not to do it.

See http://en.wikipedia.org/wiki/Priority_inversion

Basically, lowering the priority of one backend in PostgreSQL can lead
to reduced performance of all, especially also the backends with
higher priorities.

(Think of priority inversion as a timed soft deadlock. It will
eventually resolve, because it's not a real deadlock, but it might
mean halting important stuff for quite some time.)

Taking the example above, consider the following processes and nice
values:

19x backends As nice = 0
1x backend B nice = 10 (doing maintenance work)
1x updatedb nice = 5 (running as a cronjob at night)

Now, it possible (the probability depends upon your specific
situation), where backend B grabs some internal lock that is needed,
and then it gets preempted by higher priority stuff. Well, the A
backends need that lock too, so they cannot run; instead we wait till
updatedb (which updates the locate search db, and goes through the
whole filesystem of the server) is finished.

Luckily most if not all of these processes are disc io bound, so they
get interrupted any way, and low priority processes don't starve.
Well, replace updatedb with something hogging the CPU, and rethink the
situation.

Andreas

---------------------------(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

Hi,

There is an article about "Lowering the priority of a PostgreSQL query"
(http://weblog.bignerdranch.com/?p=11) that explains how to use the
setpriority() to lower PostgreSQL processes.

I?m wondering how much effective it would be for i/o bound systems.

Will the setpriority() system call affect i/o queue too?

Reimer


> -----Mensagem original-----
> De: Jim C. Nasby [mailto:jim@nasby.net]
> Enviada em: domingo, 26 de novembro de 2006 22:52
> Para: Brad Nicholson
> Cc: carlos.reimer@opendb.com.br; pgsql-performance@postgresql.org
> Assunto: Re: [PERFORM] Priority to a mission critical transaction
>
>
> On Thu, Nov 23, 2006 at 03:40:15PM -0500, Brad Nicholson wrote:
> > On Tue, 2006-11-21 at 21:43 -0200, Carlos H. Reimer wrote:
> > > Hi,
> > >
> > > We have an application that is mission critical, normally very fast,
> > > but when an I/O or CPU bound transaction appears, the mission critical
> > > application suffers. Is there a way go give some kind of priority to
> > > this kind of application?
> > > Reimer
> >
> >
> > Not that I'm aware of. Depending on what the problems transactions are,
> > setting up a replica on a separate machine and running those
> > transactions against the replica might be the solution.
>
> The BizGres project has been working on resource quotas, which might
> eventually evolve to what you're looking for.
> --
> Jim Nasby jim@nasby.net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

All,

The Bizgres project is working on resource management for PostgreSQL. So far,
however, they have been able to come up with schemes that work for BI/DW at
the expense of OLTP. Becuase of O^N lock checking issues, resource
management for OLTP which doesn't greatly reduce overall performance seems a
near-impossible task.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Short summary:
* Papers studying priority inversion issues with
databases including PosgreSQL and realistic workloads
conclude setpriority() helps even in the presence of
priority inversion issues for TCP-C and TCP-W like
workloads.
* Avoiding priority inversion with priority inheritance
will further help some workloads (TCP-C) more than
others (TCP-W) but even without such schedulers
priority inversion does not cause as much harm
as the benefit you get from indirectly scheduling
I/O through setpriority() in any paper I've seen.

Andreas Kostyrka wrote:
> * Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
>> Will the setpriority() system call affect i/o queue too?
>
> Nope, and in fact the article shows the way not to do it.

Actually *YES* setpriority() does have an indirect effect
on the I/O queue.

This paper: http://www.cs.cmu.edu/~bianca/icde04.pdf
studies setpriority() with non-trivial (TCP-W and TCP-C)
workloads on a variety of databases and shows that
that setpriority() is *extremely* effective for
PostgreSQL.

"For TPC-C on MVCC DBMS, and in particular PostgreSQL,
CPU scheduling is most effective, due to its ability
to indirectly schedule the I/O bottleneck.

For TPC-C running on PostgreSQL,
the simplest CPU scheduling policy (CPU-Prio) provides
a factor of 2 improvement for high-priority transactions,
while adding priority inheritance (CPU-Prio-Inherit)
provides a factor of 6 improvement while hardly
penalizing low-priority transactions. Preemption
(P-CPU) provides no appreciable benefit over
CPU-Prio-Inherit."

> See http://en.wikipedia.org/wiki/Priority_inversion

Priority Inversion is a well studied problem; and depends
on both the workload and the database. In particular,
TPC-W workloads have been studied on a variety of databases
including PostgreSQL. Again, from:
http://www.cs.cmu.edu/~bianca/icde04.pdf

They observe that avoiding priority inversion
issues by enabling priority inheritance with PostgreSQL
has a negligible effect on TCP-W like workloads, but
a significant improvement on TCP-C like workloads.

"Recall from Section 5.3 that CPU scheduling (CPUPrio)
is more effective than NP-LQ for TPC-W. Thus Figure 8
compares the policies CPU-Prio-Inherit to CPU-Prio for
the TPC-W workload on PostgreSQL.

We find that there is no improvement for CPU-Prio-
Inherit over CPU-Prio. This is to be expected given
the low data contention found in the TPC-W workload; priority
inversions can only occur during data contention. Results
for low-priority transactions are not shown, but as in
Figure 4, low-priority transactions are only negligibly
penalized on average."

Yes, theoretically priority inversion can have pathologically
bad effects (not unlike qsort), it affects some workloads more
than others.

But in particular, their paper concludes that
PostgreSQL with TCP-C and TCP-W like workloads
gain significant benefits and no drawbacks from
indirectly tuning I/O scheduling with setpriority().




If anyone has references to papers or studies that suggest that
priority inversion actually is a problem with RDBMS's - and
PostgreSQL on Linux in particular, I'd be very interested.

Otherwise it seems to me existing research points to
significant benefits with only theoretical drawbacks
in pathological cases.

Josh Berkus wrote:
> All,
>
> The Bizgres project is working on resource management for PostgreSQL. So far,
> however, they have been able to come up with schemes that work for BI/DW at
> the expense of OLTP. Becuase of O^N lock checking issues, resource
> management for OLTP which doesn't greatly reduce overall performance seems a
> near-impossible task.
>

Right - I guess it is probably more correct to say that the
implementation used in Bizgres is specifically targeted at BI/DW
workloads rather than OLTP.

At this point we have not measured its impact on concurrency in anything
other than a handwaving manner - e.g pgbench on an older SMP system
showed what looked like about a 10% hit. However the noise level for
pgbench is typically >10% so - a better benchmark on better hardware is
needed.

Cheers

Mark

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Ron Mayer wrote:
> Short summary:
> * Papers studying priority inversion issues with
> databases including PosgreSQL and realistic workloads
> conclude setpriority() helps even in the presence of
> priority inversion issues for TCP-C and TCP-W like
> workloads.
> * Avoiding priority inversion with priority inheritance
> will further help some workloads (TCP-C) more than
> others (TCP-W) but even without such schedulers
> priority inversion does not cause as much harm
> as the benefit you get from indirectly scheduling
> I/O through setpriority() in any paper I've seen.
>
> Andreas Kostyrka wrote:
>> * Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
>>> Will the setpriority() system call affect i/o queue too?
>> Nope, and in fact the article shows the way not to do it.
>
> Actually *YES* setpriority() does have an indirect effect
> on the I/O queue.
>

While I was at Greenplum a related point was made to me:

For a TPC-H/BI type workload on a well configured box the IO subsystem
can be fast enough so that CPU is the bottleneck for much of the time -
so being able to use setpriority() as a resource controller makes sense.

Also, with such a workload being mainly SELECT type queries, the dangers
connected with priority inversion are considerably reduced.

Cheers

Mark

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Mark Kirkwood wrote:
> Ron Mayer wrote:
>> Short summary:
>> * Papers studying priority inversion issues with
>> databases including PosgreSQL and realistic workloads
>> conclude setpriority() helps even in the presence of
>> priority inversion issues for TCP-C and TCP-W like
>> workloads.
>> * Avoiding priority inversion with priority inheritance
>> will further help some workloads (TCP-C) more than
>> others (TCP-W) but even without such schedulers
>> priority inversion does not cause as much harm
>> as the benefit you get from indirectly scheduling
>> I/O through setpriority() in any paper I've seen.
>>
>> Andreas Kostyrka wrote:
>>> * Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
>>>> Will the setpriority() system call affect i/o queue too?
>>> Nope, and in fact the article shows the way not to do it.
>>
>> Actually *YES* setpriority() does have an indirect effect
>> on the I/O queue.
>>
>
> While I was at Greenplum a related point was made to me:
>
> For a TPC-H/BI type workload on a well configured box the IO subsystem
> can be fast enough so that CPU is the bottleneck for much of the time -
> so being able to use setpriority() as a resource controller makes sense.

Perhaps - but section 4 of the paper in question (pages 3 through 6
of the 12 pages at http://www.cs.cmu.edu/~bianca/icde04.pdf) go
through great lengths to identify the bottlenecks for each workload
and each RDBMS. Indeed for the TCP-W on PostgreSQL and DB2, CPU
was a bottleneck but no so for TCP-C - which had primarily I/O
contention on PostgreSQL and lock contention on DB2.

http://www.cs.cmu.edu/~bianca/icde04.pdf
"for TPC-C ... The main result shown in Figure 1 is that locks
are the bottleneck resource for both Shore and DB2 (rows 1 and
2), while I/O tends to be the bottleneck resource for PostgreSQL
(row 3). We now discuss these in more detail.
...
Thus, CPU is the bottleneck resource for TPC-W 1."

> Also, with such a workload being mainly SELECT type queries, the dangers
> connected with priority inversion are considerably reduced.

And indeed the TCP-W benchmark did not show further improvement
for high priority transactions with Priority Inheritance enabled
in the scheduler (which mitigates the priority inversion problem) -
but the TCP-C benchmark did show further improvement -- which agrees
with Mark's observation. However even with priority inversion
problems; the indirect benefits of setpriority() on I/O scheduling
outweighed the penalties of priority inversion in each of their
test cases.