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 > > >
