Обсуждение: Controlling performance impact of multiple users

This is an issue that I have never seen we;; ved with other databases I
have used:

We have an on-line application that is PSQL based. Users want to be able to
get reports from this database - both canned reports that we might write
for them, and ad-hoc reports where they use an odbc-based report generator
and reach into our database.

The issue is how to prevent users from hogging the system, and especially
from slowing down the on-line users. The on-line users are doing either
"heads down" intensive data entry/editing or are working telephone centers.
Hence slowing them down is a *very bad thing.*

  What would be nice is some way to limit a give customer (group of users)
in terms of how much database resource (I/O, CPU) that they use at any one
time.

Is there any way to do anything like this?

Other options we have are:
    scheduling reports to run "after hours"
    replicating the database to a reports-only server and let the report
users fight with each other without impacting the online users.
    charging users for the resources they use, with a steep prime time
charge (this is a very rational approach, but unlikely to be acceptible to
the users)

We thought of "nicing" the users on the theory that if their report
processes can't get much CPU, then they can't make as many database
queries, but in practice this doesn't work because:
    1) A single query from one of the users, if poorly formed, or if the
database doesn't support it well, can generate mammoth scans
         and merges.
    2) Our users are mostly runing ODBC from networked (WAN'd, btw)
desktops. Thus they are immune from these policies.


Any suggestions? Any way other folks have done this (PSQL specific or just
general tricks)?




John Moore

http://www.tinyvital.com/personal.html

UNITED WE STAND

On Thu, 11 Jul 2002, John Moore wrote:

> The issue is how to prevent users from hogging the system, and especially
> from slowing down the on-line users.

Mixing OLTP and OLAP on one database server has never seemed like a good
idea to me. Part of the problem with the idea of resource limitation is
that if someone, say, starts a large update where it becomes apparent
that this is going to use too many resources only part-way into it,
you're kind of stuck. You have to either see it though, or pay the cost
of the rollback, which may be just as expensive by that point.

Thus, I recommend somehow replicating the data from the OLTP server
to another server for the OLAP folks.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

At 09:04 PM 7/14/2002, Curt Sampson wrote:
>On Thu, 11 Jul 2002, John Moore wrote:
>
> > The issue is how to prevent users from hogging the system, and especially
> > from slowing down the on-line users.
>
>Mixing OLTP and OLAP on one database server has never seemed like a good
>idea to me. Part of the problem with the idea of resource limitation is
>that if someone, say, starts a large update where it becomes apparent
>that this is going to use too many resources only part-way into it,
>you're kind of stuck. You have to either see it though, or pay the cost
>of the rollback, which may be just as expensive by that point.


I agree


>Thus, I recommend somehow replicating the data from the OLTP server
>to another server for the OLAP folks.


This is how we did it at a previous employer. I was hoping somebody has
some other trick, since we don't have any easy way to replicate the data!

John

On Mon, 15 Jul 2002, John Moore wrote:

> This is how we did it at a previous employer. I was hoping somebody has
> some other trick, since we don't have any easy way to replicate the data!

Well, if you've got scheduled downtime every day, and can live with
once-a-day updates, you could always shut down both databases and
rsync one to the other.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC