如何对真正数据库密集型的 Rails 操作进行基准测试和优化？
客户站点的管理部分中有一个操作，例如 Admin::Analytics（我没有构建但必须维护），它通过执行几十个相当密集的数据库查询来编译站点使用分析。每当编译分析报告时，此功能始终是应用程序性能的瓶颈。但是，最近的瓶颈变得如此严重，以至于当访问时，该网站会突然停止并无限期挂起。直到昨天，我还没有理由在服务器上运行“top”命令，但这样做我意识到 Admin::Analytics#index 会导致 mysqld 以 350+% CPU 功率旋转四核生产型 VPS。
我已经下载了生产数据和生产日志的新副本。然而，当我在开发盒上本地访问 Admin::Analytics#index 时，同时使用生产数据，它会在大约 10 - 12 秒内加载（并利用我的双核 CPU 的约 150+%），遗憾的是这是正常的。我想可能是mysql设置之间突然出现了差异。此外，数据库的 mysqldump 现在为 531 MB，而 28 天前仅为 336 MB。无论如何，我没有 VPS 上的 root 访问权限，因此调整 mysqld 性能会很麻烦，我真的很想找出这个问题的确切原因。但是，生产日志不包含信息。关于查询；它们只是报告这些请求所花费的时间，平均每个请求需要几分钟（尽管它们似乎导致 mysqld 停滞的时间比这个长得多，并提示我请求我们的主机重新启动 mysqld 只是为了让我们的站点恢复）在一种情况下）。
我想我可以尝试提高生产中的日志级别以获取信息。在 Admin::Analytics#index 执行的数据库查询上，但同时我害怕在生产中复制这种行为，因为我不想调用我们的主机来再次重新启动 mysqld！此操作在其控制器中包含一个数据库请求，并在其视图中嵌入了几十个准备好的语句！
您将如何继续进行基准测试/诊断并优化/修复此操作？！
（旁白：显然我想用 Google Analytics 或类似的解决方案完全替换此功能，但我需要在继续之前解决此问题。）
There is an action in the admin section of a client's site, say Admin::Analytics (that I did not build but have to maintain) that compiles site usage analytics by performing a couple dozen, rather intensive database queries. This functionality has always been a bottleneck to application performance whenever the analytics report is being compiled. But, the bottleneck has become so bad lately that, when accessed, the site comes to a screeching halt and hangs indefinitely. Until yesterday I never had a reason to run the "top" command on the server, but doing so I realized that Admin::Analytics#index causes mysqld to spin at upwards of 350+% CPU power on the quad-core, production VPS.
I have downloaded fresh copies of production data and the production log. However, when I access Admin::Analytics#index locally on my development box, while using the production data, it loads in about 10 - 12 seconds (and utilizes ~ 150+% of my dual-core CPU), which sadly is normal. I suppose there could be a discrepancy in mysql settings that has suddenly come into play. Also, a mysqldump of the database is now 531 MB, when it was only 336 MB 28 days ago. Anyway, I do not have root access on the VPS, so tweaking mysqld performance would be cumbersome, and I would really like to get to the exact cause of this problem. However, the production logs don't contain info. on the queries; they merely report the length that these requests took, which average out to a few minutes apiece (although they seemed to have caused mysqld to stall for much longer than this and prompting me to request our host to reboot mysqld just to get our site back up in one instance).
I suppose I can try upping the log level in production to solicit info. on the database queries being performed by Admin::Analytics#index, but at the same time I'm afraid to replicate this behavior in production because I don't feel like calling our host up to restart mysqld again! This action contains a single database request in its controller, and a couple dozen prepared statements embedded in its view!
How would you proceed to benchmark/diagnose and optimize/fix this action?!
(Aside: Obviously I would like to completely replace this functionality with Google Analytics or a similar solution, but I need fix this problem before proceeding.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我建议看一下这篇文章：
http://axonflux.com/building-and-scaling-a-startup
特别是，query_reviewer 和 newrelic 对我来说是救星。
I'd recommend taking a look at this article:
http://axonflux.com/building-and-scaling-a-startup
Particularly, query_reviewer and newrelic have been a life-saver for me.
我很感谢这方面的所有帮助，但事实证明，解决这个问题的办法是在 Analytics 表上实现几个索引，以满足此操作中的查询。用于添加索引和操作的简单 Rails 迁移现在可以在不到一秒的时间内加载到我的开发盒和产品上！
I appreciate all the help with this, but what turned out to be the fix for this was to implement a couple of indexes on the Analytics table to cater to the queries in this action. A simple Rails migration to add the indexes and the action now loads in less than a second both on my dev box and on prod!