Innodb 的性能改进 - 高 CPU

Question 1

每秒速率 = RPS

针对您的操作系统和 my.cnf [mysqld] 部分的建议

对于你的操作系统，ulimit -n 32768 # 增加打开文件限制以满足你的负载和其他应用程序的需求

为了使此功能在操作系统停止/启动时持续存在，此 URL 是类似系统的基本指南。 https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ 其中示例有 500000，请使用 32768。

对于您的 my.cnf [mysqld] 部分，

read_rnd_buffer_size=32K  # from 256K to reduce handler_read_rnd_next RPS of 413,713
read_buffer_size=256K  # from 128K to reuce handler_read_next RPS of 2,197
innodb_log_file_size=5G  # from 256M to support ~ 1 hr before rotation to new log
innodb_log_buffer_size=3G  # from 128NM to support ~ 30 min before write to media
innodb_flush_neighbors=2  # from 1 to flush rows in 1 sweep to current extent and this will keep dirty pages minimized.
innodb_thread_concurrency=4  # from 0 to allow other apps access when needed

观察：应检查您的准备和执行存储过程的策略。通常，一个准备将支持许多执行，然后才需要另一个准备。应检查您的执行和关闭（或释放）存储过程的策略。我们发现，最佳成功方法是执行后关闭以释放完成存储过程执行所需的资源。您有许多悬而未决的执行。在 4 小时的正常运行时间内，发生了 355,271 个 com_create_table 事件。您可以用这些新创建的表做什么？4 小时内 Select_scan 计数为 37,017,333（2,514 RPS），这告诉我您确实需要索引来避免以这种速率进行表扫描。

请查看个人资料以获取联系信息和免费实用脚本，并通过电子邮件联系以查看任何详细信息。

Answer

每秒速率 = RPS

针对您的操作系统和 my.cnf [mysqld] 部分的建议

对于你的操作系统，ulimit -n 32768 # 增加打开文件限制以满足你的负载和其他应用程序的需求

为了使此功能在操作系统停止/启动时持续存在，此 URL 是类似系统的基本指南。 https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ 其中示例有 500000，请使用 32768。

对于您的 my.cnf [mysqld] 部分，

read_rnd_buffer_size=32K  # from 256K to reduce handler_read_rnd_next RPS of 413,713
read_buffer_size=256K  # from 128K to reuce handler_read_next RPS of 2,197
innodb_log_file_size=5G  # from 256M to support ~ 1 hr before rotation to new log
innodb_log_buffer_size=3G  # from 128NM to support ~ 30 min before write to media
innodb_flush_neighbors=2  # from 1 to flush rows in 1 sweep to current extent and this will keep dirty pages minimized.
innodb_thread_concurrency=4  # from 0 to allow other apps access when needed

观察：应检查您的准备和执行存储过程的策略。通常，一个准备将支持许多执行，然后才需要另一个准备。应检查您的执行和关闭（或释放）存储过程的策略。我们发现，最佳成功方法是执行后关闭以释放完成存储过程执行所需的资源。您有许多悬而未决的执行。在 4 小时的正常运行时间内，发生了 355,271 个 com_create_table 事件。您可以用这些新创建的表做什么？4 小时内 Select_scan 计数为 37,017,333（2,514 RPS），这告诉我您确实需要索引来避免以这种速率进行表扫描。

请查看个人资料以获取联系信息和免费实用脚本，并通过电子邮件联系以查看任何详细信息。

Question 2

我听到多个问题：

如何每秒将 50 多行数据从物联网提取到慢速 HDD 上。
如何将“旧”数据从 SSD 迁移到 HDD。
如何处理大量表格（每个物联网一个表格）。

我不确定这些是否真的是你的目标，但让我从它们开始。

批量插入（或不）

为了实现高速采集，最好收集多行数据，然后将它们插入到单个 INSERT 语句中。100 行的批次插入速度大约是 100 个单行插入的 10 倍。

但如果插入内容是分开的，这可能不太实用。尤其是当它们要插入到不同的表中时。

好吧，“每个物联网一张表”有很多不好的原因；我将在下面介绍这一点。

在数据仓库的“事实”表中插入一行可能涉及规范化、索引更新等。这会增加每秒 50 行。因此，我建议将一秒钟内的所有数据收集到临时表中（一次一个，没有索引，没有规范化），然后将它们从该表中发送到“真实”事实表和摘要表中。我在高速摄取

归档旧数据

通过按日期分区，可以删除表的一块内容并将其复制到另一个表、磁盘或机器。这涉及为所有数据创建一个单独的表，但保留它PARTITION BY RANGE(TO_DAYS(...))。然后可以将“旧”分区“传输”到另一个位置。这涉及一些特殊命令来将其从现有分区表中删除等。请参阅分割——时间序列讨论和可传输表空间。

单表

请注意，我两次提到了表结构。对于物联网（或类似的数据仓库应用程序），我建议采用以下特征：

单一事实表
按照以下方式对其进行索引： PRIMARY KEY(sensor, datetime)，再加上很少的其他索引。sensor在 PK 中拥有第一个索引对许多可能的查询都有好处。
汇总表用于对每小时或每天的数据进行计数、平均、求和等。此类表对于报告、图表等来说将更快。此外，它们可以轻松拥有更多索引 - 汇总表上的索引成本较低，而事实表上的索引成本较高。
规范化——任何重复次数较多的长字符串都会被放入查找表中，而不是存储在事实表中。相反，SMALLINT UNSIGNED会使用（2 个字节，范围为 0..64K）或其他大小的 INT 来代替它。摘要表可能需要“非规范化”。批量插入可以“批量规范化”以提高效率。
汇总表
传感器表[没做完]

Answer

我听到多个问题：

如何每秒将 50 多行数据从物联网提取到慢速 HDD 上。
如何将“旧”数据从 SSD 迁移到 HDD。
如何处理大量表格（每个物联网一个表格）。

我不确定这些是否真的是你的目标，但让我从它们开始。

批量插入（或不）

为了实现高速采集，最好收集多行数据，然后将它们插入到单个 INSERT 语句中。100 行的批次插入速度大约是 100 个单行插入的 10 倍。

但如果插入内容是分开的，这可能不太实用。尤其是当它们要插入到不同的表中时。

好吧，“每个物联网一张表”有很多不好的原因；我将在下面介绍这一点。

在数据仓库的“事实”表中插入一行可能涉及规范化、索引更新等。这会增加每秒 50 行。因此，我建议将一秒钟内的所有数据收集到临时表中（一次一个，没有索引，没有规范化），然后将它们从该表中发送到“真实”事实表和摘要表中。我在高速摄取

归档旧数据

通过按日期分区，可以删除表的一块内容并将其复制到另一个表、磁盘或机器。这涉及为所有数据创建一个单独的表，但保留它PARTITION BY RANGE(TO_DAYS(...))。然后可以将“旧”分区“传输”到另一个位置。这涉及一些特殊命令来将其从现有分区表中删除等。请参阅分割——时间序列讨论和可传输表空间。

单表

请注意，我两次提到了表结构。对于物联网（或类似的数据仓库应用程序），我建议采用以下特征：

单一事实表
按照以下方式对其进行索引： PRIMARY KEY(sensor, datetime)，再加上很少的其他索引。sensor在 PK 中拥有第一个索引对许多可能的查询都有好处。
汇总表用于对每小时或每天的数据进行计数、平均、求和等。此类表对于报告、图表等来说将更快。此外，它们可以轻松拥有更多索引 - 汇总表上的索引成本较低，而事实表上的索引成本较高。
规范化——任何重复次数较多的长字符串都会被放入查找表中，而不是存储在事实表中。相反，SMALLINT UNSIGNED会使用（2 个字节，范围为 0..64K）或其他大小的 INT 来代替它。摘要表可能需要“非规范化”。批量插入可以“批量规范化”以提高效率。
汇总表
传感器表[没做完]

Question 3

全球状况和变量的分析：

观察结果：

版本：10.3.27-MariaDB-0+deb10u1
16 GB RAM
正常运行时间 = 04:05:27；一些 GLOBAL STATUS 值可能尚无意义。
1.58e+4 查询/秒：335 个问题/秒

更重要的问题：

下面的一些指标表明更多的 RAM 和更大的 innodb_buffer_pool_size 会有所帮助。

看看是否可以重复使用准备好的语句，而不是关闭其中的 98%。

建议的更改：

key_buffer_size = 30M  -- lowered since MyISAM is not being used
innodb_io_capacity = 1000  -- if disk is SSD
innodb_flush_neighbors = 0  -- if SSD
innodb_log_file_size -- Caution; this is complex to change
innodb_change_buffer_max_size = 25 -- unless you have a reason for "50"
query_cache_type = OFF
query_cache_size = 0

详细信息和其他观察结果：

( Key_blocks_used * 1024 / key_buffer_size ) = 2 * 1024 / 128M = 0.00%-- key_buffer 的使用百分比。高水位线。-- 降低 key_buffer_size（现在为 134217728）以避免不必要的内存使用。

( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (2 + 0 + 342390 + 3638550 + 295730 + 3638550) / 14727 = 537 /sec-- IOPs？--如果硬件可以处理，请将 innodb_io_capacity（现在为 500）设置为大约这个值。

( ( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / innodb_io_capacity / Uptime ) = ( 2 + 0 + 342390 + 3638550 + 295730 + 3638550 ) / 500 / 14727 = 107.5%- 这可能是一个指示 innodb_io_capacity 是否合理设置的指标。——如果硬件可以处理，则增加 innodb_io_capacity（现在为 500）。

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 500 = 4-- 容量：最大/普通 -- 推荐 2。最大容量应大约等于 I/O 子系统可以处理的 IOP。（如果驱动器类型未知，2000/200 可能是合理的一对。）

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((331721 + 3638550) ) / 14727 = 269 /sec-- InnoDB I/O -- 增加 innodb_buffer_pool_size（现在是 10737418240）？

( Innodb_buffer_pool_pages_flushed ) = ((331721 + 3638550) ) / 14727 = 247 /sec-- 写入（刷新）-- 增加 innodb_buffer_pool_size（现在为 10737418240）？

( innodb_change_buffer_max_size ) = 50- 用于“更改缓冲区”的缓冲池的百分比 - 用于索引更改的写入缓存。 - 该值不应太小，以免妨碍延迟写入，也不应太大，以免干扰读取。

( Innodb_os_log_written ) = 1612644 /sec-- 这是 InnoDB 繁忙程度的指标。-- 非常空闲或非常繁忙的 InnoDB。

( innodb_log_buffer_size ) = 128M-- 建议 2MB-64MB，至少与事务中设置的最大 blob 一样大。-- 调整 innodb_log_buffer_size（现在为 134217728）。

( innodb_log_buffer_size / innodb_log_file_size ) = 128M / 256M = 50.0%-- 缓冲区在 RAM 中；文件在磁盘上。-- buffer_size 应该更小和/或 file_size 应该更大。

( Innodb_log_writes ) = 932 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 23,749,420,544 / (14727 / 3600) / 2 / 256M = 10.8-- 比率 -- （见会议纪要）

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 14,727 / 60 * 256M / 23749420544 = 2.77-- InnoDB 日志轮换间隔的分钟数从 5.6.8 开始，innodb_log_file_size 可以动态更改；我不知道 MariaDB 的情况。一定要更改 my.cnf --（建议轮换间隔 60 分钟有点武断。）调整 innodb_log_file_size（现在为 268435456）。（无法在 AWS 中更改。）

( Innodb_row_lock_waits ) = 0.34 /sec-- 获取行锁的延迟频率。-- 可能是由可以优化的复杂查询引起的。

( Innodb_dblwr_writes ) = 20 /sec-- “双写缓冲区”写入磁盘。“双写”是一种可靠性功能。一些较新的版本/配置不需要它们。--（其他问题的症状）

( innodb_flush_neighbors ) = innodb_flush_neighbors = 1-- 将块写入磁盘时进行小幅优化。-- 对于 SSD 驱动器使用 0；对于 HDD 使用 1。

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON-- 是否使用自适应哈希 (AHI)。-- 对于大部分只读，则为 ON；对于 DDL 密集型，则为 OFF

( innodb_flush_log_at_trx_commit ) = 1-- 1 = 安全；2 = 更快 -- （您决定）使用 1，以及 sync_binlog（现在为 0）=1，以获得最高级别的容错能力。0 最适合速度。2 是 0 和 1 之间的折衷。

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON-- 通常应为 ON。-- 在某些情况下，OFF 更好。另请参阅 innodb_adaptive_hash_index_parts（现在是 8）（5.7.9 之后）和 innodb_adaptive_hash_index_partitions（MariaDB 和 Percona）。ON 已与罕见的崩溃有关（错误 73890）。10.5.0 决定默认为 OFF。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 是否记录所有死锁。-- 如果您受到死锁的困扰，请启用此功能。注意：如果您有大量死锁，这可能会将大量数据写入磁盘。

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6%-- 全文结果集的字节限制。（它会根据需要增长。）-- 降低设置。

( local_infile ) = local_infile = ON -- local_infile（现在为 ON）= ON 是一个潜在的安全问题

( Qcache_not_cached ) = (Innodb_deadlocks) / 14727 = 49 /sec-- SQL_CACHE 尝试，但被忽略 -- 重新考虑缓存；调整 qcache

( Qcache_hits / (Qcache_hits + Com_select) ) = 461,409 / (461409 + 38022537) = 1.2%-- 命中率 -- 使用 QC 的 SELECT -- 考虑关闭查询缓存。

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 13121952) / 3138 / 16384 = 0.0711-- query_alloc_block_size 与公式 -- 调整 query_alloc_block_size（现在为 16384）

( Queries ) = (181705 - 3138) / 14727 = 15824 /sec-- 查询（包括 SP 内部）-- >3000可能强调服务器

( (Queries-Questions)/Queries ) = (233052549-4930886)/233052549 = 97.9%-- 存储例程内的查询比例。--（如果很高的话还不错；但它会影响其他一些结论的有效性。）

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (18326112 + 3121 + 0 + 0 + 220 + 0) / 14727 = 1244 /sec-- 写入次数/秒 -- 50 次写入次数/秒 + 日志刷新可能会使普通驱动器的 I/O 写入容量达到最大

( Com_stmt_prepare - Com_stmt_close ) = 18,687,518 - 18332247 = 355,271-- 有多少准备好的语句尚未关闭。-- 关闭准备好的语句

( Com_stmt_close / Com_stmt_prepare ) = 18,332,247 / 18687518 = 98.1%-- 应该关闭准备好的语句。-- 检查所有准备好的语句是否都已“关闭”。

( Com_admin_commands ) = 121 /sec -- 为什么有这么多 DDL 语句？

( binlog_format ) = binlog_format = MIXED-- 语句/行/混合。-- 5.7 (10.3) 首选行

( slow_query_log ) = slow_query_log = OFF-- 是否记录慢速查询。（5.1.12）

( long_query_time ) = 10-- 定义“慢速”查询的截止时间（秒）。-- 建议 2

( back_log ) = 80--（从 5.6.6 开始自动调整大小；基于 max_connections）-- 在进行大量连接时，提升到 min(150, max_connections（现在为 151）) 可能会有所帮助。

( thread_cache_size / Max_used_connections ) = 151 / 74 = 204.1% -- 线程缓存大于可能的连接数没有任何好处。浪费空间才是缺点。

( thread_pool_max_threads ) = 65,536-- MariaDB 线程池的众多设置之一 -- 降低值。

异常小：

((query_cache_size - Qcache_free_memory) /
      Qcache_queries_in_cache) / query_cache_min_res_unit = 0.284
(query_cache_size - Qcache_free_memory) /
      Qcache_queries_in_cache = 1,164
(query_cache_size - Qcache_free_memory) /
      Qcache_queries_in_cache = 1,164
Acl_database_grants = 0
Acl_users = 2
Aria_pagecache_read_requests = 17 /HR
Aria_pagecache_reads = 1.2 /HR
Aria_pagecache_write_requests = 3.4 /HR
Com_show_fields = 0
Handler_read_first = 2 /HR
Handler_read_rnd = 5.6 /HR
Handler_tmp_write = 0.56 /sec
Rows_tmp_read = 0.56 /sec
Select_range / Com_select = 0.00%
Sort_priority_queue_sorts = 0.24 /HR
Sort_rows = 64 /HR
Sort_scan = 0.98 /HR
innodb_lru_scan_depth / innodb_io_capacity = 0.2

异常大：

Com_begin = 24 /sec
Com_call_procedure = 25 /sec
Com_create_table = 24 /sec
Com_dealloc_sql = 25 /sec
Com_execute_sql = 1268 /sec
Com_insert = 1244 /sec
Com_insert_select = 0.21 /sec
Com_insert_select + Com_replace_select = 0.21 /sec
Com_prepare_sql = 1268 /sec
Com_select = 2581 /sec
Com_stmt_close = 1244 /sec
Com_stmt_execute = 1268 /sec
Com_stmt_prepare = 1268 /sec
Empty_queries = 2517 /sec
Feature_json = 24 /sec
Feature_subquery = 2488 /sec
Handler_commit = 5038 /sec
Handler_commit/Questions = 1504.9%
Handler_delete = 1059 /sec
Handler_read_rnd_next / Handler_read_rnd = 2.65e+8
Innodb_buffer_pool_write_requests = 13333 /sec
Innodb_data_fsyncs = 1020 /sec
Innodb_data_writes = 1200 /sec
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 247 /sec
Innodb_data_written = 9708271 /sec
Innodb_dblwr_pages_written = 247 /sec
Innodb_log_write_requests = 2229 /sec
Innodb_os_log_fsyncs = 933 /sec
Innodb_pages_created = 16 /sec
Innodb_pages_written = 247 /sec
Innodb_rows_deleted = 1059 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 2318 /sec
Innodb_rows_inserted = 1258 /sec
Prepared_stmt_count = 20
Table_open_cache_hits = 6285 /sec
Tc_log_page_size = 4,096
innodb_background_scrub_data_check_interval = 0.24 /sec
innodb_background_scrub_data_interval = 41 /sec

异常字符串：

Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
event_scheduler = ON
ft_boolean_syntax = + -><()~*:&
innodb_fast_shutdown = 1
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT

Answer