![]() ![]() Innodb_undo_log_truncate added in 5.7, set to ‘OFF’Īnd attempted to switch off the following: innodb_strict_mode=‘OFF’ Innodb_temp_data_file_path added in 5.7, set to 'ibtmp1:12M:autoextend' Innodb_strict_mode changed from ‘OFF’ to ‘ON’ Innodb_purge_rseg_truncate_frequency added in 5.7 set to ‘128’ Innodb_page_cleaners added in 5.7 set to ‘4’ Innodb_numa_interleave added in 5.7 set to ‘OFF’ Innodb_mirrored_log_groups removed in 5.7, set to ‘1’ in 5.6 Innodb_log_write_ahead_size added in 5.7 set to 8192 Innodb_log_checksums added in 5.7 set to ‘ON’ Innodb_flush_sync added in 5.7 set to ‘ON’ Innodb_fill_factor added in 5.7 set to ‘100’ Innodb_file_format_max changed from ‘Antelope’ to ‘Barracuda’ Innodb_file_format changed from ‘Antelope’ to ‘Barracuda’ Innodb_default_row_format added in 5.7, set to ‘dynamic’ Innodb_deadlock_detect added in 5.7, set to ‘ON’ Innodb_checksum_algorithm changed from ‘innodb’ to ‘crc32’ Innodb_buffer_pool_load_at_startup changed from ‘OFF’ to ‘ON’ Innodb_buffer_pool_dump_pct added in 5.7, set to ’25’ Innodb_buffer_pool_dump_at_shutdown changed from ‘OFF’ to ‘ON’ Innodb_additional_mem_pool_size set to 8388608 in 5.6, removed in 5.7 Innodb_adaptive_hash_index_parts added in 5.7, set to ‘8’ Next, I looked at the differences in my innodb settings (my 5.7 instance has more memory, hence the pool size differences). SET optimizer_switch='duplicateweedout=off' SET optimizer_switch='derived_merge=off' I switched them off as follows: SET optimizer_switch='condition_fanout_filter=off' The only changes I see were the last three options in 5.7. I started by comparing the optimizer settings between 5.6 and 5.7: # 5.6 Each table has approximately 2 million rows. After upgrading to 5.7, it’s taking over 30 minutes. In 5.6, this query completes in 90 seconds. ![]() Name,d.id,deviceType,issuedBy, description,avNum,CompanyName, I have a fairly straightforward query that has become very slow after upgrading from MySQL 5.6.35 to MySQL 5.7.12 (running on AWS RDS). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |