mysql - Azure Mysql 保持高 CPU 使用率,我无法调低它
问题描述
我有一个使用 Azure Mysql 作为数据库服务器的 asp.net 应用程序。在我的应用程序中有一些线程可以查询 mysql 数据库。它总是导致 Azure Mysql 数据库服务器 CPU 使用率高达 99%。然后我尝试编辑 Azure MySQL 的服务器参数。我调整如下:
innondb_buffer_pool_size : from 16106127360 bytes to 134217728 bytes.
innodb_thread_concurrency: from 0 to 33
wait_timeout: from 120 to 30
interactive_timeout: from 28800 to 30
但它没有用。当我运行应用程序时。Azure MySQL 的 CPU 使用率仍然使用 99%。上面的调整似乎没有用。我应该做哪些调整来降低 Azure MySQL 的 CPU 使用率?
解决方案
观察:
- 版本:5.7.22-日志
- 16 GB 内存
- 正常运行时间 = 16:50:57;一些 GLOBAL STATUS 值可能还没有意义。
- 您正在 Windows 上运行。
- 运行 64 位版本
- 您似乎完全(或大部分)运行 InnoDB。
更重要的问题:
tmp_table_size
不应超过 RAM 的 1%。否则,一连串的复杂查询可能会导致交换,这对性能来说是很糟糕的。
一些指标表明缺乏足够的索引。有关查找这些查询,请参阅http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog。请参阅http://mysql.rjweb.org/doc.php/index_cookbook_mysql为它们设计索引。
innodb_buffer_pool_size
应设置为可用RAM 的 70% 左右——10G。但是,由于 buffer_pool 大部分是空的,因此提高该值将没有用。另一方面,如果您的数据可能会增长,请记住此设置。
如果使用 SSD,innodb_flush_neighbors = 0
你使用 BEGIN...COMMIT 吗?还是自动提交=开启?
60 线程_正在运行?他们可能正在互相绊倒。(1) 加快查询速度,(2) 在客户端节流。
myisam_sort_buffer_size
——危险的大;改为300M
不使用通用日志时关闭它——它往往会很快填满磁盘。
细节和其他观察:
( innodb_buffer_pool_size / _ram ) = 2048M / 16384M = 12.5%
-- 用于 InnoDB buffer_pool 的 RAM 百分比
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096
-- 页面清理器每秒的工作量。-- “InnoDB: page_cleaner: 1000ms 预期循环占用了......”可以通过降低 lru_scan_depth 来修复:考虑 1000 / innodb_page_cleaners(现在是 4)
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5
-- innodb_page_cleaners -- 建议将 innodb_page_cleaners (现在 4) 设置为 innodb_buffer_pool_instances (现在 8)
( innodb_lru_scan_depth ) = 1,024
-- “InnoDB: page_cleaner: 1000ms 预期循环占用了......”可以通过降低 lru_scan_depth 来修复
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 120,208 * 16384 / 2048M = 91.7%
-- 缓冲池空闲 -- buffer_pool_size 大于工作集;可以减少它
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 120,208 / 131072 = 91.7%
-- 当前未使用的 buffer_pool 的 Pct -- innodb_buffer_pool_size(现在为 2147483648)是否大于所需?
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 175,161,344 / 2048M = 8.2%
-- 数据占用缓冲池的百分比 -- 小百分比可能表明 buffer_pool 过大。
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 60,657 / 60 * 48M / 10239488 = 4,969
-- InnoDB 日志轮换之间的分钟数从 5.6.8 开始,可以动态更改;请务必同时更改 my.cnf。-- (轮换间隔 60 分钟的建议有些武断。)调整 innodb_log_file_size(现在为 50331648)。(不能在 AWS 中更改。)
( innodb_flush_method ) = innodb_flush_method =
-- InnoDB 应该如何要求操作系统写入块。建议使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 以避免双重缓冲。(至少对于 Unix。)有关 O_ALL_DIRECT 的警告,请参阅 chrischandler
( innodb_flush_neighbors ) = 1
-- 将块写入磁盘时的小优化。-- 使用 0 表示 SSD 驱动器;1 用于硬盘。
( innodb_io_capacity ) = 200
- 磁盘上每秒的 I/O 操作数。100 用于慢速驱动器;200 用于旋转驱动器;SSD 1000-2000;乘以 RAID 系数。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF
-- 是否记录所有死锁。-- 如果你被死锁困扰,打开它。注意:如果你有很多死锁,这可能会写入很多磁盘。
( join_buffer_size / _ram ) = 262,144 / 16384M = 0.00%
-- 每个线程 0-N。可以加快 JOIN(更好地修复查询/索引)(所有引擎)用于索引扫描、范围索引扫描、全表扫描、每个完整 JOIN 等。 -- 如果很大,请减小 join_buffer_size(现在为 262144)以避免内存压力. 建议少于 1% 的 RAM。如果很小,则将 RAM 增加到 0.01% 以改进一些查询。
( myisam_sort_buffer_size / _ram ) = 4096M / 16384M = 25.0%
-- 用于 ALTER、CREATE INDEX、OPTIMIZE、LOAD DATA;需要时设置。也适用于 MyISAM 的修复表。-- 减少 myisam_sort_buffer_size(现在为 4294967296)以防止内存溢出。
( character_set_server ) = character_set_server = utf8
-- 将 character_set_server(现在是 utf8)设置为 utf8mb4 可以帮助解决字符集问题。那是未来的默认值。
( net_buffer_length / max_allowed_packet ) = 16,384 / 10M = 0.16%
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON 是一个潜在的安全问题
( tmp_table_size ) = 2048M
-- 限制用于支持 SELECT 的MEMORY临时表的大小 -- 减少 tmp_table_size(现在为 2147483648)以避免内存不足。也许不超过64M。
( Handler_read_rnd_next / Com_select ) = 7,170,628,371 / 95062 = 75,431
-- 每个 SELECT 扫描的平均行数。(大约)——考虑提高 read_buffer_size(现在为 65536)
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (14560 + 0 + 33 + 0) / 0 = INF
-- 每个提交的语句(假设所有 InnoDB) -- 低:可能有助于在事务中将查询分组;高:长期交易使各种事情紧张。
( Select_full_join / Com_select ) = 55,648 / 95062 = 58.5%
-- 无索引连接的选择百分比 -- 为 JOIN 中使用的表添加合适的索引。
( Select_scan ) = 247,281 / 60657 = 4.1 /sec
-- 全表扫描 -- 添加索引/优化查询(除非它们是小表)
( Select_scan / Com_select ) = 247,281 / 95062 = 260.1%
-- 选择进行全表扫描的百分比。(可能被存储例程愚弄。)——添加索引/优化查询
( slow_query_log ) = slow_query_log = OFF
-- 是否记录慢查询。(5.1.12)
( Com_change_db / Connections ) = 39,710 / 168 = 236
-- 每个连接的数据库切换 --(次要)考虑使用“db.table”语法
( Threads_running - 1 ) = 61 - 1 = 60
-- 活动线程(收集数据时的并发) -- 优化查询和/或模式
( Threads_created / Connections ) = 149 / 168 = 88.7%
-- 进程创建速度 -- 增加thread_cache_size(现在为10)(非Windows)
( Threads_running / thread_cache_size ) = 61 / 10 = 6.1
-- 线程:当前/缓存(使用线程池时不相关)- 优化查询
您有一半的查询缓存。您应该同时设置 query_cache_type = OFF 和 query_cache_size = 0 。(根据传言)QC 代码中有一个“错误”,除非您关闭这两个设置,否则某些代码会保持打开状态。
异常小:
10 * read_buffer_size = 0.6MB
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 2.7%
Innodb_rows_updated = 0
Key_reads+Key_writes + Innodb_pages_read+Innodb_pages_written+Innodb_dblwr_writes+Innodb_buffer_pool_pages_flushed = 0.41 /sec
异常大:
Com_create_table = 64 /HR
Com_drop_table = 0.035 /sec
Com_show_create_proc = 4.6 /HR
Com_show_status = 1.2 /sec
Handler_read_rnd_next / Handler_read_rnd = 36,149
Innodb_log_writes / Innodb_log_write_requests = 152.3%
Innodb_os_log_pending_writes = 1
Sort_range = 88 /sec
Threads_running = 61
myisam_sort_buffer_size = 4096MB
net_read_timeout = 999,999
net_write_timeout = 999,999
异常字符串:
ft_boolean_syntax = + -><()~*:&
general_log = ON
have_crypt = NO
innodb_fast_shutdown = 1
log_syslog = ON
lower_case_file_system = ON
lower_case_table_names = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
session_track_system_variables = time_zone, autocommit, character_set_client, character_set_results, character_set_connection
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
推荐阅读
- python - 刮问题。为什么没有数据被抓取?
- javascript - 为什么我的 SCSS 文件在导入后没有应用到我的 React 中?
- flutter - Flutter 和 Dart 扩展
- kotlin - 在 Kotlin 中正确使用 Number 类
- android - 我在E盘安装了flutter,但在C盘安装了android studio。它会起作用吗?
- javascript - Angular 指令如何工作?(ng*For) 为例
- javascript - 使用 react 获取 json api ( useeffect )
- sqlite - 在 Tcl 中加入包含希伯来语的列表?
- sqlite - 添加连接条件时 SQLite TOTAL 查询增加
- oracle - 使用 PutSQL 插入 Oracle 表会返回 sql.generated.key 属性的 rowid 值,而不是生成的序列 id