MySQL Forums
Forum List  »  Newbie

Re: MYSQL update taking to long need help
Posted by: Martin Weaver
Date: January 31, 2017 06:23AM

Answers under questions


Peter Brawley Wrote:
-------------------------------------------------------
> Those answers don't require a trained DBA. However
> if you're managing these tables, you are
> their DBA.
>
> Read about BBCode tags. Execute these cmds ...
>
> select version();
'5.6.25-log'



>
> Show Create Table schemaa1.cqt;
'cqt', 'CREATE TABLE `cqt` (\n `trayid` int(10) unsigned NOT NULL DEFAULT \'0\',\n `linking` int(10) unsigned NOT NULL,\n `pallet` int(10) unsigned DEFAULT NULL,\n `traytype` varchar(2) DEFAULT NULL,\n `trayact` int(10) unsigned DEFAULT NULL,\n PRIMARY KEY (`trayid`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'



>
> Show Create Table schema1.phase3;
'phase3', 'CREATE TABLE `phase3` (\n `num` int(10) unsigned NOT NULL DEFAULT \'0\',\n `stored` decimal(32,0) NOT NULL,\n `readid` varchar(45) DEFAULT NULL,\n `complete` int(1) NOT NULL DEFAULT \'0\',\n `trayid` int(5) unsigned DEFAULT NULL,\n `pallet` int(5) unsigned DEFAULT NULL,\n `linking` int(5) unsigned NOT NULL,\n `backed` int(6) unsigned DEFAULT NULL,\n `traytype` varchar(2) NOT NULL DEFAULT \'0\',\n PRIMARY KEY (`stored`),\n KEY `linking` (`linking`),\n CONSTRAINT `phase3_ibfk_1` FOREIGN KEY (`linking`) REFERENCES `cqt` (`trayid`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'



>
> Explain
> update schema1.phase3 as a
> right join schema1.cqt as b on b.trayid =
> a.linking
> set a.trayid = b.trayact, a.pallet = b.pallet ;
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'b', 'ALL', NULL, NULL, NULL, NULL, '18465', NULL
'1', 'SIMPLE', 'a', 'ref', 'linking', 'linking', '4', 'ezmysqltest.b.trayid', '25', NULL




>
> show variables like 'innodb%';

# Variable_name, Value
'innodb_adaptive_flushing', 'ON'
'innodb_adaptive_flushing_lwm', '10'
'innodb_adaptive_hash_index', 'ON'
'innodb_adaptive_max_sleep_delay', '150000'
'innodb_additional_mem_pool_size', '2097152'
'innodb_api_bk_commit_interval', '5'
'innodb_api_disable_rowlock', 'OFF'
'innodb_api_enable_binlog', 'OFF'
'innodb_api_enable_mdl', 'OFF'
'innodb_api_trx_level', '0'
'innodb_autoextend_increment', '64'
'innodb_autoinc_lock_mode', '1'
'innodb_buffer_pool_dump_at_shutdown', 'OFF'
'innodb_buffer_pool_dump_now', 'OFF'
'innodb_buffer_pool_filename', 'ib_buffer_pool'
'innodb_buffer_pool_instances', '8'
'innodb_buffer_pool_load_abort', 'OFF'
'innodb_buffer_pool_load_at_startup', 'OFF'
'innodb_buffer_pool_load_now', 'OFF'
'innodb_buffer_pool_size', '8388608'
'innodb_change_buffer_max_size', '25'
'innodb_change_buffering', 'all'
'innodb_checksum_algorithm', 'crc32'
'innodb_checksums', 'ON'
'innodb_cmp_per_index_enabled', 'OFF'
'innodb_commit_concurrency', '0'
'innodb_compression_failure_threshold_pct', '5'
'innodb_compression_level', '6'
'innodb_compression_pad_pct_max', '50'
'innodb_concurrency_tickets', '5000'
'innodb_data_file_path', 'ibdata1:12M:autoextend'
'innodb_data_home_dir', ''
'innodb_disable_sort_file_cache', 'OFF'
'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_timeout', '1'
'innodb_flush_log_at_trx_commit', '1'
'innodb_flush_method', ''
'innodb_flush_neighbors', '1'
'innodb_flushing_avg_loops', '30'
'innodb_force_load_corrupted', 'OFF'
'innodb_force_recovery', '0'
'innodb_ft_aux_table', ''
'innodb_ft_cache_size', '8000000'
'innodb_ft_enable_diag_print', 'OFF'
'innodb_ft_enable_stopword', 'ON'
'innodb_ft_max_token_size', '84'
'innodb_ft_min_token_size', '3'
'innodb_ft_num_word_optimize', '2000'
'innodb_ft_result_cache_limit', '2000000000'
'innodb_ft_server_stopword_table', ''
'innodb_ft_sort_pll_degree', '2'
'innodb_ft_total_cache_size', '640000000'
'innodb_ft_user_stopword_table', ''
'innodb_io_capacity', '200'
'innodb_io_capacity_max', '2000'
'innodb_large_prefix', 'OFF'
'innodb_lock_wait_timeout', '50'
'innodb_locks_unsafe_for_binlog', 'OFF'
'innodb_log_buffer_size', '1048576'
'innodb_log_compressed_pages', 'ON'
'innodb_log_file_size', '50331648'
'innodb_log_files_in_group', '2'
'innodb_log_group_home_dir', '.\\'
'innodb_lru_scan_depth', '1024'
'innodb_max_dirty_pages_pct', '75'
'innodb_max_dirty_pages_pct_lwm', '0'
'innodb_max_purge_lag', '0'
'innodb_max_purge_lag_delay', '0'
'innodb_mirrored_log_groups', '1'
'innodb_monitor_disable', ''
'innodb_monitor_enable', ''
'innodb_monitor_reset', ''
'innodb_monitor_reset_all', ''
'innodb_old_blocks_pct', '37'
'innodb_old_blocks_time', '1000'
'innodb_online_alter_log_max_size', '134217728'
'innodb_open_files', '300'
'innodb_optimize_fulltext_only', 'OFF'
'innodb_page_size', '16384'
'innodb_print_all_deadlocks', 'OFF'
'innodb_purge_batch_size', '300'
'innodb_purge_threads', '1'
'innodb_random_read_ahead', 'OFF'
'innodb_read_ahead_threshold', '56'
'innodb_read_io_threads', '4'
'innodb_read_only', 'OFF'
'innodb_replication_delay', '0'
'innodb_rollback_on_timeout', 'OFF'
'innodb_rollback_segments', '128'
'innodb_sort_buffer_size', '1048576'
'innodb_spin_wait_delay', '6'
'innodb_stats_auto_recalc', 'ON'
'innodb_stats_method', 'nulls_equal'
'innodb_stats_on_metadata', 'OFF'
'innodb_stats_persistent', 'ON'
'innodb_stats_persistent_sample_pages', '20'
'innodb_stats_sample_pages', '8'
'innodb_stats_transient_sample_pages', '8'
'innodb_status_output', 'OFF'
'innodb_status_output_locks', 'OFF'
'innodb_strict_mode', 'OFF'
'innodb_support_xa', 'ON'
'innodb_sync_array_size', '1'
'innodb_sync_spin_loops', '30'
'innodb_table_locks', 'ON'
'innodb_thread_concurrency', '8'
'innodb_thread_sleep_delay', '1250'
'innodb_undo_directory', '.'
'innodb_undo_logs', '128'
'innodb_undo_tablespaces', '0'
'innodb_use_native_aio', 'ON'
'innodb_use_sys_malloc', 'ON'
'innodb_version', '5.6.25'
'innodb_write_io_threads', '4'

Options: ReplyQuote


Subject
Written By
Posted
Re: MYSQL update taking to long need help
January 31, 2017 06:23AM


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.