Mysql 5.1 to 5.5.1.18 Upgrade Poor Performance
Posted by: Thomas Frederiksen
Date: December 21, 2011 01:32PM
Date: December 21, 2011 01:32PM
We recently upgraded to 5.5.1.18 and have noticed massive performance degradation with any query that has a dependent or correlated subquery. None of the INI settings changed other than renaming default_character_set to character-set-server.
I have turned query cache off now, but haven't had the opportunity to bounce the server.
Please see the query below as an example. Original time was around 3 minutes and it never finishes now.
Explain:
Show Variables:
Thanks in advance,
Tom
I have turned query cache off now, but haven't had the opportunity to bounce the server.
Please see the query below as an example. Original time was around 3 minutes and it never finishes now.
SELECT NULL, ce.contract_esiid_id, c.contract_execution_date AS 'EventDate', IF(c.is_renewal = 1, 2, 1) AS 'type', cu.state, IF(ch.actual_kwh IS NOT NULL, IF(e.commodity='E', ch.actual_kwh, fn_gas_to_thm((SELECT h.units FROM tblraw_hud_data h WHERE h.gkey=ce.gkey ORDER BY start_date DESC LIMIT 1), ch.actual_kwh, e.gkey, (SELECT h.sender_transaction_id FROM tblraw_hud_data h WHERE h.gkey=ce.gkey ORDER BY start_date DESC LIMIT 1)) ), 0 ) AS 'hud', e.commodity FROM tblcontract_esiids ce INNER JOIN tblesiids e ON e.gkey = ce.gkey INNER JOIN tblcontracts c ON c.contract_id = ce.contract_id AND c.recstatus = 1 INNER JOIN tblcustomer cu ON cu.customer_number = c.customer_number LEFT JOIN tblcalendarized_hud_totals ch ON ch.gkey = ce.gkey WHERE ce.recstatus = 1 AND ce.void = 0 GROUP BY ce.contract_esiid_id ORDER BY NULL
Explain:
+----+--------------------+-------+--------+-----------------------------------+----------------+---------+----------------------------------+-------+----------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+--------+-----------------------------------+----------------+---------+----------------------------------+-------+----------+-----------------+ | 1 | PRIMARY | c | ref | PRIMARY,Customer_Number,RecStatus | RecStatus | 1 | const | 48782 | 100.00 | Using temporary | | 1 | PRIMARY | cu | eq_ref | PRIMARY,Customer_Number | PRIMARY | 13 | crm.c.Customer_Number | 1 | 100.00 | | | 1 | PRIMARY | ce | ref | Contract_ID,RecStatus,GKEY | Contract_ID | 4 | crm.c.contract_id | 1 | 100.00 | Using where | | 1 | PRIMARY | e | eq_ref | PRIMARY | PRIMARY | 4 | crm.ce.GKEY | 1 | 100.00 | | | 1 | PRIMARY | ch | ref | idx_Gkey | idx_Gkey | 4 | crm.e.GKEY | 1 | 100.00 | | | 3 | DEPENDENT SUBQUERY | h | index | GKEY | idx_start_date | 3 | NULL | 1 | 900.00 | Using where | | 2 | DEPENDENT SUBQUERY | h | index | GKEY | idx_start_date | 3 | NULL | 1 | 900.00 | Using where | +----+--------------------+-------+--------+-----------------------------------+----------------+---------+----------------------------------+-------+----------+-----------------+
Show Variables:
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | ROW | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | cp850 | | character_set_connection | cp850 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | cp850 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | cp850_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | NO_CHAIN | | concurrent_insert | AUTO | | connect_timeout | 10 | | datadir | G:\ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_storage_engine | InnoDB | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | engine_condition_pushdown | ON | | error_count | 0 | | event_scheduler | ON | | expire_logs_days | 30 | | external_user | | | flush | OFF | | flush_time | 1800 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | general_log_file | G:\ PRODDB02.log | | group_concat_max_len | 1024 | | have_compress | YES | | have_crypt | NO | | have_csv | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_innodb | YES | | have_ndbcluster | NO | | have_openssl | DISABLED | | have_partitioning | YES | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | | hostname | PRODDB02 | | identity | 0 | | ignore_builtin_innodb | OFF | | init_connect | | | init_file | | | init_slave | | | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 33554432 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 38654705664 | | innodb_change_buffering | all | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 5000 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | G:\ | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 600 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 16777216 | | innodb_log_file_size | 536870912 | | innodb_log_files_in_group | 4 | | innodb_log_group_home_dir | G:\ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 0 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 64 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | ON | | innodb_rollback_segments | 128 | | innodb_spin_wait_delay | 6 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 34 | | innodb_thread_sleep_delay | 10000 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 1.1.8 | | innodb_write_io_threads | 64 | | insert_id | 0 | | interactive_timeout | 28800 | | join_buffer_size | 16777216 | | keep_files_on_create | OFF | | key_buffer_size | 57671680 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | last_insert_id | 0 | | lc_messages | en_US | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | lock_wait_timeout | 31536000 | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_error | E:\MySQL Log Files\error.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | long_query_time | 10.000000 | | low_priority_updates | OFF | | lower_case_file_system | ON | | lower_case_table_names | 1 | | max_allowed_packet | 1073741824 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_connect_errors | 10 | | max_connections | 800 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 536870912 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_long_data_size | 1073741824 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 107374182400 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | BACKUP | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 131072000 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | OFF | | named_pipe | OFF | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old | OFF | | old_alter_table | OFF | | old_passwords | OFF | | open_files_limit | 6048 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on | | performance_schema | OFF | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 1000 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 10000 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 1000000 | | performance_schema_max_rwlock_classes | 30 | | performance_schema_max_rwlock_instances | 1000000 | | performance_schema_max_table_handles | 100000 | | performance_schema_max_table_instances | 50000 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 1000 | | pid_file | G:\MySQL Datafiles\PRODDB02.pid | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | proxy_user | | | pseudo_thread_id | 149741 | | query_alloc_block_size | 8192 | | query_cache_limit | 4194304 | | query_cache_min_res_unit | 4096 | | query_cache_size | 367001600 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | rand_seed1 | 0 | | rand_seed2 | 0 | | range_alloc_block_size | 4096 | | read_buffer_size | 65536 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 1 | | shared_memory | OFF | | shared_memory_base_name | MYSQL | | skip_external_locking | ON | | skip_name_resolve | OFF | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | C:\Windows\TEMP | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slave_type_conversions | | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | G:\MySQL Datafiles\PRODDB02-slow.log | | socket | MySQL | | sort_buffer_size | 2097152 | | sql_auto_is_null | OFF | | sql_big_selects | ON | | sql_big_tables | OFF | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_low_priority_updates | OFF | | sql_max_join_size | 18446744073709551615 | | sql_mode | | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | 0 | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | InnoDB | | sync_binlog | 0 | | sync_frm | ON | | sync_master_info | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | | system_time_zone | Atlantic Standard Time | | table_definition_cache | 400 | | table_open_cache | 200 | | thread_cache_size | 38 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | timestamp | 1324495609 | | tmp_table_size | 536870912 | | tmpdir | C:\Windows\TEMP | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | unique_checks | ON | | updatable_views_with_limit | YES | | version | 5.5.18-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86 | | version_compile_os | Win64 | | wait_timeout | 28800 | | warning_count | 0 | +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
Thanks in advance,
Tom
Subject
Views
Written By
Posted
1117
December 27, 2011 10:28AM
1347
December 30, 2011 12:52PM
1324
December 30, 2011 01:30PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.