从RDS Aurora 5.6到Aurora 5.7的性能问题

Question

最近，我将我的一个RDS实例从5.6升级到极光mysql 5.7，在尝试解决这个问题时，我不得不将实例类提高三倍(cpu最终会与之挂钩，并且不会下降)。

现在大多数阅读都是慢的(有些读慢了30%，有些读慢了400% )。从解释输出来看，查询仍然使用适当的索引。innodb_buffer_pool_size us设置为默认的RDS值(我认为实例类*3/24)和大多数其他innodb_*设置都是不可修改的。

下面是一个例子Mysql5.6：

| count(*) |
+----------+
| 20646739 |
+----------+
1 row in set (0.06 sec)

Mysql5.7：

mysql> select count(*) from nope;                                                                                                                                                                    +----------+
| count(*) |
+----------+
| 20646739 |
+----------+
1 row in set (3.77 sec)

其他人遇到这种情况，可以提供一些洞察力的设置，我可以调整？

这两个实例都是db.r3.2xlarge。

Answer 1

(假设状况为5.7)

Analysis of GLOBAL STATUS and VARIABLES:

观测：

• 版本:5.7.12-日志
• 61 GB内存
• 正常运行时间= 1d 09:08:40
• 您不在Windows上运行。
• 运行64位版本
• 您似乎正在完全(或大部分)运行InnoDB。

更重要的问题：

table_open_cache 10000
innidb_io_capacity 1000   -- if using SSD drive
innodb_change_buffering = all  -- unless there is a reason for "none"
query_cache_size = 50M

当大量RAM被赋予时，查询缓存效率很低。Aurora已经解决了这个问题，但我不知道RDS是否解决了这个问题。所以我建议缩小尺寸。

似乎有大量的回滚。

详细信息和其他观察：

( Opened_tables ) = 2,084,463 / 119320 = 17 /sec -打开桌子的频率-增加table_open_cache (现在6000)

( table_open_cache ) = 6,000 --要缓存的表描述符的数量--几百个通常是好的。

( Table_open_cache_overflows ) = 2,078,458 / 119320 = 17 /sec -可能需要增加table_open_cache (现在是6000)

( Table_open_cache_misses ) = 2,084,463 / 119320 = 17 /sec -可能需要增加table_open_cache (现在是6000)

( Opened_tables / Uptime / table_open_cache_instances ) = 2,084,463 / 119320 / 16 = 1.09 -- table_open_cache_instances的一个度量--增加table_open_cache_instances (现在16)，以减少对table_open_cache (现在6000)的争夺。

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 47683M / 8 = 5,960.4MB --每个buffer_pool实例的大小。-一个实例至少应该是1GB。在非常大的RAM中，有16个实例。

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 --每秒钟页面清理器的工作量。- "InnoDB: page_cleaner: 1000预定循环.“可以通过降低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) (开始在10.5中消失)

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000的预定循环.“可以通过降低lru_scan_depth来固定

( innodb_io_capacity ) = 200--阅读可能是粗俗的或尖刻的。

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 3,755,104 / 5871616 = 64.0% --目前还没有使用的buffer_pool的Pct -- innodb_buffer_pool_size (现在是49999249408)比需要的大吗？

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 --容量:最大/普通--推荐2。最大值应该相当于你的I/O子系统所能处理的IOP。(如果驱动器类型未知，则2000/200可能是合理的一对。)

( innodb_change_buffering ) = innodb_change_buffering = none --在5.6.11/ 5.5.31之前，有一个bug使得=“更改”成为一个更安全的选项。

( innodb_doublewrite ) = innodb_doublewrite = OFF --额外的I/O，但在坠机时额外的安全性。-- FusionIO，Galera，复制品，ZFS都可以.

( Handler_rollback ) = 5,533,615 / 119320 = 46 /sec -为什么会有这么多回滚？

( innodb_flush_neighbors ) = 1 --将块写入磁盘时的次要优化。- SSD驱动器使用0；HDD使用1。

( innodb_io_capacity ) = 200 --每秒可以在磁盘上执行I/O操作。100用于慢速驱动器；200用于旋转驱动器；1000-2000用于SSD；乘以RAID因子。

( Handler_rollback/Questions ) = 5,533,615/19923532 = 27.8% --回滚/查询--为什么回滚这么多？

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF --是否记录所有死锁。--如果你被死锁所困扰，把这个打开警告:如果您有很多死锁，这可能会写入很多磁盘。

( max_connections ) = 3,000 --最大连接数(线程)。影响各种拨款。--如果max_connections (现在3000)太高，而且各种内存设置都很高，那么可能会耗尽内存。

( local_infile ) = local_infile = ON - local_infile (现在开始)= ON是一个潜在的安全问题

( query_cache_size ) = 2,460,864,512 = 2,346.9MB -- QC的大小--太小=没有多大用处。太大=太多的开销。推荐0或不超过50米。

( Qcache_hits / Qcache_inserts ) = 6,303,912 / 6590660 = 0.956 --点击插入比--高是好的--考虑关闭查询缓存。

( Created_tmp_tables ) = 18,657,920 / 119320 = 156 /sec -作为复杂选择的一部分创建“临时”表的频率。

( Created_tmp_disk_tables ) = 982,228 / 119320 = 8.2 /sec --创建磁盘“临时”表的频率，作为复杂索引的一部分，选择更好的索引，重新制定查询可能会有所帮助。

( Created_tmp_disk_tables / Questions ) = 982,228 / 19923532 = 4.9% --需要磁盘tmp表的查询的Pct。-更好的索引/无瑕疵/等等。

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (634834 + 494921 + 71954 + 0) / 433212 = 2.77 --每次提交语句(假设所有InnoDB) -- Low:可能有助于在事务中将查询分组；高:长事务会使各种事情紧张。

( Select_full_join / Com_select ) = 1,285,085 / 9113463 = 14.1% -- %的选择是无索引的联接--向联接中使用的表添加合适的索引(Es)。

( Sort_merge_passes ) = 152,110 / 119320 = 1.3 /sec --很好的排序--增加sort_buffer_size (现在是262144)和/或优化复杂查询。

( Com__biggest ) = Com__biggest = Com_stmt_execute --哪个"Com_“指标是最大的。-通常是Com_select (现在是9113463)。如果是其他的东西，那么它可能是一个草率的平台，或者可能是其他的东西。

( relay_log_space_limit ) = 1,000,000,000 = 953.7MB --副本上中继日志的最大总大小。(0=unlimited) --让我们来讨论有一个限制的理由。

( long_query_time ) = 25 --定义“慢速”查询的截止值(秒)。-建议2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11，5.7.1)默认情况下，复制的语句不会显示在慢速日志中；这会导致它们显示出来。--在慢速日志中看到可能干扰副本读取的写操作是有帮助的。

( back_log ) = 16,000 -- (基于max_connections的自动大小为5.6.6；基于max_connections) --提高到最小(150，max_connections(现在为3000))可能会在进行大量连接时有所帮助。

( Max_used_connections / max_connections ) = 110 / 3000 = 3.7% --连接的峰值% --由于几个内存因素可以基于max_connections (现在的3000)扩展，所以最好不要设置得太高。

( Connections ) = 2,266,422 / 119320 = 19 /sec --连接--增加wait_timeout (现在是28800)；使用池？

异常小：

Com_show_fields = 0
Com_show_tables = 0
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.0005
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_data_fsyncs = 0
Innodb_data_reads = 0
Innodb_data_writes = 0
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 0
Innodb_data_written = 0
Innodb_dblwr_pages_written = 0
Innodb_log_write_requests = 0
Innodb_os_log_fsyncs = 0
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 0
Open_files = 2
Table_locks_immediate = 2.7 /HR
innodb_online_alter_log_max_size = 128MB
innodb_sort_buffer_size = 1.05e+6
performance_schema_max_cond_classes = 0
performance_schema_max_digest_length = 0
performance_schema_max_file_classes = 0
performance_schema_max_file_handles = 0
performance_schema_max_mutex_classes = 0
performance_schema_max_rwlock_classes = 0
performance_schema_max_socket_classes = 0
performance_schema_max_stage_classes = 0
performance_schema_max_statement_classes = 0
performance_schema_max_thread_classes = 0
query_cache_limit = 1.05e+6

异常大：

(query_cache_size - Qcache_free_memory) / query_cache_size = 99.7%
1 - Qcache_free_memory / query_cache_size = 99.7%
Com_call_procedure = 1.3 /sec
Com_create_trigger = 0.24 /HR
Com_delete_multi = 44 /HR
Com_do = 0.78 /sec
Com_drop_procedure = 0.03 /HR
Com_drop_trigger = 0.24 /HR
Com_flush = 29 /HR
Com_insert_select = 0.44 /sec
Com_insert_select + Com_replace_select = 1.1 /sec
Com_purge_before_date = 12 /HR
Com_replace_select = 0.7 /sec
Com_stmt_close = 136 /sec
Com_stmt_execute = 136 /sec
Com_stmt_prepare = 136 /sec
Com_update_multi = 0.87 /sec
Created_tmp_files = 0.58 /sec
Handler_read_key = 113933 /sec
Handler_read_next = 352797 /sec
Handler_write = 122405 /sec
Innodb_buffer_pool_bytes_data = 290621 /sec
Innodb_buffer_pool_pages_dirty = 354,764
Innodb_buffer_pool_pages_free = 3.76e+6
Innodb_buffer_pool_pages_total = 5.87e+6
Innodb_buffer_pool_read_requests = 1273844 /sec
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 2,807
Innodb_rows_deleted + Innodb_rows_inserted = 495 /sec
Innodb_rows_inserted = 494 /sec
Open_tables = 5,984
Prepared_stmt_count = 5
Qcache_total_blocks = 4.47e+6
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 37,784
Select_full_range_join = 0.46 /sec
Select_full_range_join / Com_select = 0.60%
Select_range_check = 50 /HR
back_log / max_connections = 533.3%
innodb_buffer_pool_chunk_size = 5,960.4MB
innodb_purge_batch_size = 1,800
innodb_stats_persistent_sample_pages = 128
server_audit_query_log_limit = 65,536
table_definition_cache = 20,000

异常字符串：

core_file = ON
gtid_mode = OFF_PERMISSIVE
innodb_checksums = OFF
innodb_fast_shutdown = 1
innodb_use_native_aio = OFF
opt_s__derived_merge = off
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_recovery = ON
show_compatibility_56 = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
thread_handling = multiple-connections-per-thread

Answer 2

每秒速率= RPS

关于您的5.7.12极光参数组的建议

innodb_write_io_threads=16  # from 4 to enable higher IOPS to your SSD device
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
innodb_max_dirty_pages_pct_lwm=.0001  # from 0 to enable pre-flushing
innodb_max_dirty_pages_pct=.0001  # from 75 % dirty tolerated to reduce innodb_buffer_pool_pages_dirty count of 354,764 - will take hours
net_buffer_length=98304  # from 16384 to reduce count of interruptions for send/receive pkts
innodb_fast_shutdown=0  # from 1 to avoid recovery cycle on restart

这只是你提高性能的旅程的开始。感谢您有机会为您的团队服务。

并不是所有这些全局变量都是动态的。将需要实例停止/启动。