Thread: Overload

Hi,

  I'd like to implement a system to prevent a PostgreSQL database from
being overloaded by delaying queries when the database is already highly
loaded. I.e. the the sum of the execution costs of queries currently in
the database is already near a certain threshold and executing the
"next" query would cause the execution costs to pass this threshold.
  Limiting the number of queries concurrently in the database to a fixed
number n is out of question since - in my opinion - n simple
   SELECT c FROM t WHERE c="..."
  would generally produce a much lower workload than n complex queries.
So, the goal is some more dynamic approach.
  But my problem is to measure the execution costs of a query. My first
thought was to use the estimates of the optimizer but these estimates
only give the time needed to execute the query.
  I know that the term "execution costs" is somewhat imprecise. Ideally,
the value for the execution costs is a value that "merges" the I/O and
the CPU usage used by the query (to be more precise: estimates about the
I/O and CPU usage for the query). I've read the developer manuals but I
didn't find any information on this. Does PostgreSQL offer information
on the additional workload (execution costs) caused by a query? In case
it does not: Does anybody have an idea how I get an estimate for the
execution costs before executing a query?

Thanks in advance

Stefan

Stefan Krompass wrote:
 >
Does PostgreSQL offer information
> on the additional workload (execution costs) caused by a query? In case
> it does not: Does anybody have an idea how I get an estimate for the
> execution costs before executing a query?

I cant add to you question, but two nightly thoughts:

i) if you SQL-server is tortured by some application, its very likely
that you have only a limited range of different select-types. You could
measure the exact costs manually and use this values for your problem.

ii) The workload might depend on you specific system, on your RAM,
harddisk etc.  On one system CPU-speed might be the bottleneck, on
others the RAM and so on. And the cost for a query on your system is not
always the same. Depending on swap, cache and so on.
Maybe you should consider having a second SQL-server to lower load.

best,
peter

--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
pilsl@goldfisch.at