I'm trying to figure out which limits I'm hitting on some inserts. I have 50 plus tables lets call them A_USER, B_USER, C_USER etc which I daily refresh with updated (and sometimes new) data.
I insert the data into a temporary table using LOAD DATA INFILE. This works great and is very fast. Then I do an INSERT INTO A_USER (Select col1,col2,col3...,col 20, 0,0,0,0,0,0,etc etc from A_TEMP) on DUPLICATE KEY UPDATE col1=A_TEMP.col1,col2= etc The sizes in the tables range from 500 entries up to 750,000. two of them in the 200,000 range take 2-3 mins for this to complete, the largest at 750,000 takes over an hour. a sampling of my cnf file is old_passwords=1 max_connections = 50 max_user_connections = 50 table_cache=2000 open_files_limit=4000 log-slow-queries = /var/log/mysql-slow.log long_query_time = 12 log-queries-not-using-indexes thread_cache_size = 100 query_cache_size = 64M key_buffer_size = 512M join_buffer_size = 24M sort_buffer_size = 64M read_buffer_size = 4M tmp_table_size = 64M max_heap_table_size = 64M There is 2Gb Ram in the server which I would gladly increase if I knew I could tweak these settings to fix this? Any ideas what I should do to figure out what is causing it? Regards Phil
