|
I have been trying to run an fairly large INSERT into an
empty table joining two other tables now for several weeks and have not been
able to get the query to run to completion even when sub-seting the data into smaller
ranges. I have tried this at MySQL releases 4.1.8a and 4.1.10a with
no noticable improvement. The server is running Linux 2.4.21-4 Elsmp RedHat 3.2.3.-20. The server is dedicated to MySQL. The my.cnf file is
attached. There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs (top
shows 4 processors). SHOW STATUS shows very good buffer hit ratio. Current stats
are attached. Server was re-booted last Friday. It has been running the INSERT
query since Saturday mid-morning and has yet to complete. Table 1 230
million rows total compund
PK index – 4 columns range 1 should
select 35 million rows. Explain plan shows it to be using the PK Table 2 598
million rows compound
PK index – 4 columns and one secondary index. Range 1 should select about
130 million rows Table 3 Empty
table unindexed. Two keys from Table 1 and 17 columns from table 2 populate this
table The general form of the query is: INSERT INTO Table 3 (col1, ….
Col18) SELECT Col1,,.col18 FROM Table
1 a INNER JOIN Table 2 b ON (PK columns and range selection) WHERE a.col5
= b.col5 AND …. AND … AND a.col18
= b.col18 Any suggestions are welcome. |
Variable_name Value Aborted_clients 52 Aborted_connects 5 Binlog_cache_disk_use 0 Binlog_cache_use 0 Bytes_received 337148 Bytes_sent 40680011 Com_admin_commands 0 Com_alter_db 0 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_change_db 38 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 1 Com_dealloc_sql 0 Com_delete 0 Com_delete_multi 0 Com_do 0 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 0 Com_drop_user 0 Com_execute_sql 0 Com_flush 3 Com_grant 4 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 0 Com_insert_select 4 Com_kill 2 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize 0 Com_preload_keys 0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_rename_table 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 0 Com_savepoint 0 Com_select 90 Com_set_option 15 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_charsets 1 Com_show_collations 34 Com_show_column_types 0 Com_show_create_db 0 Com_show_create_table 2 Com_show_databases 3 Com_show_errors 0 Com_show_fields 25 Com_show_grants 0 Com_show_innodb_status 0 Com_show_keys 20 Com_show_logs 0 Com_show_master_status 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_privileges 0 Com_show_processlist 6603 Com_show_slave_hosts 0 Com_show_slave_status 0 Com_show_status 6619 Com_show_storage_engines 0 Com_show_tables 5 Com_show_variables 29 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_truncate 0 Com_unlock_tables 0 Com_update 0 Com_update_multi 0 Connections 89 Created_tmp_disk_tables 2 Created_tmp_files 3 Created_tmp_tables 4 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 0 Handler_delete 0 Handler_discover 0 Handler_read_first 14 Handler_read_key 34664442 Handler_read_next 2460468597 Handler_read_prev 0 Handler_read_rnd 42 Handler_read_rnd_next 184703062 Handler_rollback 0 Handler_update 1 Handler_write 348602337 Key_blocks_not_flushed 0 Key_blocks_unused 590180 Key_blocks_used 1811829 Key_read_requests 1691116639 Key_reads 16737509 Key_write_requests 148881620 Key_writes 1949178 Max_used_connections 5 Not_flushed_delayed_rows 0 Open_files 43 Open_streams 0 Open_tables 23 Opened_tables 51 Qcache_free_blocks 1 Qcache_free_memory 25094216 Qcache_hits 14 Qcache_inserts 15 Qcache_lowmem_prunes 0 Qcache_not_cached 47 Qcache_queries_in_cache 14 Qcache_total_blocks 32 Questions 13564 Rpl_status NULL Select_full_join 0 Select_full_range_join 0 Select_range 8 Select_range_check 0 Select_scan 14 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 4 Sort_merge_passes 0 Sort_range 0 Sort_rows 42 Sort_scan 1 Table_locks_immediate 144 Table_locks_waited 0 Threads_cached 1 Threads_connected 4 Threads_created 5 Threads_running 3 Uptime 419787
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
