☰

无索引执行的连接：5568

tag-icon • tag-icon mysql sql optimization my.cnf
无索引执行的连接：5568

我尝试进行 mysql 优化。昨天 CPU 使用率达到 100%。我尝试使用 mysqltuner 进行优化，但这次我给出了 mysqltuner 警告。

我有一个高流量的数据库。每天有 500-600 个连接。也许更多。

我该如何修复它？

[!!] 无索引执行的连接：5568

已使用的密钥缓冲区：18.2%（已使用 97M / 536M 缓存）

InnoDB 日志文件大小/InnoDB 缓冲池大小比率（12.5%）：256.0M * 2/4.0G 应等于 25%

InnoDB 写日志效率：70.64% (3619 次命中/ 5123 次总计)

Centos 6 8gb Ram 和 4 个处理器

硬碟：https://pastebin.com/AnFdUHp6

更新：

我的.cnf：https://pastebin.com/g7DbmZ2T

更新：

mysqltuner：https://pastebin.com/HBdSjxaj

全局变量：https://pastebin.com/xTzu2PGM

但 CPU 使用率仍然为 %100。

答案1

Joins performed without indexes: 91。这很可能是你表现不佳的最大元凶。

您的数据库没有针对用于连接多个表的数据的 SQL 查询中使用的列的适当索引。

这意味着 MySQL 需要扫描整个表来获取与查询键匹配的行，而不是仅查看索引数据。

因此，您需要研究您的 SQL 查询，并为连接中使用的表/列添加适当的索引。

答案2

您的配置至少存在两个问题：

  • 过度分配max_connections。您的最高连接使用量为 4，而您分配了 440 个连接，这导致内存过度消耗（每个连接分配 18.5 M，乘以 440 个连接，则使用 8G 内存）。减少max_connections到 20 个并定期监控连接使用情况。
  • 过度分配innodb_buffer_pool_size。您的数据集为 254.3M，您分配了 4G。将其减少到 1G，并在您的数据库至少几天内不重新启动的情况下在负载下运行后对其进行检查。同时重置innodb_buffer_pool_instances为 1。

由于内存过度分配（为 MySQL 分配 12.6G + 1.3G 其他进程内存 > 8G 系统内存），您的系统可能进入交换抖动状态，从而导致 CPU 使用率过高。

答案3

抱歉，第一个答案不太合适——显然我昨天太累了，误解了你的问题。

虽然使用连接池有助于提高应用程序的整体性能，但很可能首先要采取其他措施：

MysqlTuner 建议设置有效的选项：

query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 1.0M, or always use indexes with JOINs)
innodb_log_file_size should be (=528M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)

还要确保mysqld.log定期轮换并检查其中的警告/错误。您可以先删除它并重新启动 mysqld，然后查看从现在开始会打印哪些错误/警告。

您可以测试将大小降低innodb_buffer_pool_size至 1G ( 1073741824) 和innodb_buffer_pool_instances1 是否会对性能产生负面影响。根据 mysqltuner 的说法，缓冲区中只有 254.3M 的数据 - 因此您有足够的增长空间。

@tero-kilkanen 的建议也值得一试。尽管 19K 个查询中只有 58 个缺少匹配索引，但我认为这不会带来很大的性能提升。但无论如何，请检查索引的完整性和包含的键。

要消除警告，即您的 mysqld 可能会使用超过已安装的 RAM 量，您应该将 降低到max_connections一个合理的数字：20 - 30。您的应用程序在 mysqld 正常运行期间最多使用 4。mysqld
分配的最大内存量大致是通过将max_connections(440) 乘以每个连接分配的缓存（主要是 sort_buffer_size 加上一些较小的缓存），然后将全局缓冲区（即innodb_buffer_pool_size）添加到它来计算的。虽然每个连接 18.5MB 听起来不算太多（其实不然） - 将其乘以 440 本身就等于 8GB。

由于您的应用程序主要使用读取语句 (99%)，因此在应用程序端添加缓存可能会减少数据库的总体负载并显著提高前端的性能。
经常查询的、很少改变的值（例如前端用户表）可能会被缓存。然后，您可以在编辑学生/家长用户及其权限时使缓存无效。

如果仍然遇到问题，请在运行 mysqltuner 之前先运行 mysqld 和应用程序更长时间。1-3 天应该会因为更准确的使用情况配置文件而产生更好的建议。

答案4

针对 my.cnf [mysqld] 部分需要考虑的建议每秒速率 = RPS

删除第一行 open_files_limit（您的 my.cnf 中有 2 个）并且仅删除 innodb_open_files=800000 的行，以允许默认值为您的实例工作。

following could be change or ADD line to my.cnf

thread_cache_size=100  # for CAP of 100 per V5.7 refman to reduce threads_created
query_cache_type=0  # for NO query cache to conserve CPU cycles
query_cache_size=0  # from 1M to conserve RAM for more useful purpose
innodb_io_capacity=15000  # from 200 to use more of SSD IOPS capacity
sort_buffer_size=2M  # from 6M to conserve RAM per CONNECTION
read_buffer_size=128K  # from 1M to reduce handler_read_next RPS
join_buffer_size=256K  # from 2M per table join OPS per CONNECTION
table_open_cache=10000  # from 524288 for a practical LIMIT
table_definition_cache=1000  # from 2000 since you have less than 100 tables today
open_files_limit=65536  # from 1049026 million for a practical LIMIT

相关内容