MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:David Scott Date:January 7 2009 7:07pm
Subject:Re: Locking database when 'creating sort index'
View as plain text  
Oh and we increased the key_buffer_size=1200M (30% of ram) no change.

2009/1/7 David Scott <critters@stripped>

> 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 <mos99@stripped>
>
> 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=1
>>
>>
>>
>

Thread
Locking database when 'creating sort index'David Scott7 Jan
  • Re: Locking database when 'creating sort index'Johan De Meersman7 Jan
  • Re: Locking database when 'creating sort index'mos7 Jan
    • Re: Locking database when 'creating sort index'David Scott7 Jan
      • Re: Locking database when 'creating sort index'David Scott7 Jan
        • Re: Locking database when 'creating sort index'Chandru8 Jan
      • Re: Locking database when 'creating sort index'mos7 Jan