Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: David Scott (crittersdesktopcreatures.com)
Date: Wed Jan 07 2009 - 13:07:49 CST
Oh and we increased the key_buffer_size=1200M (30% of ram) no change.
2009/1/7 David Scott <crittersdesktopcreatures.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 <mos99fastmail.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
>>> gets to the 'creating sort index' phase, this lock seems to affect the
>>> server, all databases... does anyone know what may be causing this?
>>> Thanks in advance
>>> David Scott
>> 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
>> 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
>> 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.
>> If you increase your key_buffer size from the default value to 30% of your
>> memory, you should get indexes built faster.
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: