On Oct 5, 2009, at 8:04 AM, Joshua Berry wrote:
Our shop uses postgres for a dozen installations. The applications
have some realtime performance requirements, and are just good enough
to function properly. The problem is that the clients (owners of the
production servers) are using the same server/database for
customizations that are causing problems with the performance of our
applications.
Example of clients' customizations:
* Large tables with text datatypes that get cast in the queries
* No primary keys, indexes, FK constraints
* Use of external scripts that use count(*) from table where id = x,
in a loop from the script, to determine how to construct more queries
later in the same script.
This sounds very familiar.
Is this just for reporting, or does it interact more closely with
the application?
If it's just reporting, then maybe a slony slave server, just for
reporting,
would help.
If it interacts more closely with the application then you're not
going to be able protect them from themselves programatically.
The clients consider themselves experts and don't take
suggestions/criticism well. If we just go ahead and try to port/change
the scripts ourselves, the old code can come back, clobbering the
changes that we made!
If personal feedback isn't going to work (and it sounds like it
isn't) then perhaps hacking up pgfouine a little to create a
dashboard showing where the database resource is going.
That way you have a neutral place to point at when they
complain the app is running slowly, and that "neutral" report
can communicate "you, the customer, are the problem because
your queries suck" in a more objective way.
My question is this: how can we limit the resources to
queries/applications other that what we create and deploy? Are there
any pragmatic options in scenarios like this? We prided ourselves in
having an OSS solution, but it seems that it's become a liability.
If the queries are operating on the same data as the production
app I don't see any good way to segment the IO and CPU resources
that's going to be idiot proof, especially of your customers are
going to demand full database access.
We use PG 8.3 running on a range on Linux Distos.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
