Just a little more info on this. I tried setting all of this up on a home server with, as far as I can see, more or less identical specs with the exception being that it's a 64bit linux build rather than 32bit.
Same insert on duplicate update takes 3 mins. I spent all day yesterday trying to figure out what limits are being hit without success. Would certainly appreciate any pointers to look at.. Phil On Thu, Feb 28, 2008 at 11:19 AM, Phil <[EMAIL PROTECTED]> wrote: > 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 > >
