Oh and we increased the key_buffer_size=1200M (30% of ram) no change. 2009/1/7 David Scott <critt...@desktopcreatures.com>
> 1) InnoDb2) 5.0.51 on Linux > 3) No, a Select with a bunch of Joins, a Where, group and order > 4) 37 seconds > 5) Yes > 6) Show Processlist does not show anything, just the user, what are you > looking for? > > 2009/1/7 mos <mo...@fastmail.fm> > > At 11:20 AM 1/7/2009, you wrote: >> >>> When we run a large query other queries start to back up when the large >>> one >>> gets to the 'creating sort index' phase, this lock seems to affect the >>> whole >>> server, all databases... does anyone know what may be causing this? >>> Thanks in advance >>> -- >>> David Scott >>> >> >> David, >> Can you provide us with more info? >> >> 1) Is this an InnoDb table or MyISAM? >> 2) What version of MySQL are you using? >> 3) Are you using Create Index or Alter Table? Can you give us the syntax >> you are using? >> 4) How long does it take? Can you give us the table structure & # of >> indexes? >> 5) Are these queries that are backed up, referencing the table you are >> building the index on? >> 6) Can you provide us with a Show Process List? >> >> This should help the members of this list give you a better more informed >> answer. >> >> Offhand I suspect your key_buffer_size may be too low and MySQL is >> attempting to build the index on disk rather than in memory. If the index >> can be built in memory it will be 10x faster than building the index on >> disk. That is why adding as much ram as possible to your server will help. >> >> This is set in your my.cnf file: >> >> # Size of the Key Buffer, used to cache index blocks for MyISAM tables. >> # Do not set it larger than 30% of your available memory, as some memory >> # is also required by the OS to cache rows. Even if you're not using >> # MyISAM tables, you should still set it to 8-64M as it will also be >> # used for internal temporary disk tables. >> key_buffer_size=500M >> >> If you increase your key_buffer size from the default value to 30% of your >> memory, you should get indexes built faster. >> >> Mike >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=critt...@desktopcreatures.com >> >> >> >
