对各种数据库技术进行自动化性能测试的可行方法？
该网站上的很多人都指出：“优化性能是万恶之源”。我现在的问题是我有很多复杂的 SQL 查询，其中许多查询利用 PL/pgSQL 或 PL/python 中用户创建的函数。我的问题是，我没有任何性能分析工具来向我展示哪些函数实际上使查询变慢。我当前的方法是排除各种函数并花时间查询每个函数。我知道我也可以使用解释分析，但我认为它不会为我提供有关用户创建的函数的信息。
我当前的方法非常乏味，特别是因为 PostgreSQL 中没有查询进度，所以如果我选择在太多数据上运行查询，有时我必须等待查询运行 60 秒。
因此，我在想创建一个工具是否是一个好主意，它可以通过修改 SQL 查询来自动对 SQL 查询进行性能分析，并获取其各个版本的实际处理时间。每个版本都是一个简化版本，可能只包含一个用户创建的函数。我知道我没有清楚地描述如何做到这一点，而且我可以想到很多复杂的因素，但我也可以看到其中许多因素都有解决方法。我基本上需要你的直觉来判断这种方法是否可行。
另一个类似的想法是运行查询，将服务器设置 work_mem 设置为各种值，并显示这将如何影响性能。
这样的工具可以使用 JDBC 编写，因此可以对其进行修改以适用于所有主要数据库。在这种情况下，它可能是一个可行的商业产品。
A lot of guys on this site state that: "Optimizing something for performance is the root of all evil". My problem now is that I have a lot of complex SQL queries, many of them utilizing user created functions in PL/pgSQL or PL/python. My problem is that I do not have any performance profiling tool to show me, which functions actually make the queries slow. My current method is to exclude the various functions and take the time on the query for each one. I know that I could use explain analyze as well, but I do not think it will provide me with the information about user created functions.
My current method is quite tedious, especially since there is not query progress in PostgreSQL so I have sometimes have to wait for the query to run for 60 seconds, if I choose to run it on too much data.
Therefore, I am thinking whether it could be a good idea to create a tool, which will automatically do a performance profiling of SQL queries by modifying the SQL query and take the actual processing time on various versions of it. Each version would be a simplified one, which would maybe just contain a single user created function. I know that I am not describing how to do this clearly, and I can think of a lot of complicating factors, but I can also see that there are workarounds for many of these factors. I basically need your gut feeling on whether such a method is feasible.
Another similar idea is to run the query setting server settings work_mem to various values, and showing how this would impact the performance.
Such a tool could be written using JDBC so it could be modified to work across all major databases. In this case it might be a viable commercial product.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Apache JMeter 可用于负载测试和监视 SQL 查询的性能（使用 JDBC）。但它不会修改您的 SQL。
实际上，我认为没有任何工具可以简化然后重新运行 SQL。 “简化”应该如何进行？
Apache JMeter can be used to load test and monitor the performance of SQL Queries (using JDBC). It will howerever not modify your SQL.
Actually I don't think any tool out there could simplify and then re-run your SQL. How should that "simplifying" work?