PHP/MySQL:尽管CPU使用率始终低于40％，但是12个CPU内核能否使应用程序比6个CPU内核更快? [英] PHP/MySQL: Can 12 CPU cores make app faster than 6 CPU cores although CPU usage always below 40%?

问题描述

我们的问题是，尽管CPU使用率一直且一直低于40％，但在我们减少了CPU内核之后MySQL延迟了我们的应用程序.我的问题是:确实是CPU减少导致MySQL现在变慢了吗?还是我应该在其他地方寻找?

our problem is that MySQL is delaying our app after we reduced the CPU cores, although CPU usage always is and has been below 40%. My question is: Was it really the CPU reduction which triggered MySQL to be slow now? Or should I be looking somewhere else?

更多详细信息:我的团队正在运行一个移动应用，最多可同时有数千名用户在线.他们每秒向后端触发100个请求.我们正在使用PHP/MySQL，并具有12个CPU内核和8 GB RAM.工具phpMyAdmin显示CPU使用率为15-25％，RAM使用为1 GB.

More details: My team is running a mobile app with up to a couple of thousand users being online at the same time. They fire up to 100 requests/second to the backend. We are using PHP/MySQL and had 12 CPU cores and 8 GB RAM. The tool phpMyAdmin showed that CPU usage was 15-25% and RAM usage at 1 GB.

然后，我们将CPU核心数量减少到6个.CPU使用率最多增加了40％.但是，面对更高的负载(但没有比运行12核时更高的负载)，MySQL无法立即处理所有查询，并且查询排队延迟了我们的整个应用程序.当我们有12个内核时，这种情况不会发生.

Then we reduced CPU cores to 6. CPU usage went up to about 40% max. However, faced with higher load (but not higher load than we had while running 12 cores) MySQL is not able to process all queries immediately and queries are lining up delaying our whole app. This did not happen when we had 12 cores.

对于任何提示或技巧，我将不胜感激.我们已经在对服务器(变量)配置进行全面审查.我们仅使用InnoDB表.

I would be grateful for any hints or tips. We are already conducting a full-review of the server(variable) configuration. We are using only InnoDB-tables.

非常感谢， Fman

Thanks a lot, Fman

推荐答案

在我看来，您的MySQL可能已达到其I/O限制.我们在数据库服务器上进行了压力测试，这对于理解数据库阻塞点很关键，那就是在添加或更改记录时，必须进行磁盘写操作(可以缓存SELECT).因此，当我们刚刚阅读我们的网站时，它处理了很多工作，但是当我们模拟结帐(创建了多个记录)时，它很快就陷入了困境.

It sounds to me like your MySQL is probably reaching it's I/O limits. We did a stress test on our DB server and something that's critical to understanding DB choke points is that when you add or change records, you have to do a disk write (SELECT can be cached). So when we were just reading our website it handled quite a load, but when we simulated a checkout (multiple records created) it bogged down very quickly.

当时，我们没有任何选择. SSD的价格太贵，无法提供磁性产品(我们的扼流圈数量远远超过了我们真正遇到的任何问题).我们能做的最好的事情就是RAID10.但是，由于有了云，您现在可以轻松地(便宜地)拥有一个功能强大的DB服务器，它将更好地处理I/O负载(尤其是SSD价格迅速下降).您会注意到，亚马逊甚至在设置的IOPS上出售其中间实例功能

Back then, we didn't have any options. SSDs were too expensive to provision vs magnetic (and our choke number was way beyond anything we've ever really hit). Best we could do was RAID 10. But, thanks to clouds, you can now easily (and cheaply) have a fairly powerful DB server that will handle the I/O loads much better (especially with SSD prices dropping rapidly). You'll note that Amazon sells even their middle instances on the Provisioned IOPS feature

对于需要快速且一致的I/O性能的任何生产应用程序，我们建议配置IOPS(每秒输入/输出操作)存储.预置的IOPS存储是一种存储类型，可提供快速，可预测和一致的吞吐量性能.创建数据库实例时，请指定IOPS速率和存储空间分配. Amazon RDS规定在数据库实例的生命周期内或直到您对其进行更改之前，IOPS进行速率和存储.预置的IOPS存储针对具有一致性能要求的I/O密集型在线事务处理(OLTP)工作负载进行了优化.

For any production application that requires fast and consistent I/O performance, we recommend Provisioned IOPS (input/output operations per second) storage. Provisioned IOPS storage is a storage type that delivers fast, predictable, and consistent throughput performance. When you create a DB instance, you specify an IOPS rate and storage space allocation. Amazon RDS provisions that IOPS rate and storage for the lifetime of the DB instance or until you change it. Provisioned IOPS storage is optimized for I/O intensive, online transaction processing (OLTP) workloads that have consistent performance requirements.

听起来非常像您可能有一个简单的设置(甚至可能将数据库托管在同一服务器上). AWS不是市面上唯一的游戏，但您可能要考虑迁移到某种类型的云，或者至少要升级到可以处理您对服务器提出的更高I/O要求的事物.忘记核心数.如果您的HDD令人窒息，则您可以运行72个内核，而没有任何区别.

It sounds very much like you probably have a simple setup (and maybe even host your DB on the same server). AWS isn't the only game in town but you might want to consider moving to some sort of cloud, or at least step up to something that can handle the higher I/O demands you're putting on your server. Forget the number of cores. If your HDD is choking you could be running 72 cores and not have any difference whatsoever.

另一个提示:请查看 MySQL Tuner .当您运行它时，它会检查您的数据库统计信息(运行时间越长越好)，并且可以推荐许多有用的调整措施，这些调整措施也可以提高性能.

One other tip: Check out MySQL Tuner. When you run it, it checks your database stats (the longer it's been running, the better) and can recommend lots of helpful tweaks that can also improve performance.

这篇关于PHP/MySQL:尽管CPU使用率始终低于40％，但是12个CPU内核能否使应用程序比6个CPU内核更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！