如何限制客户端修改对生产系统的影响
我们的商店已经为十几个客户端安装开发了一些WEB/SMS/DB解决方案。这些应用程序有一些实时性能要求，并且足以正常运行。问题在于，客户端（生产服务器的所有者）使用相同的服务器/数据库进行自定义，这会导致我们创建和部署的应用程序的性能出现问题。
客户端自定义的一些示例：
- 为查询中转换为其他数据类型的列添加包含许多文本数据类型的大型表
- 无主键、索引或 FK 约束
- 使用使用
count(*) 的外部脚本from table where id = x，在脚本循环中，以确定稍后如何在同一脚本中构造更多查询。 （规划者无法优化批量操作，或者只是一次完成所有操作） - 服务器上的所有新代码文件均由 root 创建/拥有，具有 0777 权限
客户端不能很好地接受建议/批评。如果我们继续尝试自己移植/更改脚本，旧代码可能会回来，破坏我们所做的任何更改！或者，由于对他们的用例了解有限，我们在尝试优化他们的更改时破坏了功能。
我的问题是：除了我们创建和部署的资源之外，我们如何限制查询/应用程序的资源？在这样的情况下有什么实用的选择吗？我们为拥有 OSS 解决方案而感到自豪，但它似乎已成为一种负担。
我们使用在 Linux Distos 上运行的 PG 8.3。客户更喜欢 php，但 shell 脚本、perl、python 和 plpgsql 都以一种或另一种形式在系统上使用。
Our shop has developed a few WEB/SMS/DB solution for a dozen client installations. The applications have some real-time 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 the applications that we created and deployed.
A few examples of clients' customizations:
- Adding large tables with many text datatypes for the columns that get cast to other data types in the queries
- No primary keys, indexes, or 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. (no bulk actions that the planner can optimize or just do everything in a single pass) - All new code files on the server are created/owned by root, with 0777 permissions
The clients 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 any changes that we make! Or with out limited knowledge of their use cases, we break functionality while trying to optimize their changes.
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. The clients prefer php, but shell scripts, perl, python, and plpgsql are all used on the system in one form or another.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这个问题在第一个客户端获得对第一台计算机的完全访问权限后大约两分钟开始出现，此后一直没有消失。任何时候，如果一个人的首要任务是快速完成面向业务的工作，他们就会马虎行事，把每个人的事情都搞砸。这就是事情的运作方式，因为正确的设计和实施比廉价的黑客更难。你不会解决这个问题，你所能做的就是弄清楚如何让客户更容易与你合作而不是反对你。如果你做得对，它会看起来像是优质的服务而不是唠叨。
首先，数据库方面。现在有方法可以控制 PostgreSQL 中的查询资源。主要困难在于像“nice”这样的工具可以控制 CPU 使用率，但如果数据库无法容纳在 RAM 中，那么很可能是 I/O 使用率让你丧命。请参阅此 开发者消息总结了此处的问题。
现在，如果实际上客户端正在消耗 CPU，则可以使用两种技术来改善这种情况：
听起来您的问题不是他们正在运行的特定查询进程，而是他们对更大的结构进行的其他修改。只有一种方法可以解决这个问题：您必须像对待入侵者一样对待客户，并使用计算机安全领域的该部分的方法来检测他们何时搞砸了。严重地！在服务器上安装像 Tripwire 这样的入侵检测系统（还有更好的工具，这只是典型的例子），并让它在它们触摸任何东西时向您发出警报。新文件是0777？应该直接跳出正确的 IDS 报告。
在数据库方面，您无法直接检测到正在被有用修改的数据库。您应该每天将模式 pg_dump 写入一个文件（pg_dumpall -g 和 pg_dump -s，然后将其与您交付的最后一个文件进行比较，并在何时再次提醒您如果你处理得这么好，与客户的联系就会变成“我们注意到你在服务器上发生了变化……你想用它来完成什么？”关注他们可能会变成一个销售机会，他们可能会停止摆弄事情，因为你知道你会立即抓住它，
你应该立即开始安装尽可能多的版本控制软件。您应该能够登录每个系统，运行适当的状态/差异工具进行安装，并定期将其邮寄给您，这效果最好。将模式转储为它所管理的内容的组件。没有足够的人对数据库中的代码使用严格的版本控制方法。
这是这里有用的主要技术方法。剩下的就是一个经典的咨询客户管理问题，它更多的是人的问题，而不是计算机的问题。振作起来，情况可能会更糟 - 如果您为他们提供 ODBC 访问权限，并且他们发现可以在 Access 中编写自己的查询或类似的简单内容，那么 FSM 会帮助您。
This problem started about two minutes after the first client was given full access to the first computer, and it hasn't gone away since. Anytime someone whose priorities are getting business oriented work done quickly they will be sloppy about it and screw up things for everyone. That's just how things work, because proper design and implementation are harder than cheap hacks. You're not going to solve this problem, all you can do is figure out how to make it easier for the client to work with you than against you. If you do it right, it will look like excellent service rather than nagging.
First off, the database side. There's now way to control query resources in PostgreSQL. The main difficulty is that tools like "nice" control CPU usage, but if the database doesn't fit in RAM it may very well be I/O usage that is killing you. See this developer message summarizing the issues here.
Now, if in fact it's CPU the clients are burning through, you can use two techniques to improve that situation:
It sounds like your problem isn't the particular query processes they're running, but rather other modifications they're making to the larger structure. There's only one way to cope with that: you have to treat the client like they're an intruder and use the approaches of that portion of the computer security field to detect when they screw things up. Seriously! Install an intrusion detection system like Tripwire on the server (there are better tools, that's just the classic example), and have it alert you when they touch anything. New file that's 0777? Should jump right out of a proper IDS report.
On the database side, you can't directly detect the database being modified usefully. You should do a pg_dump of the schema every day into a file (pg_dumpall -g and pg_dump -s, then diff that against the last one you delivered and again alert you when it's changed. If you manage that this well, the contact with the client turns into "we noticed you changed on the server...what is it you're trying to accomplish with that?" which makes you look like you're really paying attention to them. That can turn into a sales opportunity, and they may stop fiddling with things as much just knowing you're going to catch it immediately.
The other thing you should start doing immediately is install as much version control software as you can on each client box. You should be able to login to each system, run the appropriate status/diff tool for the install, and see what's changed. Get that mailed to you regularly too. Again, this works best if combined with something that dumps the schema as a component to what it manages. Not enough people use serious version control approaches on the code that lives in the database.
That's the main set of technical approaches useful here. The rest of what you've got is a classic consulting client management problem that's far more of a people problem than a computer one. Cheer up, it could be worse--FSM help you if you give them ODBC access and they discover they can write their own queries in Access or something simple like that.