performance problem on INSERT into MyISAM table
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. Ed Sweeney Variable_name Value Aborted_clients 52 Aborted_connects5 Binlog_cache_disk_use 0 Binlog_cache_use0 Bytes_received 337148 Bytes_sent 40680011 Com_admin_commands 0 Com_alter_db0 Com_alter_table 0 Com_analyze 0 Com_backup_table0 Com_begin 0 Com_change_db 38 Com_change_master 0 Com_check 0 Com_checksum0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index0 Com_create_table1 Com_dealloc_sql 0 Com_delete 0 Com_delete_multi0 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_close0 Com_ha_open 0 Com_ha_read 0 Com_help0 Com_insert 0 Com_insert_select 4 Com_kill2 Com_load0 Com_load_master_data0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize0 Com_preload_keys0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_rename_table0 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_rollback0 Com_savepoint 0 Com_select 90 Com_set_option 15 Com_show_binlog_events 0 Com_show_binlogs0 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_tables0 Com_show_privileges 0 Com_show_processlist6603 Com_show_slave_hosts0 Com_show_slave_status 0 Com_show_status 6619 Com_show_storage_engines0 Com_show_tables 5 Com_show_variables 29 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_truncate0 Com_unlock_tables 0 Com_update 0 Com_update_multi0 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_discover0 Handler_read_first 14 Handler_read_key34664442 Handler_read_next 2460468597 Handler_read_prev 0 Handler_read_rnd42 Handler_read_rnd_next 184703062 Handler_rollback0 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_connections5 Not_flushed_delayed_rows0 Open_files 43 Open_streams0 Open_tables 23 Opened_tables 51 Qcache_free_blocks 1 Qcache_free_memory 25094216 Qcache_hits 14 Qcache_inserts 15 Qcache_lowmem_prunes0 Qcache_not_cached 47 Qcache_queries_in_cache 14
Re: performance problem on INSERT into MyISAM table
I must assume you have all the proper indexes setup and your configuration variables are fairly optimal. First, I would run just the select part with an explain in front of it to see what MySQL is trying to do. I've had MySQL run a query for an inordinate amount of time on a fairly small data set because of a typo and no index in use for a join. Second, I would check the size of the data file to see if it is growing. At least you'll be able to see if something is going on. Third, I would try just the select part with a limit of say 10 or 20. This will show you how long MySQL is taking to do the search and joins, eliminating data transfer time. You need to find where it's bottlenecking. It may very well be that you are I/O bound (iostat). MySQL is reading from the tables, certainly using temporary tables with a data set of that size and trying to load data into a new table. That's a lot of reads and writes going on at once, your disk(s) may be getting hammered, especially if you are not using RAID. You could also try disabling indexes on table 3 (if you have any) and then enabling after the import is done. That will speed up the import process. Lastly, instead of doing and insert select, just select to a file (INTO OUTFILE). Then import the data using load data infile. A two step processing, but something that will allow you to control all the I/O that's going on. On Apr 26, 2005, at 9:22 AM, Ed Sweeney wrote: 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. Ed Sweeney show_status.txt-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
