Hi David, I think try using show full processlist that shall tell the query that is running. I think the problem is with your query only. we need to fine tune the query. Please send the query and the explain plan for the same. share more stats on things that you notice during that time.
Regards, Chandru forums.mafiree.com On Thu, Jan 8, 2009 at 12:37 AM, David Scott <critt...@desktopcreatures.com>wrote: > 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 > >> > >> > >> > > >
