Обсуждение: Limiting the impact of schema additions/poor queries made by clients on production machines
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. 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! 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. We use PG 8.3 running on a range on Linux Distos. Regards, -Joshua P.S. I've cross-posted this on stackoverflow; I would love to hear any stories or practical advice in this area, but I don't want to clutter the pgsql-general list with non-pg advice. Here's the link: http://stackoverflow.com/questions/1520645/how-to-limit-the-effect-of-client-modifications-to-production-systems
On Mon, Oct 5, 2009 at 9:04 AM, Joshua Berry <yoberi@gmail.com> 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. Can you use londiste or slony to make a replicant for them to run these things on so they don't affect the main production server? > 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! It's pretty obvious that they are not only NOT experts, but also unprofessional as well. If they won't cooperate, then I'd suggest making it clear you're not going to make fixing their mistakes a priority, and then proceed to give them enough rope to hang themselves with. > 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. Even a Big Commercial DBMS can only hold back the clown patrol for so long. If they're good enough at being bad users they can cause problems. Besides, these guys sound like they'd demand admin access to any db you gave them. You customer is your primary liability, not your toolset. you make it more idiot proof, they become better idiots. It's an arms race against stupidity, and you can't really win one of those. I'd take away their access if you can. They're idiots.
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